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

解法一：先做一个开窗的（只要知道开窗的伙伴应该都会做）

解法二：后面再做个不开窗的,用mysql老版本的朋友应该都熟悉变量这种方式，

select
*
from 
(
    select
        c.tag,c.uid,row_number() over(partition by c.tag order by c.max desc,c.min desc,c.uid desc) as rk
    from 
    (
        select
        a.uid,b.tag,max(score) as max,min(score) as min
        from exam_record a join examination_info b on a.exam_id = b.exam_id
        group by a.uid,b.tag
    ) c

) d where d.rk <=3

select
e.tag as tid,e.uid,format(e.rk,0) as ranking
from 
(
    select
        c.tag,c.uid,(case when @preCol = c.tag then @rk:=@rk+1 else @rk:= 1 end ) as rk,
        @preCol:=c.tag
    from 
    (
        select
        a.uid,b.tag,max(score) as max,min(score) as min
        from exam_record a join examination_info b on a.exam_id = b.exam_id
        group by a.uid,b.tag
    ) c,(
        select 
        @preCol := NULL,
        @rk := 0
    ) d
    order by c.tag,c.max desc,c.min desc,c.uid desc
) e where e.rk <= 3