包含ORDER BY的MySQL UNION 2查询
|
我正在尝试联合两个都包含ORDER BY \的查询。
正如我发现的那样，您无法通过属于UNION的查询进行排序。
我只是不知道该怎么办呢。让我解释一下我要做什么。
我正在尝试选择40个最新的配置文件，然后从该列表中选择20个随机集。然后，我希望将其与：
选择40个随机配置文件，其中配置文件不属于第一组中查询的原始40个最新配置文件
随机订购整套60条记录。
我知道使用Rand（）函数的效率后果
SELECT profileId
FROM (SELECT profileId
FROM profile profile2
WHERE profile2.profilePublishDate <= Now()
ORDER BY profile2.profilePublishDate DESC
LIMIT 0,40) AS profile1
ORDER BY RAND()
LIMIT 0,20
UNION (SELECT profileId
FROM profile profile4
WHERE profileId NOT IN (SELECT profileId
FROM profile profile4
WHERE profile4.profilePublishDate <= Now()
ORDER BY profile4.profilePublishDate DESC
LIMIT 0,40)
ORDER BY RAND()
LIMIT 0,40) as profile3
ORDER BY RAND()
SELECT *
FROM
(
(
SELECT profileId
FROM
(
SELECT profileId
FROM profile profile2
WHERE profile2.profilePublishDate <= Now()
ORDER BY profile2.profilePublishDate DESC
LIMIT 0,40
) AS profile1
ORDER BY RAND()
LIMIT 0,20
)
UNION
(
SELECT profileId
FROM profile profile4
WHERE profileId NOT IN (
SELECT * FROM
(
SELECT profileId
FROM profile profile4
WHERE profile4.profilePublishDate <= Now()
ORDER BY profile4.profilePublishDate DESC
LIMIT 0,40
) AS temp2
)
ORDER BY RAND()
LIMIT 0,40
)
) TEMP
ORDER BY RAND();
没有找到相关结果
已邀请:
2 个回复
末钉蹈泰唬
SELECT * FROM ( **(** SELECT profileId FROM ( SELECT profileId FROM profile profile2 WHERE profile2.profilePublishDate <= Now() ORDER BY profile2.profilePublishDate DESC LIMIT 0,40 ) AS profile1 ORDER BY RAND() LIMIT 0,20 **)** UNION ( SELECT profileId FROM profile profile4 WHERE profileId NOT IN ( SELECT profileId FROM profile profile4 WHERE profile4.profilePublishDate <= Now() ORDER BY profile4.profilePublishDate DESC LIMIT 0,40 ) ORDER BY RAND() LIMIT 0,40 ) ) TEMP ORDER BY RAND();硕歌沙
。但是，这非常复杂（许多随机的语句仅在需要时使用），因此将有序数据集（即just9ѭ和中的）收集到您所用语言的数组中可能会减少处理器的工作量选择并随机化该数组的顺序。