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

每类试卷得分前3名

https://www.nowcoder.com/practice/255aa1863fe14aa88694c09ebbc1dbca

SELECT
tid,uid,ranking
FROM
(SELECT
tid,uid,
RANK() OVER(PARTITION BY tid ORDER BY rk1 ASC,rk2 ASC,uid DESC) AS ranking
FROM 
(SELECT 
tid,uid,
RANK() OVER(PARTITION BY tid ORDER BY MAX1 DESC) AS rk1,
RANK() OVER(PARTITION BY tid ORDER BY MIN1 DESC) AS rk2
FROM (SELECT 
tag AS tid,uid,
MAX(score) AS MAX1,
MIN(score) AS MIN1
FROM exam_record
LEFT JOIN examination_info USING(exam_id)
GROUP BY tid,uid) t1)t2
WHERE rk1 <= 3 AND rk2 <= 3) t3
WHERE ranking <= 3; 

全部评论

相关推荐

点赞 收藏 评论
分享
牛客网
牛客企业服务