题目描述
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