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

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