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

解题思路：

1、根据题目，需要对每个用户，每门考试下钻，并统计最值；(group by两个字段+min()+max())

2、排序：窗口函数排序，需要对科目进行分组，并对最高分、最低分和账号三个字段进行排序，用来区分并列情况;(rank() over(partition by order by)

3、筛选：筛选各自科目中的前三名即可;(where)

    tmp2.tag
    ,tmp2.uid
    ,tmp2.rk
from(#rank() over窗口函数进行排序,三个字段需要排序
    SELECT
        tmp1.*
        ,rank() over(partition by tmp1.tag order by tmp1.max_score desc,tmp1.min_score desc,tmp1.uid desc) as rk
    from(#需要按人按考试计算最高分和最低分
        select
            er.uid
            ,ei.tag
            ,min(er.score) as min_score
            ,max(er.score) as max_score
        from examination_info ei join exam_record er using(exam_id)
        group by er.uid
                ,ei.tag
        )tmp1
    )tmp2
where tmp2.rk<4