SQL中如何合并两个分组SELECT查询以查询湖泊数量多于山脉的国家

合并两个统计结果生成关联表 #

将两个分组统计查询作为派生表，通过国家字段关联，使用全外连接覆盖所有有统计数据的国家，用COALESCE函数将无对应统计项的空值转为0：

SELECT 
  COALESCE(m.Country, l.Country) AS Country,
  COALESCE(m.mountain_cnt, 0) AS mountain_count,
  COALESCE(l.lake_cnt, 0) AS lake_count
FROM 
  (SELECT Country, COUNT(*) AS mountain_cnt FROM geoMountain GROUP BY Country) m
FULL OUTER JOIN 
  (SELECT Country, COUNT(*) AS lake_cnt FROM geoLake GROUP BY Country) l
ON m.Country = l.Country

如果使用的数据库不支持FULL OUTER JOIN（如低版本MySQL），可以用UNION ALL的兼容方案实现：

SELECT 
  Country,
  SUM(mountain_cnt) AS mountain_count,
  SUM(lake_cnt) AS lake_count
FROM (
  SELECT Country, COUNT(*) AS mountain_cnt, 0 AS lake_cnt FROM geoMountain GROUP BY Country
  UNION ALL
  SELECT Country, 0 AS mountain_cnt, COUNT(*) AS lake_cnt FROM geoLake GROUP BY Country
) AS temp
GROUP BY Country

筛选湖泊数量多于山脉的国家 #

在关联表的基础上增加筛选条件即可，注意你之前示例中的条件写反了，正确的判断逻辑是湖泊计数大于山脉计数：

SELECT Country FROM (
  SELECT 
    COALESCE(m.Country, l.Country) AS Country,
    COALESCE(m.mountain_cnt, 0) AS mountain_count,
    COALESCE(l.lake_cnt, 0) AS lake_count
  FROM 
    (SELECT Country, COUNT(*) AS mountain_cnt FROM geoMountain GROUP BY Country) m
  FULL OUTER JOIN 
    (SELECT Country, COUNT(*) AS lake_cnt FROM geoLake GROUP BY Country) l
  ON m.Country = l.Country
) AS result
WHERE lake_count > mountain_count

内容的提问来源于stack exchange，提问作者TrizZm4ster