前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >你违规了吗?

你违规了吗?

作者头像
猴子数据分析
发布2022-07-13 17:20:08
3510
发布2022-07-13 17:20:08
举报
文章被收录于专栏:猴子数据分析

【面试题】

有三张表:“学生表”、“期末成绩表”、“违纪表”。

查询半年内没有违纪记录,并且期末考试总分为每个班级前10名的学生名单。

【解题思路】

可以把这个复杂业务问题,使用多维度拆解分析方法,拆解为以下3个子问题:

1)从“违纪表”中查询半年内有违纪记录的学生名单,这部分最终要过滤掉

2)每个班级每个学生的期末考试总分

3)每个班级期末考试总分排名前10名的学生名单

1. 没有违纪记录的学生

查询半年内有违纪记录的学生名单,涉及到对时间的筛选。

问题中没有对“半年内”进行定义,这里我们可以自己定义下业务含义为“当前日期前半年(182天)之后”。

1)curdate()函数:获取当前日期;

2)date_sub(指定日期,interval … day):获取指定日期…天之前的日期。

“当前日期前182天”,也就是:

代码语言:javascript
复制
date_sub(curdate(),interval 182 day)

从“违纪表”中查询半年内有违纪记录的学生名单:

代码语言:javascript
复制
select distinct 学生id
from 违纪表
where 违纪时间 >= date_sub(curdate(),interval 182 day);

查询结果:

把这条查询结果记为表t1。

2.每个班级每个学生的期末考试总分

这里涉及到“期末成绩表”和“学生表”两张表,要用到多表查询。

把“期末成绩表”当作左表,使用左联结,给左表中补充班级id信息

代码语言:javascript
复制
select a1.*,
       a2.班级id
from 期末成绩表 as a1
left join
学生表 as a2 
on a1.学生id = a2.学生id;

把上面的查询结果记录为表a3,然后,计算每个班级每个学生的期末考试总分。涉及到“每个”,要想到《猴子 从零学会SQL》里讲过的用“分组汇总”,按班级、学生分组(group by),汇总(分数求和sum)

代码语言:javascript
复制
select 班级id,学生id,
sum(分数) as 总分
from a3
group by 班级id,学生id;

查询结果:

3.每个班级期末考试总分排名前 10 名的学生名单

涉及到排名问题,要想到《猴子从零学会SQL》里讲过的用窗口函数。把上一步查询结果记录为表a4,用窗口函数row_number()

代码语言:javascript
复制
select *,
       row_number() over(partition by 班级id 
                         order by 总分 desc) as 班级排名
from a4;

查询结果:

把这条查询结果记为表a5。

4. 条件筛选

题目中设定了两个查询条件:

条件一:班级排名 <= 10;

条件二:要求“半年内没有违纪记录的学生”-->通过排除“半年内有违纪记录的学生”进行过滤。

代码语言:javascript
复制
select 学生id,总分,班级排名,班级id
from a5
where 班级排名 <= 10
and 学生id not in t1;

将子查询代入:

代码语言:javascript
复制
select 学生id,总分,班级排名,班级id
from (
select *,
       row_number() over(partition by 班级id 
                         order by 总分 desc) as 班级排名
from (
select 班级id,学生id,
sum(分数) as 总分
from (
select a1.*,
       a2.班级id
from 期末成绩表 as a1
left join
学生表 as a2 
on a1.学生id = a2.学生id
) as a3
group by 班级id,学生id
) as a4
) as a5
where 班级排名 <= 10
and 学生id not in (
select distinct 学生id
from 违纪表
where 违纪时间 >= date_sub(curdate(),interval 182 day)
);

【本题考点】

1)考查对日期函数的了解;

2)考查对分组函数group by的灵活使用;

3)考查对多表联结的了解,特别是灵活使用where条件进行数据过滤。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2022-06-09,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 猴子数据分析 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档