按游戏与关卡分组，结合双排序条件获取用户最优游戏成绩

嘿，我来帮你梳理下问题所在，以及给出更靠谱的解决方案！

问题背景 #

你有两张核心表：

• scores表：记录用户游戏成绩，字段包含id、id_user、id_game、game_level、score、time
• games表：存储游戏基础信息，字段有id、name、levels、description等

你的需求很明确：针对指定用户（比如id_user=4），获取其每个id_game+game_level组合的最优成绩（优先按score降序，同分时按time升序），同时关联games表拿到对应游戏的信息。

你遇到的问题 #

你先尝试了排序查询，结果看起来是对的：

SELECT id_game , game_level , score , time 
FROM scores 
WHERE id_user = 4 
ORDER BY id_game asc , game_level asc , score desc , time asc

但把这个排序后的结果作为派生表进行分组时，却没得到预期的每组最优记录：

SELECT * from ( 
    SELECT id_game , game_level , score , time 
    FROM scores 
    WHERE id_user = 4 
    ORDER BY id_game asc , game_level asc , score desc , time asc 
) A 
GROUP BY id_game , game_level

调整排序逻辑也没用，这是因为你踩了SQL分组的一个常见坑。

样本数据 #

你的测试数据如下：

+----+---------+------------+-------+-------+---------+
| id | id_game | game_level | score | time  | id_user |
+----+---------+------------+-------+-------+---------+
| 1  | 1       | 1          | 70    | 01:20 | 4       |
| 2  | 1       | 1          | 70    | 01:17 | 4       |
| 3  | 1       | 2          | 66    | 00:44 | 4       |
| 4  | 1       | 2          | 64    | 00:22 | 4       |
| 5  | 1       | 3          | 100   | 03:24 | 4       |
| 6  | 1       | 4          | 99    | 01:29 | 4       |
| 7  | 1       | 4          | 99    | 01:23 | 4       |
+----+---------+------------+-------+-------+---------+

预期结果 #

你想要的最终结果是：

+---------+------------+-------+-------+
| id_game | game_level | score | time  |
+---------+------------+-------+-------+
| 1       | 1          | 70    | 01:17 |
| 1       | 2          | 66    | 00:44 |
| 1       | 3          | 100   | 03:24 |
| 1       | 4          | 99    | 01:23 |
+---------+------------+-------+-------+

问题根源 #

你之前的分组方法之所以失效，核心原因是：在绝大多数SQL数据库中，GROUP BY的逻辑是先分组，再从每个分组中随机选择非聚合字段的值。派生表中的ORDER BY会被数据库忽略，因为分组操作的优先级高于排序，数据库不会帮你保留排序后的第一条记录。

简单说：你以为是先排序再取每组第一条，但实际是先分组，再从每组里随便拿一条数据，这自然得不到最优结果。

推荐的解决方案 #

这里推荐两种方法，优先用窗口函数，逻辑清晰且效率高；如果你的数据库不支持窗口函数，再用关联子查询。

方法一：窗口函数ROW_NUMBER()（推荐） #

这是处理这类“分组取Top1”需求的标准方案，几乎所有现代数据库（MySQL 8.0+、PostgreSQL、SQL Server等）都支持：

SELECT 
    s.id_game,
    s.game_level,
    s.score,
    s.time,
    g.name,
    g.description -- 按需添加games表的其他字段
FROM (
    SELECT 
        *,
        -- 按游戏+关卡分组，组内按分数降序、时间升序排，给每条记录标行号
        ROW_NUMBER() OVER (
            PARTITION BY id_game, game_level 
            ORDER BY score DESC, time ASC
        ) AS rn
    FROM scores
    WHERE id_user = 4
) s
-- 关联games表获取游戏信息
JOIN games g ON s.id_game = g.id
-- 只取每个分组的第一条（最优记录）
WHERE s.rn = 1
-- 按游戏和关卡排序，让结果更规整
ORDER BY s.id_game ASC, s.game_level ASC;

逻辑拆解

1. 窗口函数分组排序：PARTITION BY id_game, game_level把数据按游戏ID和关卡分成一个个小组；ORDER BY score DESC, time ASC在每个小组内按你的规则排序，然后给每条记录分配一个行号rn，小组内的最优记录行号就是1。
2. 筛选最优记录：外层查询只保留rn=1的记录，也就是每个游戏关卡的最优成绩。
3. 关联游戏表：通过id_game关联games表，直接获取你需要的游戏信息。

方法二：关联子查询（兼容旧版数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.7及更早版本），可以用这种方式：

SELECT 
    s.id_game,
    s.game_level,
    s.score,
    s.time,
    g.name,
    g.description
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