数据准备
CREATE TABLE Student (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE Test (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE TestAttempt (
id INT PRIMARY KEY,
studentId INT,
testId INT,
score INT,
FOREIGN KEY (studentId) REFERENCES Student(id),
FOREIGN KEY (testId) REFERENCES Test(id)
);
解题思路首先处理一个学生在同一场考试中有多条记录，通过testid,studentid选择最高分数。
select testid,max(score) as score,studentid from testattempt ta group by testid,studentid；
然后再使用窗口函数RANK()对每个考试的分数进行排序，并为每个考试的每个学生分配一个排名。然后，查询选择排名在前三名的学生，并按考试ID和分数降序排序结果。
SELECT
testid,
StudentId,
Score,
RANK() OVER(PARTITION BY testid ORDER BY Score DESC) as Ranka
FROM
testattemptm
完整代码：
with testattemptm
as (select testid,max(score) as score,studentid from testattempt ta group by testid,studentid),
RankedScores AS (
SELECT
testid,
StudentId,
Score,
RANK() OVER(PARTITION BY testid ORDER BY Score DESC) as Ranka
FROM
testattemptm
)
SELECT
testid,
StudentId, t.name,st.name,
Score
FROM
RankedScores rs
left join test t on rs.testid=t.id
left join student st on rs.studentid=st.id
WHERE
Ranka <= 3
ORDER BY
testid,
Score DESC;
