SQL按ID分组如何获取players_level最大的对应整行数据

问题说明 #

待处理数据表存在重复ID记录，表包含JSON类型字段，其中players_level字段存储玩家等级。需求为按ID去重，每个ID仅保留players_level取值最大的完整行数据。

原始示例数据 #

预期输出 #

每个ID返回1条players_level最高的完整记录，共3条结果：

• id=1：保留players_level=0、score=0的记录
• id=16：保留players_level=3、score=500的记录，剔除level=2的重复记录
• id=17：保留players_level=1、score=0的记录

实现方案 #

直接使用GROUP BY搭配MAX(players_level)只能拿到每个ID对应的最高等级值，无法关联返回同一条记录下的score、JSON类型players等其他字段，以下两种方案可正确返回目标结果：

方案1：窗口函数实现（支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库新版本） #

通过ROW_NUMBER()窗口函数按ID分区，分区内按players_level降序排序，最终取每个分区排序第一位的行即可：

SELECT id, score, players, players_level
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY players_level DESC) AS row_sort
    FROM 替换为你的实际表名
) temp
WHERE row_sort = 1;

说明：如果同一ID下存在多条players_level同为最大值的记录，该写法默认返回其中1条；如果需要返回所有最高等级的同ID记录，可将ROW_NUMBER()替换为RANK()

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

先通过聚合查询拿到每个ID对应的最高players_level值，再关联原表匹配ID和对应最高等级的完整行：

SELECT t1.*
FROM 替换为你的实际表名 t1
JOIN (
    SELECT id, MAX(players_level) AS max_level
    FROM 替换为你的实际表名
    GROUP BY id
) t2
ON t1.id = t2.id AND t1.players_level = t2.max_level;

说明：如果同一ID下存在多条players_level同为最大值的记录，该写法会返回所有符合最高等级的记录 #

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