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

# 查询各用户在每类试卷中的最大分数和最小分数
select uid, tag, max(score) max_score, min(score) min_score
from exam_record er
join examination_info ei
on er.exam_id=ei.exam_id
group by uid, tag

# 查询每类试卷得分的排名
select tag, uid,
row_number()over(partition by tag order by max_score desc, min_score desc, uid desc) ranking
from (
    select uid, tag, max(score) max_score, min(score) min_score
    from exam_record er
    join examination_info ei
    on er.exam_id=ei.exam_id
    group by uid, tag
) as k1
# -------------------------完整代码----------------------------
# 查询每类试卷得分的前三名
select tag, uid, ranking
from (
   select tag, uid,
    row_number()over(partition by tag order by max_score desc, min_score desc, uid desc) ranking
    from (
        select uid, tag, max(score) max_score, min(score) min_score
        from exam_record er
        join examination_info ei
        on er.exam_id=ei.exam_id
        group by uid, tag
    ) as k1 
) as k2
where ranking <=3
order by tag, ranking;

由于试卷得分排名的排序规则是：如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者

因此使用窗口函数来排序前，应该把所需要的字段都给查询出来，即最开始需要查询出每个用户关于每类试卷的最大分数及最小分数，有了这些排序所需字段值，才能实现窗口函数的排序：

row_number()over(partition by tag order by max_score desc, min_score desc, uid desc)

其中row_number()函数生成的不重复的序列值