mysql - ORDER BY RAND() 在 UNION 中不起作用

Question

我在 UNION 中使用 ORDER BY RAND() 时遇到一些问题。它似乎不是在 UNION 内的单独查询中随机排序的。

我只想对子查询进行排序，而不是立即对整个查询进行排序，因为顺序需要是子查询 1 > 子查询2 > 子查询3。

我尝试过 MySQL 的其他一些随机函数，例如 NEWID() 和 UUID()，但它们似乎也不起作用。

我使用 WordPress 来获取结果。

SELECT *
        FROM (
                (SELECT act.wordpress_id,
                        act.title,
                        act.main_image,
                        loc.id,
                        "activities" AS type,
                        loc.town,
                        loc.village
                FROM tha_wc_activity act
                INNER JOIN tha_wc_location loc
                ON act.location_id = loc.id
                WHERE
                  act.active = 1 AND
                  act.visible_in_activities_overview = 1 AND
                  loc.id = 1
                ORDER BY RAND()
                )
            UNION
                (SELECT act.wordpress_id,
                        act.title,
                        act.main_image,
                        loc.id,
                        "activities" AS type,
                        loc.town,
                        loc.village
                FROM tha_wc_activity act
                INNER JOIN tha_wc_location loc
                ON act.location_id = loc.id
                WHERE
                  act.active = 1 AND
                  act.visible_in_activities_overview = 1 AND
                  loc.village = "villageName"
                ORDER BY RAND()
                )
            UNION
                (SELECT act.wordpress_id,
                        act.title,
                        act.main_image,
                        loc.id,
                        "activities" AS type,
                        loc.town,
                        loc.village
                FROM tha_wc_activity act
                INNER JOIN tha_wc_location loc
                ON act.location_id = loc.id
                WHERE
                  act.active = 1 AND
                  act.visible_in_activities_overview = 1 AND
                  loc.town = "townName"
                ORDER BY RAND()
                )
            ) AS act
            WHERE act.wordpress_id != 1
            LIMIT 0, 15)

更新:感谢 Gordon Linoff，我发现我必须对单独的子查询施加限制，否则它将无法获得有序结果

Answer 1

我不太清楚你的问题是什么。您在子查询中使用 order by rand() ，然后选择所有结果。获取所有行就是所有行，无论其顺序如何。

然后，您将使用 union 来删除重复项并具有其他逻辑。

大概，您想要这样的东西:

select *
from (<subquery 1>
      union
      <subquery 2>
      . . .
     ) t
where . . .
order by rand()
limit 15;

不需要对子查询进行排序，除非您使用limit。

请注意，您可能希望使用 union all 而不是 union - 除非您明确希望承担删除重复项的开销。