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

SELECT tag, uid, ranking FROM(
    SELECT tag, uid,
    row_number() over(partition by tag order by MAX(score) DESC, MIN(score) DESC, uid DESC) AS ranking
    FROM examination_info,exam_record 
    WHERE examination_info.exam_id=exam_record.exam_id 
    GROUP BY tag,uid) a##按照这个分组后，窗口函数就能使用聚合函数的值
WHERE a.ranking<=3;

这里要mark的是，我转念一想可不可以这样，只做一次select：

SELECT tag, uid,
row_number() over(partition by tag order by MAX(score) DESC, MIN(score) DESC, uid DESC) AS ranking
FROM examination_info,exam_record 
WHERE examination_info.exam_id=exam_record.exam_id 
GROUP BY tag,uid
having ranking<=3;

结果是不行的，报错"You cannot use the alias 'ranking' of an expression containing a window function in this context.'"。问题出在不能用having ranking<=3。