分组内多字段排序取用户游戏最优成绩的SQL实现问题

首先，咱们来聊聊你遇到的问题：你的分组查询没得到预期结果，核心原因是GROUP BY的工作逻辑和你想的不一样。

为什么你的原方法失效？ #

在MySQL（假设你用的是MySQL）中，当你使用GROUP BY时，SELECT子句里的非聚合列（比如score、time）并不是从你子查询排序后的结果里取第一条，而是会从分组内的任意行中选取（这是MySQL的非标准行为，其他数据库甚至会直接报错）。而且，子查询里的ORDER BY在被外层GROUP BY引用时，其实不会影响分组的行选择——因为SQL的执行顺序中，GROUP BY是在ORDER BY之前处理的，子查询的排序对分组毫无意义。

所以你原以为分组会取排序后的第一条，但实际上完全不是这么回事。

最优解决方案：使用窗口函数（推荐） #

现在主流数据库（MySQL 8+、PostgreSQL、SQL Server等）都支持窗口函数，用ROW_NUMBER()可以完美解决这个问题，逻辑清晰又简洁。

下面是关联games表后的完整SQL：

SELECT 
    g.id AS game_id,
    g.name AS game_name,
    g.levels AS total_game_levels,
    g.description,
    s.id_game,
    s.game_level,
    s.score,
    s.time
FROM (
    SELECT 
        id_game,
        game_level,
        score,
        time,
        -- 按游戏+关卡分组，组内按分数降序、时间升序编号
        ROW_NUMBER() OVER (
            PARTITION BY id_game, game_level 
            ORDER BY score DESC, time ASC
        ) AS rank_num
    FROM scores
    WHERE id_user = 4
) s
-- 关联games表获取游戏信息
JOIN games g ON s.id_game = g.id
-- 只保留每组的第一条（最优成绩）
WHERE s.rank_num = 1
-- 按游戏和关卡排序，让结果更规整
ORDER BY s.id_game ASC, s.game_level ASC;

逻辑解释：

1. 内层子查询中，ROW_NUMBER()会给每个(id_game, game_level)分组里的行按规则编号：分数最高的排第一，如果分数相同，用时更短的排第一。
2. 筛选rank_num = 1的行，就得到了每个游戏关卡的最优成绩。
3. 最后关联games表，把游戏的名称、总关卡数、描述等信息一起查出来。

用你的样例数据测试，这个SQL会精准返回你期望的结果。

兼容老版本数据库的方案（无窗口函数） #

如果你用的是MySQL 8之前的版本，不支持窗口函数，可以用关联子查询的方式实现：

SELECT 
    g.id AS game_id,
    g.name AS game_name,
    g.levels AS total_game_levels,
    g.description,
    s.id_game,
    s.game_level,
    s.score,
    s.time
FROM scores s
JOIN games g ON s.id_game = g.id
WHERE s.id_user = 4
-- 核心逻辑：找不到同用户、同游戏、同关卡下，成绩更高或同分但用时更短的记录
AND NOT EXISTS (
    SELECT 1
    FROM scores s2
    WHERE s2.id_user = s.id_user
      AND s2.id_game = s.id_game
      AND s2.game_level = s.game_level
      AND (s2.score > s.score OR (s2.score = s.score AND s2.time < s.time))
)
ORDER BY s.id_game ASC, s.game_level ASC;

这个方法的思路是：找出所有“没有比它更好的成绩”的记录，剩下的就是每个组的最优结果。

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