题解 | 每类试卷得分前3名
每类试卷得分前3名
https://www.nowcoder.com/practice/255aa1863fe14aa88694c09ebbc1dbca
select
tag,
uid,
rk as ranking
最后的筛选
from
(
select
*,
更具最高最低成绩对tag窗口内的数据进行排名
row_number() over(partition by tag order by max_score desc,min_score desc,uid desc) rk
from
(
select
ei.tag,
er.uid,
先给没人最大最小分数标在他的tag,后边
max(score) as max_score,
min(score) as min_score
from exam_record er
inner join examination_info ei
on er.exam_id = ei.exam_id
where er.score is not null
group by tag,uid
) mt
) mt1
where rk<4
