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

一、知识点总结

把有用的知识写在前面，以方便自个儿复习观看😊

1）窗口函数：有三种排序方式

rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

2）聚合函数：通常查找最大值最小值的时候，首先会想到使用聚合函数。

a.group by的常见搭配：常和以下聚合函数搭配

avg（）-- 求平均值
count（）-- 计数
sum（）-- 求和
max（） -- 最大值
min（）-- 最小值

b.group by 的进阶用法，和with rollup一起使用。

3）左右连接

左连接：表1 left join 表2 on 表1.字段=表2.字段（以表1为准，表2进行匹配）

右连接：表1 right join 表2 on 表1.字段=表2.字段（以表2为准，表1进行匹配）

全连接：表1 union all 表2 （表1 和表2的列数必须一样多，union 去除重复项，union all 不剔除重复项）

内连接：表1 inner join 表2（取表1和表2相交部分）

外连接：表1 full outer join 表2 （取表1和表2不相交的部分）

ps：MYSQL 不支持外连接，可以用左右连接后再全连接代替

二、题目解读与解题步骤拆解

1、题目解读

求：找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。

题目中隐藏的坑

坑1：每类试卷得分的前3名，因此不包含一个用户独占一科前几名的情况，例如1003的SQL考试分别是89分和86分，只取分高的那条数据。本质就是对比某科目每个用户的最高分。
坑2：如果最高分一样，那就对比最低分:order by max(score) desc,min(score) desc,uid desc

2、解题步骤拆分

先处理表格找出每个科目各用户的最高分与最低分，进行排序，最后取前3。

排序常用row_number()匹配窗口函数。
需求字段：tag，uid，ranking

三、步骤代码

1）tag取值

SELECT tag FROM examination_info

2）uid取值

SELECT uid FROM exam_record

3）获取各个科目每个用户得分的最大值和最小值

 SELECT b.tag,a.uid,max(a.score),min(a.score)
 FROM exam_record a 
 LEFT JOIN examination_info b ON a.exam_id=b.exam_id
 GROUP BY b.tag,a.uid;

处理后表格如下

4)对上表进行排序

ROW_NUMBER()OVER(PARTITION BY tag ORDER BY max(a.score)DESC,min(a.score)DESC,a.uid DESC) ranking

SELECT b.tag,a.uid,max(a.score),min(a.score),/*这里写max(a.score)和min(a.score)是为了帮助理解窗口函数里的ORDER BY 后面的内容*/
ROW_NUMBER()OVER(PARTITION BY tag ORDER BY max(a.score)DESC,min(a.score)DESC,a.uid DESC) ranking
FROM exam_record a 
LEFT JOIN examination_info b ON a.exam_id=b.exam_id
GROUP BY b.tag,a.uid;

四、完整代码组装

SELECT tag,uid,ranking
FROM (
    SELECT b.tag,a.uid,
    ROW_NUMBER()OVER(PARTITION BY tag ORDER BY max(a.score) DESC,min(a.score) DESC,a.uid DESC) ranking
        /*先按照最高分，再按照最低分排，最后按照uid排序 */
    FROM exam_record a 
    LEFT JOIN examination_info b ON a.exam_id=b.exam_id
    GROUP BY b.tag,a.uid)t1
    
WHERE ranking<=3

最终结果