题解 | #每类试卷得分前3名#

本题使用了窗口函数，row_number

select tag,uid,row_number() over (
#根据tag对数据进行排序，排序规则按照题目要求如下：求出rank排名   
partition by tag 
order by max(score)desc ,min(score)desc ,uid desc) ranking
from exam_record er join examination_info ei on er.exam_id = ei.exam_id
# 因为题目要求 求出最大分数和最小分数，所以以tag和uid为分组求最大最小分数
group by ei.tag,er.uid

select * from
(select tag,uid,row_number(), over (partition by tag order by max(score)desc ,min(score)desc ,uid desc) ranking
from exam_record er join examination_info ei on er.exam_id = ei.exam_id group by ei.tag,er.uid) t
where ranking<=3;
再以此为基础上，把前三名过滤出来即可