查询各大洲人口最多国家的SQL优化方案咨询

world表存储全球国家基础信息，每行记录包含三个字段：

• name：国家名称
• continent：国家所属大洲
• population：国家人口
  需求为查询返回每个大洲中人口最多的国家对应的continent和name字段。你当前写的CTE关联聚合结果的写法逻辑正确，可以跑通结果，但还有更简洁、执行效率更高的实现方案。

你当前的实现逻辑是先通过CTE算出每个大洲的最高人口值，再把聚合结果和原表做关联匹配对应国家，存在两个可优化的地方：

• 用了逗号分隔表名的隐式内连接写法，可读性差，后续维护时很容易漏写连接条件产生笛卡尔积，更推荐用显式JOIN写法
• 执行时需要两次访问world表：第一次是CTE中分组聚合计算最高人口，第二次是关联时回表匹配对应国家，数据量大时额外开销比较明显

1. 窗口函数写法（首选，简洁高效） #

目前主流的MySQL 8.0+、PostgreSQL、SQL Server等数据库都支持窗口函数，用ROW_NUMBER()实现只需要一次表扫描，不需要构建临时聚合结果集，代码可读性也更高：

SELECT continent, name
FROM (
    SELECT
        continent,
        name,
        ROW_NUMBER() OVER (PARTITION BY continent ORDER BY population DESC) AS rank_num
    FROM world
) ranked
WHERE rank_num = 1;

补充：如果同一个大洲存在多个人口完全相等、并列第一的国家，上面的写法只会返回其中一条。如果需要把所有并列第一的国家都返回，把ROW_NUMBER()替换成RANK()即可。

2. 关联子查询写法（兼容老版本数据库） #

如果你用的是不支持窗口函数的老版本数据库，可以用相关子查询实现，不需要写CTE，逻辑更紧凑，在continent、population字段有联合索引的场景下性能也很好：

SELECT continent, name
FROM world w
WHERE population = (
    SELECT MAX(population)
    FROM world
    WHERE continent = w.continent
);

这个写法和你原有逻辑完全一致，会返回所有大洲中人口并列第一的国家，省掉了CTE临时结果的构建和两表关联的开销。

• 窗口函数写法：执行时只需要对表做一次分区排序，数据库优化器可以生成非常高效的执行计划，数据量越大优势越明显，是这类「分组取极值对应记录」场景的工业界首选方案
• 关联子查询写法：有合适索引的情况下，子查询可以直接通过索引快速定位每个大洲的最高人口值，性能优于CTE两表关联的写法
• 原CTE写法：需要先聚合生成临时结果集，再做两表匹配，涉及两次表访问和临时表写入，三者中性能最低

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