题解 | #每类试卷得分前3名#
每类试卷得分前3名
http://www.nowcoder.com/practice/255aa1863fe14aa88694c09ebbc1dbca
# 思路：
# 1、生成每个tag每个uid的最大分数、最小分数
# 2、利用row_number按照tag分组，按照最大分数、最小分数、uid排序生成排名
# 3、查询排名小于等于3的信息
select t2.tag,t2.uid,t2.rank_score from (
select t1.tag,t1.uid,
ROW_NUMBER() over(partition by t1.tag order by t1.max_score desc,t1.min_score desc,t1.uid desc) as rank_score
from (
select e1.tag,e2.uid,max(e2.score) as max_score,min(e2.score) as min_score
from exam_record as e2 left join examination_info as e1 on
e2.exam_id=e1.exam_id group by e1.tag,e2.uid
) t1 ) t2
where t2.rank_score <= 3
查看13道真题和解析