mysql - 如何组合两个不同的mysql where子句，每个子句产生5行

Question

我使用以下两个查询从每个查询中获取 5 行并显示在一个列表中。有没有可能组合两个查询的方法，这样我就不必单独运行它们。但仍然会得到相同的结果，第一个 where 子句的结果和第二个 where 子句的结果相同。

    SELECT *
    FROM
    institutes
    LEFT JOIN city ON institutes.city_id = city.city_id 
    LEFT JOIN district ON city.district_id = district.district_id 
    WHERE
    city.city_id = $current_city_id ORDER BY RAND() DESC LIMIT 5


    SELECT *
    FROM
    institutes
    LEFT JOIN city ON institutes.city_id = city.city_id 
    LEFT JOIN district ON city.district_id = district.district_id 
    WHERE
    district.district_id = $current_district_id ORDER BY RAND() DESC LIMIT 5 

Answer 1

将 UNION ALL 与包装器 SELECT 一起使用:

SELECT * FROM (
    SELECT *
    FROM institutes
    LEFT JOIN city ON institutes.city_id = city.city_id 
    LEFT JOIN district ON city.district_id = district.district_id 
    WHERE city.city_id = $current_city_id
    ORDER BY RAND() DESC LIMIT 5
) x1
UNION ALL
SELECT * FROM (
    SELECT *
    FROM institutes
    LEFT JOIN city ON institutes.city_id = city.city_id 
    LEFT JOIN district ON city.district_id = district.district_id 
    WHERE district.district_id = $current_district_id
    ORDER BY RAND() DESC LIMIT 5 
) x2

包装器 SELECT 是必需的，因为您不能在同一级别组合 ORDER BY ... LIMIT 和 UNION，但是您可以将ORDER BY ... LIMIT放入子查询。

UNION ALL 是必需的，而不仅仅是 UNION，因为 UNION 会删除重复项，而您的问题需要保留重复项。

UNION ALL 也比 UNION 更快(因为它不需要重复数据删除，这通常需要排序)，但在这种情况下，行数太少以至于性能上的差异不会被注意到，但在未来的应用程序中记住这一点是很好的。