SQL技术求助：如何查询每个州中平均人口最多的城市？

原查询的问题 #

• 关联条件仅使用城市名，不同州可能存在同名城市，会导致数据关联错误，必须同时匹配州code和城市name
• 未关联state表，无法获取州名
• 仅按城市分组计算平均人口，无法实现「每个州仅保留平均人口最高的城市」的需求

正确查询方案 #

使用窗口函数ROW_NUMBER()按州分区，对每个州内的城市按平均人口降序排名，最终筛选出排名为1的记录：

WITH city_avg_pop AS (
    -- 第一步：计算每个城市的平均人口，关联州信息
    SELECT
        s.name AS state_name,
        c.name AS city_name,
        AVG(cp.pop) AS avg_population
    FROM state s
    JOIN city c ON s.code = c.code
    JOIN citypop cp ON c.code = cp.code AND c.name = cp.name
    GROUP BY s.name, c.name
),
ranked_cities AS (
    -- 第二步：给每个州的城市按平均人口降序排名
    SELECT
        state_name,
        city_name,
        avg_population,
        ROW_NUMBER() OVER (PARTITION BY state_name ORDER BY avg_population DESC) AS rnk
    FROM city_avg_pop
)
-- 第三步：筛选每个州排名第一的城市
SELECT
    state_name,
    city_name,
    avg_population
FROM ranked_cities
WHERE rnk = 1;

代码说明 #

1. CTE city_avg_pop：关联三张表，按州和城市分组，计算每个城市的平均人口，确保关联时同时匹配州code和城市name，避免同名城市的数据混乱
2. CTE ranked_cities：使用ROW_NUMBER()窗口函数，以州名为分区字段，按平均人口降序排序，给每个州内的城市分配排名
3. 最终查询：筛选出每个州排名为1的记录，即该州平均人口最高的城市

特殊情况处理 #

如果某州有多个城市平均人口相同且都是最高，ROW_NUMBER()会随机选一个；若想保留所有并列最高的城市，可替换为RANK()或DENSE_RANK()函数。

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