mysql - 如何将这些 SQL SELECT 查询组合成一个 SELECT 语句

Question

如何将这两个选择语句组合成一个查询:

SELECT SUM( incidents )  AS fires, neighborhoods AS fire_neighborhoods
FROM (
SELECT * 
FROM `fires_2009_incident_location` 
UNION ALL SELECT * 
FROM `fires_2008_incident_location`
UNION ALL SELECT * 
FROM `fires_2007_incident_location`
UNION ALL SELECT * 
FROM `fires_2006_incident_location`
) AS combo
GROUP BY fire_neighborhoods ORDER BY fires DESC



SELECT SUM( incidents )  AS adw, neighborhoods AS adw_neighborhoods
FROM (
SELECT * 
FROM `adw_2009_incident_location` 
UNION ALL SELECT * 
FROM `adw_2008_incident_location`
UNION ALL SELECT * 
FROM `adw_2007_incident_location`
UNION ALL SELECT * 
FROM `adw_2006_incident_location`
) AS combo2
GROUP BY adw_neighborhoods ORDER BY adw DESC

所以，我希望查询返回，类似于:

fire_neighborhoods  fires  adw_neighborhoods  adw
xyzNeighborhood     6      abcNeighborhood    22
jklNeighborhood     3      tuvNeighborhood    40

我想简单地合并上面两个查询的结果。这两个查询是相互独立的。一个查询的结果不会影响另一个查询的结果。我只需要一种方法将两个结果合二为一。

如果有人有任何建议，请告诉我。

谢谢。

-拉克斯米迪

Answer 1

您提供的示例表明您想要水平组合查询，但随后又声明它们是完全独立的。这些是相互矛盾的陈述，因为当记录确实相互关联时，您通常会水平组合数据。下面是我将它们水平组合的想法，但我也在下面记下了我将它们垂直组合的想法。

这取决于您希望如何将它们链接起来。如果您基于邻域进行查询，则可以在 fire_neighborhoods = adw_neighborhoods 上的两个较大查询之间进行连接，例如:

SELECT fire_neighborhoods, fires, adw
FROM (

SELECT SUM( incidents )  AS fires, neighborhoods AS fire_neighborhoods
FROM (
SELECT * 
FROM `fires_2009_incident_location` 
UNION ALL SELECT * 
FROM `fires_2008_incident_location`
UNION ALL SELECT * 
FROM `fires_2007_incident_location`
UNION ALL SELECT * 
FROM `fires_2006_incident_location`
) AS combo
GROUP BY fire_neighborhoods ORDER BY fires DESC

) AS fires
    INNER JOIN (  

SELECT SUM( incidents )  AS adw, neighborhoods AS adw_neighborhoods
FROM (
SELECT * 
FROM `adw_2009_incident_location` 
UNION ALL SELECT * 
FROM `adw_2008_incident_location`
UNION ALL SELECT * 
FROM `adw_2007_incident_location`
UNION ALL SELECT * 
FROM `adw_2006_incident_location`
) AS combo2
GROUP BY adw_neighborhoods ORDER BY adw DESC

) AS adw
    ON fires.fire_neighborhoods = adw.adw_neighborhoods

这只是一个例子。您可能需要不同的连接或其他东西才能使它适合您。

现在，您声明这两个查询是独立的，不会相互影响。如果他们真的没有共同点，您应该向每个查询添加一列以指示它来自的查询(例如，为 fire 查询添加一个常量值为 1 的列，为 adw 添加一个常量值为 2 的列询问)。然后，将两个大查询 UNION 在一起。这将以垂直方式而不是水平方式将它们结合起来。