【面试题】
有一张“学生成绩表”，包含4个字段：班级id、学生id、课程id、成绩。
问题1：求出每个学生成绩最高的三条记录
问题2：找出每门课程都高于班级课程平均分的学生
【解题步骤】
1. topN问题
问题1是常见的排名问题（topN问题），要想到用《猴子从零学会SQL》里讲过的窗口函数来解决这类业务问题。
用窗口函数获取顺序有三种：rank()，dense_rank()和row_number()。
同样是按“值”从小到大排序，三者的区别如下：
根据问题的描述，我们应该使用dense_rank窗户函数
select *from (select *,dense_rank() over (partition by 班级id,学生idorder by 成绩 desc) as 顺序from 学生成绩表) t1where 顺序 <= 3;
查询结果：
2. 汇总分析
问题2要求找出每门课程都高于班级课程平均分的学生，可以拆解成以下几个问题：
1）求出每个班级，每门课程的平均分
2）将学生每门课程的成绩与所在班级的对应课程平均分相减，结果大于0就说明该学生的这门成绩高于课程平均分
3）“找出每门课程都高于班级课程平均分的学生”说明对于学生来说，最小的“相减结果”都是大于0的
首先用汇总分析求出每个班级，每门课程的平均分。
select 班级id,课程id,avg(成绩) as 课程平均分from 学生成绩表group by 班级id,课程id;
查询结果：
3. 多表联结
涉及到多表查询，需要用到多表联结。
这里目的是为了将“将学生每门课程的成绩与所在班级的对应课程平均分相减”。
所以，是将原始的“学生成绩表”与“班级的课程平均分”进行联结。
为了保持左表“学生成绩表”的所有数据，将所有学生的成绩都与“课程平均分”x相减，所以选择“左联结（left join）”。
select t1.班级id,t1.学生id,t1.课程id,t1.成绩,t1.成绩 - t2.课程平均分 as 相减结果from 学生成绩表 t1left join (select 班级id,课程id,avg(成绩) as 课程平均分from 学生成绩表group by 班级id,课程id) t2 on t1.班级id = t2.班级id and t1.课程id = t2.课程id;
最后使用分组汇总，并结合having条件筛选出“相减结果的最小值大于0”的学生。
select 班级id,学生idfrom (select t1.班级id,t1.学生id,t1.课程id,t1.成绩,t1.成绩 - t2.课程平均分 as 相减结果from 学生成绩表 as t1left join (select 班级id,课程id,avg(成绩) as 课程平均分from 学生成绩表group by 班级id,课程id) as t2 on t1.班级id = t2.班级id and t1.课程id = t2.课程id) as tmpgroup by 班级id,学生idhaving min(相减结果) > 0;
【本题考点】
1.考查对分组汇总的了解，以及灵活使用来解决业务问题；
2.考查对多表联结的了解，以及灵活使用来解决业务问题；
3）考查对窗口函数的了解，窗户函数解决的经典问题就那么几种，记下来，就可以解决99%的业务问题。
⬇️点击「阅读原文」
免费报名 数据分析训练营
原文作者：猴子数据分析；如有侵权，请联系管理员删除。
