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

select tag as tid,uid ,ranking 
 from (
 SELECT b.tag,a.uid,max(a.score),min(a.score),
row_number() over (partition by tag order by max(a.score) desc ,min(a.score) desc ,a.uid desc) as ranking 
 /*先按照最高分，再按照最低分排，最后按照uid排序 */
 FROM exam_record a 
 LEFT JOIN examination_info b ON a.exam_id=b.exam_id
 GROUP BY b.tag,a.uid
 ) as newtable
 where ranking <=3;
 
 ##注意的一点就是，语句报错SQL_ERROR_INFO: 'Every derived table must have its own alias'
 ##原因是因为对于select生成的表 必须命个别名，as newtable，结果顺利运行