SQL按STUDENT/TIME分组取最大值并保留非分组列ID的问题

原始表 TABLE1 #

ID  STUDENT SCORE   TIME
A   1       9       1
A   1       8       2
B   1       0       1
B   1       10      2
B   1       7       3
C   2       5       1
C   2       1       2
C   2       0       3
D   3       1       1
E   3       0       1
D   3       4       2
D   3       4       3
E   3       9       2
F   4       6       1
G   4       6       1

期望结果表 WANT #

ID  STUDENT MAXSCORE    TIME
A   1       9           1
B   1       10          2
B   1       7           3
C   2       5           1
C   2       1           2
C   2       0           3
D   3       1           1
E   3       9           2
D   3       4           3
F   4       6           1

需求说明 #

针对每个STUDENT/TIME组合，选取该组合下SCORE值最大的行，同时保留对应的ID列，得到上述WANT表结构。

尝试的SQL及问题 #

尝试执行以下SQL语句：

select ID, STUDENT, MAX(SCORE) AS MAXSCORE, TIME
from TABLE1
group by STUDENT, TIME

但该语句无法正确包含ID列——因为分组字段只有STUDENT和TIME，ID不在分组字段中，也没有被聚合函数处理，多数SQL数据库会直接报错，或者返回不确定的ID值。

解决方案 #

方法1：使用ROW_NUMBER()窗口函数（只保留一行最高分） #

SELECT ID, STUDENT, SCORE AS MAXSCORE, TIME
FROM (
    SELECT 
        ID, STUDENT, SCORE, TIME,
        ROW_NUMBER() OVER (PARTITION BY STUDENT, TIME ORDER BY SCORE DESC) AS rn
    FROM TABLE1
) t
WHERE rn = 1;

• 逻辑：按STUDENT和TIME分组，每组内按SCORE降序排序，取排序后第一行（即最高分的行）。如果同一组合下有多个相同最高分的行，只会保留其中一行（可通过添加ORDER BY SCORE DESC, ID来稳定排序，比如优先保留ID更小的行）。

方法2：使用RANK()窗口函数（保留所有并列最高分） #

如果需求是保留同一STUDENT/TIME下所有最高分的行，可改用RANK()：

SELECT ID, STUDENT, SCORE AS MAXSCORE, TIME
FROM (
    SELECT 
        ID, STUDENT, SCORE, TIME,
        RANK() OVER (PARTITION BY STUDENT, TIME ORDER BY SCORE DESC) AS rk
    FROM TABLE1
) t
WHERE rk = 1;

• 逻辑：RANK()会给相同最高分的行分配相同排名，因此rk=1会返回所有并列最高分的行。

方法3：关联子查询（兼容老版本数据库） #

如果数据库不支持窗口函数，可先通过子查询获取每组最高分，再关联原表获取对应ID：

SELECT t1.ID, t1.STUDENT, t1.SCORE AS MAXSCORE, t1.TIME
FROM TABLE1 t1
INNER JOIN (
    SELECT STUDENT, TIME, MAX(SCORE) AS MAX_SCORE
    FROM TABLE1
    GROUP BY STUDENT, TIME
) t2 ON t1.STUDENT = t2.STUDENT AND t1.TIME = t2.TIME AND t1.SCORE = t2.MAX_SCORE;

• 逻辑：此方法效果和RANK()一致，会返回所有并列最高分的行；若只需保留一行，可在关联后添加筛选条件（比如WHERE t1.ID = (SELECT MIN(ID) FROM TABLE1 WHERE STUDENT=t2.STUDENT AND TIME=t2.TIME AND SCORE=t2.MAX_SCORE)）。

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