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

bvpmtnay  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(352)

我使用以下两个查询从每个查询中获取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
wz8daaqr

wz8daaqr1#

我试过“波希米亚人”的答案,但对我不起作用。多亏了他的简单解释,通过这个链接,我能够想出下面的代码,为我工作。但我不知道是否有一种方法可以消除代码中与左连接相关的冗余

sql = '(
  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
  )
  UNION
  (
  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 10'
laik7k3q

laik7k3q2#

使用 UNION ALL 带 Package 器 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

Package 纸 SELECT 因为你不能合并 ORDER BY ... LIMIT 以及 UNION 在同一水平,但你可以把 ORDER BY ... LIMIT 在子查询中。 UNION ALL 是需要的而不仅仅是 UNION 因为 UNION 删除重复项,您的问题需要保留重复项。 UNION ALL 也比 UNION (因为它不需要重复数据消除,这通常需要排序),但在这种情况下,行太少,性能上的差异不会被注意到,但最好记住这一点,以备将来的应用程序使用。

相关问题