如何按game_id分组获取每组前2条玩家得分记录？

现有玩家游戏得分表，需要按game_id分组，获取每个游戏的前两名高分玩家记录（如果组内只有1条记录，就返回那1条）。普通的LIMIT 2只能限制整个结果集的条数，没法实现分组后每组取前2条的需求。

原始表数据：

期望结果：

解决方案 #

方法1：窗口函数（推荐，支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库） #

用ROW_NUMBER()窗口函数给每组内的记录按得分排序，再筛选排名前2的：

SELECT game_id, player_id, score
FROM (
    SELECT 
        game_id, 
        player_id, 
        score,
        -- 按game_id分组，组内按score降序排，生成排名
        ROW_NUMBER() OVER (PARTITION BY game_id ORDER BY score DESC) AS rank_num
    FROM your_table_name -- 替换成你的实际表名
) ranked_data
WHERE rank_num <= 2;

如果需要处理同分并列的情况（比如两个玩家得分相同，都算第一名），可以把ROW_NUMBER()换成RANK()或DENSE_RANK()：

• RANK()：同分的记录会有相同排名，后续排名会跳过（比如1,1,3）
• DENSE_RANK()：同分记录相同排名，后续排名不跳过（比如1,1,2）

方法2：自关联查询（适用于不支持窗口函数的老版本数据库，如MySQL 5.x） #

通过自关联统计每个记录在同组内比它得分高的数量，筛选出数量小于2的记录：

SELECT t1.game_id, t1.player_id, t1.score
FROM your_table_name t1
LEFT JOIN your_table_name t2 
    ON t1.game_id = t2.game_id AND t2.score > t1.score
GROUP BY t1.game_id, t1.player_id, t1.score
HAVING COUNT(t2.player_id) < 2
ORDER BY t1.game_id, t1.score DESC;

原理是：如果一条记录在同游戏里比它得分高的记录少于2条，说明它是前两名（得分最高的记录，没有比它高的，数量为0；第二高的记录，只有1条比它高的，数量为1）。

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