题解 | #每类试卷得分前3名#
每类试卷得分前3名
https://www.nowcoder.com/practice/255aa1863fe14aa88694c09ebbc1dbca
SELECT tid, uid, ranking FROM ( SELECT tid, uid, RANK() OVER(PARTITION BY tid ORDER BY max_score DESC, min_score DESC, uid DESC) AS ranking FROM ( SELECT ei.tag AS tid, er.uid AS uid, min(er.score) AS min_score, max(er.score) AS max_score FROM examination_info ei JOIN exam_record er ON ei.exam_id = er.exam_id GROUP BY ei.tag, er.uid ) T ) TT WHERE TT.ranking <= 3 ORDER BY tid, ranking
- 第一个子查询(内层),求出每个科目、每个用户的最小/最大分数,用于后期排名
- 第二个子查询(中层),按题目要求算出排名
- 外层查询,显示前三名的信息