MYSQL 如何查询各唯一大洲对应的死亡人数最高的国家

错误写法原因 #

• 仅按continent分组的查询仅能返回分组字段和聚合结果，无法直接关联对应国家信息
• 按continent+country分组的查询会统计每个国家的最高死亡数，因此每个大洲会返回多条国家数据

正确实现方案 #

方案1：窗口函数实现（推荐，适配MySQL8+、PostgreSQL、SQL Server等主流新版数据库）

通过RANK()窗口函数按大洲分区、对总死亡数倒序排名，筛选每个分区排名第一的条目即可：

WITH country_death_rank AS (
    SELECT
        continent,
        country,
        total_deaths,
        RANK() OVER(PARTITION BY continent ORDER BY total_deaths DESC) AS rk
    FROM covid.covid_
    WHERE continent IS NOT NULL
)
SELECT 
    continent,
    country,
    total_deaths AS AllDeaths
FROM country_death_rank
WHERE rk = 1
ORDER BY AllDeaths DESC;

方案2：子查询关联实现（适配不支持窗口函数的旧版数据库）

先查询每个大洲的最高死亡数，再和原表关联匹配对应国家：

SELECT 
    a.continent,
    a.country,
    a.total_deaths AS AllDeaths
FROM covid.covid_ a
INNER JOIN (
    SELECT continent, MAX(total_deaths) AS max_death
    FROM covid.covid_
    WHERE continent IS NOT NULL
    GROUP BY continent
) b ON a.continent = b.continent AND a.total_deaths = b.max_death
WHERE a.continent IS NOT NULL
ORDER BY AllDeaths DESC;

补充说明：如果同一大洲存在多个国家总死亡数并列最高的情况，上述两种写法都会返回所有并列的国家。若只需要返回其中一个，可将方案1中的RANK()替换为ROW_NUMBER()，并在排序规则后追加额外排序条件，例如ORDER BY total_deaths DESC, country ASC即可。

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