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

还是用嵌套子查询比较方便理解

1. 查询每个标签下每个用户的最大分数和最小分数
2. 使用窗口函数生成ranking字段
3. 筛选出ranking>=3的行

select
    tag as tid,
    uid,
    ranking
from
    (
        select
            *,
            row_number() over (
                partition by
                    tag
                order by
                    max_score desc,
                    min_score desc,
                    uid desc
            ) as ranking
        from
            (
                select
                    tag,
                    uid,
                    max(score) as max_score,
                    min(score) as min_score
                from
                    examination_info
                    inner join exam_record using (exam_id)
                group by
                    tag,
                    uid
            ) as tb1
    ) as tb2
where
    ranking <= 3