SQL中使用MAX()分组取最高人口城市为何存在非确定性问题

这是非常典型的GROUP BY非聚合列陷阱，和max()函数本身没关系，问题出在对分组语义的错误理解上：
标准SQL规定，使用GROUP BY做分组聚合时，SELECT列表里的字段要么必须出现在GROUP BY子句中，要么必须被聚合函数包裹。你写的SQL里，c.name（城市名称）既没有加入GROUP BY，也没有被任何聚合函数修饰，本身就属于不符合SQL标准的写法——只有少数数据库（比如关闭了ONLY_FULL_GROUP_BY模式的旧版MySQL、部分配置下的SQLite）会放行这种语法，但数据库不会为你返回「和max(population)对应行的城市名」，只会在当前分组的所有城市行里随机挑一个c.name返回。
你测试时结果正确纯属巧合，本质是数据库扫表时刚好先读到了人口最高的那行城市，不代表这个写法逻辑成立。

• 结果错配：只要分组扫描时第一个读到的城市不是该州人口最高的城市，就会返回「州名正确、人口最大值正确、城市名完全错误」的离谱结果，比如返回「加州、奥克兰、400万」，但400万人口实际属于洛杉矶
• 结果不稳定：哪怕底层数据完全没改，只要你加了新索引、数据库做了表碎片整理重整了数据物理存储顺序、升级数据库版本调整了分组执行逻辑，同一个SQL返回的城市名都可能变化，结果完全不可复现
• 并列场景异常：如果同一个州有多个城市人口并列第一，这个写法不会返回所有并列城市，只会随机返回其中一个，没有任何确定性

用窗口函数可以明确定义分组排序规则，完全避免上述非确定性问题：

WITH ranked_cities AS (
    SELECT
        s.name AS state_name,
        c.name AS city_name,
        c.population,
        -- 按州分区，按人口倒序排号
        ROW_NUMBER() OVER (
            PARTITION BY c.state_code
            ORDER BY c.population DESC
        ) AS rk
    FROM cities c
    LEFT JOIN states s 
    ON c.state_code = s.code
)
-- 只取每个州排名第一的城市
SELECT state_name, city_name, population
FROM ranked_cities
WHERE rk = 1
ORDER BY state_name;

如果需要返回同一州内人口并列最高的所有城市，把上面的ROW_NUMBER()替换成RANK()即可。

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