牛客SQL136——每类试卷得分前3名

题目描述
https://www.nowcoder.com/practice/255aa1863fe14aa88694c09ebbc1dbca

思路：找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者
1.先选出每个人每类的最高和最低分

select e.tag,r.uid,max(r.score) max_score,min(r.score) min_score
from exam_record as r join examination_info as e on r.exam_id=e.exam_id
where r.score is not null
group by e.tag,r.uid

---

2.在根据每个试卷进行排序

select *,ROW_NUMBER() over(partition by t.tag order by t.max_score desc,t.min_score desc,t.uid desc) ranking
from (
	select e.tag,r.uid,max(r.score) max_score,min(r.score) min_score
	from exam_record as r join examination_info as e on r.exam_id=e.exam_id
	where r.score is not null
	group by e.tag,r.uid
) as t

---

3.再选出前三

select tag,uid,ranking
from(
    select *,ROW_NUMBER() over(partition by t.tag order by t.max_score desc,t.min_score desc,t.uid desc) ranking
    from (
        select e.tag,r.uid,max(r.score) max_score,min(r.score) min_score
        from exam_record as r join examination_info as e on r.exam_id=e.exam_id
        where r.score is not null
        group by e.tag,r.uid
    ) as t
)as u
where ranking<=3