CTE比子查询逻辑更清晰 | #每类试卷得分前3名#

因为窗口函数和聚合函数不能在同一个子查询里叠加使用，并且窗口函数的排序结果不能在同一个子查询底部进行where过滤，所以递进的CTE表达式是最方便的：

WITH info AS (
	SELECT
    	tid,
	    uid,
    	MAX(score) as maxs,
    	MIN(score) as mins
	FROM (
		SELECT
		    DISTINCT tag as tid,
		    r.id,
		    r.uid,
		    r.score
		FROM
		    examination_info
		JOIN
		    exam_record r USING(exam_id)
	) temp
	GROUP BY
	    tid, uid	
	ORDER BY
   		tid, maxs DESC, mins DESC, uid DESC
),

rn AS(
	SELECT
		 tid,
		uid,
		ROW_NUMBER() OVER (PARTITION BY tid ORDER BY tid, maxs DESC, mins DESC, uid DESC) AS ranking
	FROM 
		info
)

SELECT
	*
FROM
	rn
WHERE 
	ranking <= 3
    

row_number里的order by的逻辑直接复制第一段cte里的就行