【面试题】
有三张表：“学生表”、“期末成绩表”、“违纪表”。
查询半年内没有违纪记录，并且期末考试总分为每个班级前10名的学生名单。
【解题思路】
可以把这个复杂业务问题，使用多维度拆解分析方法，拆解为以下3个子问题：
1）从“违纪表”中查询半年内有违纪记录的学生名单，这部分最终要过滤掉
2）每个班级每个学生的期末考试总分
3）每个班级期末考试总分排名前10名的学生名单
1. 没有违纪记录的学生
查询半年内有违纪记录的学生名单，涉及到对时间的筛选。
问题中没有对“半年内”进行定义，这里我们可以自己定义下业务含义为“当前日期前半年(182天)之后”。
1）curdate()函数：获取当前日期；
2）date_sub(指定日期,interval … day)：获取指定日期…天之前的日期。
“当前日期前182天”，也就是：
date_sub(curdate(),interval 182 day)从“违纪表”中查询半年内有违纪记录的学生名单：
select distinct 学生idfrom 违纪表where 违纪时间 >= date_sub(curdate(),interval 182 day);
查询结果：
把这条查询结果记为表t1。
2.每个班级每个学生的期末考试总分
这里涉及到“期末成绩表”和“学生表”两张表，要用到多表查询。
把“期末成绩表”当作左表，使用左联结，给左表中补充班级id信息
select a1.*,a2.班级idfrom 期末成绩表 as a1left join学生表 as a2on a1.学生id = a2.学生id;
把上面的查询结果记录为表a3，然后，计算每个班级每个学生的期末考试总分。涉及到“每个”，要想到《猴子 从零学会SQL》里讲过的用“分组汇总”，按班级、学生分组（group by），汇总（分数求和sum）
select 班级id,学生id,sum(分数) as 总分from a3group by 班级id,学生id;
查询结果：
3.每个班级期末考试总分排名前 10 名的学生名单
涉及到排名问题，要想到《猴子从零学会SQL》里讲过的用窗口函数。把上一步查询结果记录为表a4，用窗口函数row_number()
select *,row_number() over(partition by 班级idorder by 总分 desc) as 班级排名from a4;
查询结果：
把这条查询结果记为表a5。
4. 条件筛选
题目中设定了两个查询条件：
条件一：班级排名 <= 10；
条件二：要求“半年内没有违纪记录的学生”-->通过排除“半年内有违纪记录的学生”进行过滤。
select 学生id,总分,班级排名,班级idfrom a5where 班级排名 <= 10and 学生id not in t1;
将子查询代入：
select 学生id,总分,班级排名,班级idfrom (select *,row_number() over(partition by 班级idorder by 总分 desc) as 班级排名from (select 班级id,学生id,sum(分数) as 总分from (select a1.*,a2.班级idfrom 期末成绩表 as a1left join学生表 as a2on a1.学生id = a2.学生id) as a3group by 班级id,学生id) as a4) as a5where 班级排名 <= 10and 学生id not in (select distinct 学生idfrom 违纪表where 违纪时间 >= date_sub(curdate(),interval 182 day));
【本题考点】
1）考查对日期函数的了解；
2）考查对分组函数group by的灵活使用；
3）考查对多表联结的了解，特别是灵活使用where条件进行数据过滤。
⬇️ 点击「阅读原文」
免费报名 数据分析训练营
原文作者：猴子数据分析；如有侵权，请联系管理员删除。
