MySQL UNION 2查询包含ORDER BY-Java 学习之路

我正在尝试UNION两个包含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()

UPDATE: This is the solution based on Abhay's help below (thanks Abhay):

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();

我所做的改变是：

属于UNION的每个查询都应该用括号括起来（第一个查询以粗体显示;第二个查询已被封装）
删除了第二个查询的别名 profile3
为最终 ORDER BY RAND() ，您必须为派生表创建UNION结果集;我给它 TEMP 作为别名

我没有测试上面的查询，但我希望它应该工作 . 让我知道你的发现 .

回复于 2024-05-08T22:55:50+08:00

5

您可以将整个查询包含在另一个选择中（以使其成为子选择）并在该结果集上包含 ORDER BY RAND() . 然而，这很复杂（当你只需要一个时，很多随机 ORDER BY 语句），所以收集有序数据集（即只有 profile1 和 profile3 的 UNION ）的处理器密集程度可能会减少到您所选语言的数组中 . 随机化那个数组的顺序 .

回复于 2024-05-08T22:55:50+08:00

MySQL UNION 2查询包含ORDER BY

2 回答

相关问题