包含ORDER BY的MySQL UNION 2查询 [英] MySQL UNION 2 queries containing ORDER BYs

问题描述

我正在尝试联合两个都包含ORDER BY的查询. 如我所发现的，您不能通过属于UNION的查询进行排序. 我只是不知道该怎么办呢.让我解释一下我要做什么.

I am trying to UNION two queries which both contain ORDER BY's. As I have discovered, you can not order by queries that are part of a UNION. I just don't know how else to do this query then. Let me explain what I'm trying to do.

1. 我正在尝试选择40个最新的配置文件，然后从该列表中选择20个随机集.然后，我希望将其与:
2. 选择40个随机配置文件，这些配置文件不属于第一组中查询的原始40个最新配置文件
3. 随机订购整套60条记录.

我知道使用Rand()函数带来的效率影响

I am aware of the efficiency ramifications of using the Rand() function

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

更新:这是基于以下Abhay的帮助(感谢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();

推荐答案

这是您的解决方案:

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

我所做的更改是:

1. 属于UNION的每个查询都应括在方括号中(第一个查询以粗体显示；第二个查询已被封装)
2. 为第二个查询删除了别名profile3
3. 对于最后的ORDER BY RAND()，必须将UNION结果集创建到派生表.我给它TEMP作为别名

1. each of your queries that are part of UNION should be encased in brackets (shown in bold for the first query; the second is already encased)
2. removed the alias profile3 for your 2nd query
3. for the final ORDER BY RAND(), you must create the UNION resultset to a derived table; I have given it TEMP as the alias

我尚未测试以上查询，但希望它能正常工作.让我知道你的发现.

I haven't tested the above query but I hope it should work. Let me know your findings.

这篇关于包含ORDER BY的MySQL UNION 2查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！