如何用SQL按大洲分组查询人口最多/最少国家并输出单表

已知条件 #

用到的world表包含以下字段：

• population：存储各个国家的人口数值
• name：存储所有国家的名称
• continent：存储各个国家所属的大洲

输出要求 #

返回单张结果表，固定表头为：

• Continent：大洲名称
• Most_populous：对应大洲内人口最多的国家名称
• Least_populous：对应大洲内人口最少的国家名称

参考示例对应关系：非洲行人口最多国家为加纳、人口最少国家为xyz；亚洲行人口最多国家为中国、人口最少国家为abc。

原有写法问题 #

之前尝试的多层嵌套SQL执行报错，原语句如下：

SELECT DISTINCT continent
   , (SELECT x.name
      FROM world x 
      WHERE x.population = (SELECT max(y.population) 
                            FROM world y 
                            WHERE x.continent = y.continent)) AS most_populous
   , (SELECT z.name
      FROM world z
      WHERE z.population = (SELECT min(a.population)
                            FROM world a
                            WHERE a.continent=z.continent)) AS least_populous 
FROM world;     

报错核心原因有两个：

1. 两个取国家名的关联子查询没有和外层查询的continent字段做关联绑定，子查询的计算范围没有限定在外层当前行对应的大洲
2. 如果某个大洲存在多个国家人口数恰好等于大洲人口最大值/最小值，标量子查询会返回多行结果，直接触发SQL执行错误。

简便实现方案 #

用窗口函数实现逻辑最清晰，嵌套层数少，也方便处理同值场景，通用写法如下：

SELECT 
  continent AS Continent,
  MAX(CASE WHEN rn_max = 1 THEN name END) AS Most_populous,
  MAX(CASE WHEN rn_min = 1 THEN name END) AS Least_populous
FROM (
  SELECT 
    continent,
    name,
    ROW_NUMBER() OVER(PARTITION BY continent ORDER BY population DESC, name) AS rn_max,
    ROW_NUMBER() OVER(PARTITION BY continent ORDER BY population ASC, name) AS rn_min
  FROM world
) t
GROUP BY continent;

写法逻辑说明：

• 内层查询通过两个窗口函数，按大洲分区后，分别给分区内的国家按人口降序、升序打行号，同人口的场景下按国家名称排序保证结果稳定
• 外层按大洲分组，直接取行号为1的国家名，就是对应大洲的人口最多、最少国家
• 如果需要保留人口并列第一/倒数第一的结果，把ROW_NUMBER()替换为RANK()即可，需要拼接多个国家名的话可以配合字符串聚合函数使用。

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