SQL查询需求：如何获取各roundID下的Top3用户分数

我有一个记录高分的scores表，分数按轮次（round 1、round 2等）追踪，示例表结构及数据如下：

scores表示例数据 #

该表可包含更多轮次及用户。我需要提取每个roundID下的Top3用户分数，当前使用的SELECT语句如下：

select `scores`.`score`, `users`.`username`, `scores`.`roundID` 
FROM `scores` 
INNER JOIN `users` on `users`.`user_id` = `scores`.`userID` 
ORDER BY `scores`.`score` DESC LIMIT 3;

但该语句返回全局Top3结果：

当前全局Top3结果 #

而我期望得到每个roundID下的Top3分数，结果如下：

期望的每个轮次Top3结果 #

解决方案 #

方法1：使用窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等）

利用ROW_NUMBER()窗口函数按轮次分组排序，筛选排名前3的记录，这是最简洁高效的方式：

SELECT score, username, roundID
FROM (
    SELECT 
        s.score,
        u.username,
        s.roundID,
        -- 按轮次分组，组内按分数降序生成排名
        ROW_NUMBER() OVER (PARTITION BY s.roundID ORDER BY s.score DESC) AS rank_num
    FROM scores s
    INNER JOIN users u ON u.user_id = s.userID
) AS ranked_scores
WHERE rank_num <= 3
-- 按轮次和排名排序，保证结果顺序清晰
ORDER BY roundID, rank_num;

补充说明：

• 如果需要允许分数相同的记录并列排名（比如两个相同分数都算Top3），可以把ROW_NUMBER()替换为RANK()或DENSE_RANK()：
  • RANK()：相同分数会获得相同排名，后续排名会跳过对应的位数（比如1,1,3）
  • DENSE_RANK()：相同分数获得相同排名，后续排名不会跳过（比如1,1,2）

方法2：适用于不支持窗口函数的低版本MySQL

如果你的MySQL版本低于8.0，可以通过关联子查询计算每个记录在轮次内的排名：

SELECT 
    s1.score,
    u.username,
    s1.roundID
FROM scores s1
INNER JOIN users u ON u.user_id = s1.userID
WHERE (
    -- 统计当前轮次中分数大于等于当前记录的不同分数数量
    SELECT COUNT(DISTINCT s2.score)
    FROM scores s2
    WHERE s2.roundID = s1.roundID AND s2.score >= s1.score
) <= 3
ORDER BY s1.roundID, s1.score DESC;

补充说明：

• 若需要严格取每个轮次的前3条记录（即使分数相同也只保留3条），可以将子查询中的COUNT(DISTINCT s2.score)改为COUNT(*)。

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