如何使用SQL提取每个国家TOP10亿万富翁并计算其净资产总和

你原来的代码存在两处核心问题：

1. 没有预先筛选每个国家净资产排名前10的亿万富翁，直接统计的是该国所有亿万富翁的净资产总和
2. 使用SUM() OVER()开窗函数会返回和原表行数一致的结果，每个国家对应的总和会重复出现，不会按国家聚合为单条结果

通用标准写法（支持MySQL8.0+、PostgreSQL、SQL Server等所有支持窗口函数的数据库） #

WITH ranked_billionaires AS (
    SELECT 
        Country,
        `Net Worth`,
        -- 按国家分区，净资产降序排名，ROW_NUMBER保证最多取10人，如需包含并列第10可替换为RANK()
        ROW_NUMBER() OVER (PARTITION BY Country ORDER BY `Net Worth` DESC) AS rank_num
    FROM Billionaires
)
SELECT 
    Country,
    SUM(`Net Worth`) AS TotalNetworth
FROM ranked_billionaires
WHERE rank_num <= 10
GROUP BY Country;

MySQL5.x及不支持CTE/窗口函数的数据库写法 #

执行前先初始化变量：

SET @rank_num = 0, @current_country = '';

再执行查询：

SELECT 
    Country,
    SUM(`Net Worth`) AS TotalNetworth
FROM (
    SELECT 
        Country,
        `Net Worth`,
        @rank_num := IF(@current_country = Country, @rank_num + 1, 1) AS rank_num,
        @current_country := Country
    FROM Billionaires
    ORDER BY Country, `Net Worth` DESC
) AS ranked_billionaires
WHERE rank_num <= 10
GROUP BY Country;

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