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

首先是窗口函数的知识：

ROW_NUMBER()函数可以理解为排序号，不考虑并列；

RANK()函数也为排号，考虑并列，并列之后的按照实际序号来；

dense_rank()同样是排号，考虑并列，并列之后按下一个名次来。

排序方面就是根据最大分数，然后最小分数，然后uid排序，右连接exam_record表group by一下来去重

select
    p1.tag,
    p1.uid
    ,p1.ranking
from (
        SELECT
             distinct uid as uid,
             tag,
             row_number() over(partition by tag
             ORDER BY max(score) desc,min(score) desc ,uid desc )  AS ranking
        FROM
            examination_info e
            RIGHT JOIN
            exam_record r
        ON  e.exam_id = r.exam_id
        group by
            uid,tag
         ) AS p1
where p1.ranking < 4;