关联表时排除重复记录的列求和及人口占比计算问题

现有两张表：

• Cities表：字段为城市名称name、人口pop
• Buildings表：字段为是否为学校is_school、所属城市city

需求：计算居住在设有学校的城市中的人口占比。

表数据 #

Cities表 #

Buildings表 #

错误SQL及结果 #

执行以下SQL时，因B市存在多条学校记录，人口被重复累加，得到错误结果：

SELECT
  SUM(CASE WHEN building.is_school = true THEN city.pop ELSE 0 END) school,
  SUM(city.pop) total
FROM city
LEFT JOIN building ON building.city = city.name;

错误输出：

期望正确结果 #

当前可行但繁琐的实现 #

通过子查询可得到正确结果，但写法冗余：

SELECT
  SUM(CASE WHEN city.name in (
    SELECT city.name
    FROM city
    LEFT JOIN building ON building.city = city.name
    WHERE building.is_school = true
  ) THEN city.pop ELSE 0 END),
  SUM(city.pop)
FROM city;

求更简洁的SQL实现方式。

以下几种方式均可解决重复累加问题，且代码更简洁易读：

1. 使用EXISTS子查询（推荐） #

利用EXISTS判断城市是否存在学校建筑，无需关联所有记录，性能更优：

SELECT
  SUM(CASE WHEN EXISTS (
    SELECT 1
    FROM building
    WHERE building.city = city.name
      AND building.is_school = true
  ) THEN city.pop ELSE 0 END) school,
  SUM(city.pop) total
FROM city;

2. 先聚合Buildings表再关联 #

先对Buildings表去重，筛选出所有有学校的城市，再与Cities表关联计算：

SELECT
  SUM(CASE WHEN city_with_school.name IS NOT NULL THEN city.pop ELSE 0 END) school,
  SUM(city.pop) total
FROM city
LEFT JOIN (
  SELECT DISTINCT city AS name
  FROM building
  WHERE is_school = true
) AS city_with_school ON city.name = city_with_school.name;

3. 分组后用MAX标记城市是否有学校 #

关联后按城市分组，用MAX函数判断该城市是否存在学校记录，再汇总人口：

SELECT
  SUM(CASE WHEN has_school THEN pop ELSE 0 END) school,
  SUM(pop) total
FROM (
  SELECT
    c.name,
    c.pop,
    MAX(b.is_school) AS has_school
  FROM city c
  LEFT JOIN building b ON b.city = c.name
  GROUP BY c.name, c.pop
) AS city_summary;

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