SQL取Max并包含Non-Group By COLS

Question

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

我有TABLE1，并希望WANT这样做:对于每个STUDENT/TIME，选择MAX(SCORE)

的行我试试这个::

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

但是不能包含ID

Answer 1

首先获得学生/时间的最大分数，然后连接回原始表

WITH dat
AS
(
SELECT student, time, MAX(score) AS max_score
FROM TABLE1
GROUP BY student, time
)
SELECT DISTINCT t.id, t.student, d.max_score, t.time
FROM TABLE1 t
INNER JOIN dat d 
ON t.student = d.student AND t.time = d.time AND t.score = d.max_score;

Answer 2

如果RDBMS支持窗口函数，则

with cte as (
select id,
student,
score,
time,
row_number() over (partition by student, time order by score desc) as rn
from table1)
select id, student, score, time
from cte
where rn = 1;