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

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;