如何编写SQL语句提取每个郡（county）人口最多的城市

原SQL错误原因 #

• 使用GROUP BY c.id分组时，SELECT子句中的ci.name既不属于分组键也没有被聚合函数包裹，数据库会返回该分组下随机的城市名称，无法和子查询查到的最高人口数对应
• 子查询仅返回了对应郡的最高人口数值，没有绑定对应的城市名，逻辑本身就不匹配需求

解法1：窗口函数法（推荐，兼容MySQL 8.0+/PostgreSQL/SQL Server等主流数据库） #

通过窗口函数给每个郡下的城市按人口倒序排名，直接过滤出排名第一的记录即可：

SELECT 
    ci.name AS city_name,
    ci.population AS most_populated,
    c.name AS county_name
FROM (
    SELECT 
        *,
        RANK() OVER(PARTITION BY county_id ORDER BY population DESC) AS population_rank
    FROM cities
) ci
INNER JOIN counties c ON ci.county_id = c.id
WHERE ci.population_rank = 1

说明：用RANK()会保留同人口并列第一的所有城市，如果只需要每个郡取1条，替换为ROW_NUMBER()即可。

解法2：关联子查询法（兼容MySQL 5.x等不支持窗口函数的环境） #

如果你的数据库版本不支持窗口函数，可以用子查询先拿到每个郡的最高人口，再关联匹配对应的城市和郡信息：

SELECT 
    ci.name AS city_name,
    ci.population AS most_populated,
    c.name AS county_name
FROM cities ci
INNER JOIN counties c ON ci.county_id = c.id
WHERE ci.population = (
    SELECT MAX(population) 
    FROM cities 
    WHERE county_id = ci.county_id
)

内容的提问来源于stack exchange，提问作者Jean-Luc Aubert