图解SQL面试题 (学习笔记) 003多表查询----2

这里写目录标题

  • 一级目录
    • 二级目录
      • 三级目录
  • 如何比较日期数据
  • 如何交换数据
  • 滴滴面试题:如何找出最小N个数

一级目录

二级目录

三级目录

如何比较日期数据

【题目】
下面是某公司每天的营业额,表名为 “日销”。“日期” 这一列的数据类型是日期类型(date)。
图解SQL面试题 (学习笔记) 003多表查询----2_第1张图片
请找出所有比前一天(昨天)营业额更高的数据。(前一天的意思,如果 “当天” 是 1 月 2 号,“昨天”(前一天)就是 1 号)
例如需要返回一下结果:
图解SQL面试题 (学习笔记) 003多表查询----2_第2张图片
【解题思路】
1.交叉联结
首先我们来复习一下之前课程《从零学会 sql》里讲过的交叉联结(corss join)的概念。
使用交叉联结会将两个表中所有的数据两两组合。如下图,是对表 “text” 自身进行交叉联结的结果:

图解SQL面试题 (学习笔记) 003多表查询----2_第3张图片
直接使用交叉联结的业务需求比较少见,往往需要结合具体条件,对数据进行有目的的提取,本题需要结合的条件就是 “前一天”。
2.本题的日销表交叉联结的结果(部分)如下。这个交叉联结的结果表,可以看作左边三列是表 a,右边三列是表 b。

图解SQL面试题 (学习笔记) 003多表查询----2_第4张图片
红色框中的每一行数据,左边是 “当天” 数据,右边是 “前一天” 的数据。比如第一个红色框中左边是 “当天” 数据(2 号),右边是 “前一天” 的数据(1 号)。
题目要求,销售额条件是:“当天” > “昨天”(前一天)。所以,对于上面的表,我们只需要找到表 a 中销售额(当天)大于 b 中销售额(昨天)的数据。

3.另一个需要着重去考虑的,就是如何找到 “昨天”(前一天),这里为大家介绍两个时间计算的函数:
datediff(日期 1, 日期 2):
得到的结果是日期 1 与日期 2 相差的天数。
如果日期 1 比日期 2 大,结果为正;如果日期 1 比日期 2 小,结果为负。

例如:日期 1(2019-01-02),日期 2(2019-01-01),两个日期在函数里互换位置,就是下面的结果
图解SQL面试题 (学习笔记) 003多表查询----2_第5张图片

另一个关于时间计算的函数是:
timestampdiff(时间类型, 日期 1, 日期 2)
这个函数和上面 diffdate 的正、负号规则刚好相反。
日期 1 大于日期 2,结果为负,日期 1 小于日期 2,结果为正。
在 “时间类型” 的参数位置,通过添加 “day”, “hour”, “second” 等关键词,来规定计算天数差、小时数差、还是分钟数差。示例如下图:
图解SQL面试题 (学习笔记) 003多表查询----2_第6张图片
【解题步骤】

  1. 将日销表进行交叉联结
    图解SQL面试题 (学习笔记) 003多表查询----2_第7张图片 2. 选出上图红框中的 “a. 日期比 b. 日期大一天”
    可以使用 “diffdate(a. 日期, b. 日期) = 1” 或者“timestampdiff(day, a. 日期, b. 日期) = -1”,以此为基准,提取表中的数据,这里先用 diffdate 进行操作。
    代码部分:
select *
from 日销 as a cross join 日销 as b 
     on datediff(a.日期, b.日期) = 1;

得到结果:
图解SQL面试题 (学习笔记) 003多表查询----2_第8张图片
3. 找出 a 中销售额大于 b 中销售额的数据
where a. 销售额(万元) > b. 销售额(万元)
得到结果:
图解SQL面试题 (学习笔记) 003多表查询----2_第9张图片
4. 删掉多余数据
题目只需要找销售额大于前一天的 ID、日期、销售额,不需要上表那么多数据。所以只需要提取中上表的 ID、日期、销售额(万元)列。
结合一开始提到的两个处理时间的方法,最终答案及结果如下:

select a.ID, a.日期, a.销售额(万元)
from 日销 as a cross join 日销 as b 
     on datediff(a.日期, b.日期) = 1
where a.销售额(万元) > b.销售额(万元);

或者

select a.ID, a.日期, a.销售额(万元)
from 日销 as a cross join 日销 as b 
     on timestampdiff(day, a.日期, b.日期) = -1
where a.销售额(万元) > b.销售额(万元);

图解SQL面试题 (学习笔记) 003多表查询----2_第10张图片
【本题考点】
1)考察逻辑思维能力,可以使用课程《分析方法》中的逻辑树分析方法将复杂问题拆解成一个一个可以解决的子问题
2)考察多表联结
3)针对时间的处理语句是在业务中经常用到的,需要熟练掌握。
4) 尤其考察对不同 sql 数据格式处理的掌握程度

【举一反三】
下面是气温表,名为 weather,date 列的数据格式为 date,请找出比前一天温度更高的 ID 和日期
图解SQL面试题 (学习笔记) 003多表查询----2_第11张图片
参考答案:

select a.ID, a.date
from weather as a cross join weather as b 
     on datediff(a.date, b.date) = 1
where a.temp > b.temp;

或者:

select a.ID, a.date
from weather as a cross join weather as b 
     on timestampdiff(day, a.date, b.date) = -1
where a.temp > b.temp;

图解SQL面试题 (学习笔记) 003多表查询----2_第12张图片

如何交换数据

【题目】
小明是一所学校的老师,她有一张 “学生表”,平时用来存放座位号和学生的信息。其中,座位号是连续递增的。总的座位数是偶数。

图解SQL面试题 (学习笔记) 003多表查询----2_第13张图片
现在,小明想改变相邻俩学生的座位。你能不能帮她写一个 sql 查来输出想要的结果呢?
示例查询结果如下:
图解SQL面试题 (学习笔记) 003多表查询----2_第14张图片
【解题思路】
第一步:理清换座位的逻辑
查询目的是改变相邻学生的座位号。为了理清逻辑,在原表中插入一列叫做“奇偶数”,对应表示 “座位号” 的值是 “奇数” 还是“偶数”。
图解SQL面试题 (学习笔记) 003多表查询----2_第15张图片
然后比较原始表里的 “座位号” 和交换结果里的“座位号”(对比分析方法),可以发现下图的规律。
图解SQL面试题 (学习笔记) 003多表查询----2_第16张图片
1)如果原来座位号是奇数的学生,换座位后,这名学生的座位号变为 “座位号 + 1”。
2)如果原来座位号是偶数的学生,换座位后,这名学生的座位号变为 “座位号 - 1”。

第二步:如何判断座位号是奇数,还是偶数
sql 求余函数:mod(n,m) ,返回 n 除以 m 的余数
。比如 mod(8,2) 的结果是 0。
如果 n 除以 2 的余数是 0,说明 n 是偶数,否则是奇数。
转换为判断奇数,偶数的 sql 就是:

case
      when mod(座位号, 2) != 0  then  '奇数'
      when mod(座位号, 2)  = 0  then  '偶数'
end

把前面的逻辑写到 sql 里就是:
1)如果原来座位号是奇数的学生,换座位后,这名学生的座位号变为 “座位号 + 1”。
2)如果原来座位号是偶数的学生,换座位后,这名学生的座位号变为 “座位号 - 1”。

case
       when mod(座位号, 2) != 0  then 座位号 + 1
       when mod(座位号, 2)  = 0  then 座位号 - 1
end  as  '交换后座位号'

加入 select 字句,就是最好的结果:

select
      (case
             when mod(座位号, 2) != 0  then 座位号 + 1
             when mod(座位号, 2)  = 0  then 座位号 - 1
      end)  as  '交换后座位号',
      姓名
from 学生表;

【本题考点】
逻辑思维能力,如何使用对比分析方法发现规律
条件判断语句 case
如何判断奇数和偶数:mod 函数
【举一反三】
原始座次表 ‘seat’如下,现需要更换相邻位置学生的座次。
注:该座次表‘seat’共有 5 名学生,第 5 个 座位号是奇数的不变

查询逻辑和前面一样,但是座位总数是奇数,所以:如果最后一个座位号也是奇数,那么他没有可以交换的座位了,所以最后一个座位号的学生不变。
最后一个座位号,等于表里有多少行,可以用 count(*) 计算出来

# 最后一个座位号
select count(*) as counts 
from seat;

最后一个座位号作为条件判断使用时,可以使用子查询,以便调用。最终 sql 如下。

select
    (case
      # 当座位号是奇数并且不是不是最后一个座位号时
        when mod(id, 2) != 0 and counts!= id then id + 1
       # 当座位号是奇数并且是最后一个座位号时,座位号不变
        when mod(id, 2) != 0 and counts = id then id
       # 当座位号是偶数时
        else id - 1
    end) as id2,student
from seat,(select count(*) as counts from seat);

滴滴面试题:如何找出最小N个数

【题目】
“学生表” 里记录了学生的学号、入学时间等信息。“成绩表” 里是学生选课成绩的信息。两个表中的学号一一对应。(滴滴 2020 年面试题)
图解SQL面试题 (学习笔记) 003多表查询----2_第17张图片
图解SQL面试题 (学习笔记) 003多表查询----2_第18张图片
现在需要:
筛选出 2017 年入学的 “计算机” 专业年龄最小的 3 位同学名单(姓名、年龄)
统计每个班同学各科成绩平均分大于 80 分的人数和人数占比

解题思路】
问题 1:筛选出 2017 年入学的 “计算机” 专业年龄最小的 3 位同学名单(姓名、年龄)
一看是不是有点懵?
别着急,我们用逻辑树分析方法,把这个复杂问题拆解为一个一个可以解决的简单问题:
1)筛选条件:入学时间是 2017,专业是计算机
2)最小的 3 位同学名单(姓名、年龄)

  1. 先找出符合要求的同学
    筛选条件:入学时间是 2017,专业是计算机。year(日期)函数用来获取日期的年份
select 姓名,年龄
from 学生表
where 专业='计算机' and year(入学时间)=2017;

图解SQL面试题 (学习笔记) 003多表查询----2_第19张图片
2. 最小的 3 位同学名单(姓名、年龄)
先使用 order by 对年龄排序(从小到大,也就是升序 asc),然后使用 limit 输出前 3 行数据,就是年龄最小的 3 位。

select 姓名,年龄
from 学生表
where 专业='计算机' and year(入学时间)=2017
order by 年龄 asc
limit 3

图解SQL面试题 (学习笔记) 003多表查询----2_第20张图片
问题 2:统计每个班同学各科成绩平均分大于 80 分的人数和人数占比
一看是不是有点懵?
别着急,我们用逻辑树分析方法,把这个复杂问题拆解为一个一个可以解决的简单问题:
(1)每位同学的平均成绩
(2)平均分大于 80 分的人数
(3)平均分大于 80 分的人数占比
(4)输出结果是班级,平均分大于 80 分的人数,平均分大于 80 分的人数占比

  1. 每位同学的平均成绩
    涉及到 “每个” 的时候,就要想到《猴子 从零学会 sql》里的分组汇总了。按学号分组(group by),然后求平均成绩(avg 函数),把所得结果看做临时表。
select 学号,avg(分数) as 平均成绩
from 成绩表
group by 学号;

图解SQL面试题 (学习笔记) 003多表查询----2_第21张图片
2. 平均成绩 > 80 的人数
可以使用使用 sum 函数和 case 表达式来统计平均成绩大于 80 的人数

select sum(
case when 平均成绩>80 then 1 
                     else 0 
end) as 人数
from 临时表;
  1. 平均成绩大于 80 分的人数占比
    平均成绩 > 80 的人数占比 =(平均成绩 > 80 的人数)/ 总人数
    总人数是表行数:count(学号)。所以平均成绩 > 80 的人数占比就是:
select sum(
case when 平均成绩>80 then 1
                     else 0 
end)/count(学号) as 人数占比
from 临时表;
  1. 输出结果是班级、人数、人数占比
    班级在 “学生表” 中,这涉及到需要将 “学生表” 和“临时表”2 张表,需要用到多表联结。联结两表的是“学号”,如下:

图解SQL面试题 (学习笔记) 003多表查询----2_第22张图片
因为要保留 “学生表” 班级的全部数据,所以使用左联结。

select a.班级
from 学生表 as a 
left join 临时表 as b
on a.学号=b.学号
group by 班级;

题目要求是输出班级、人数、人数占比,所以在上面 sql 中加入输出的列名:

select a.班级,人数,人数占比
from 学生表 as a 
left join 临时表 as b
on a.学号=b.学号
group by 班级;

select 子句中的人数、人数占比在前面第 1 步、第 2 步中已经得到,套入这个 sql 语句中就是:
图解SQL面试题 (学习笔记) 003多表查询----2_第23张图片
最终 sql 如下:

select a.班级,
sum(
case when b.平均成绩>80 then 1
else 0 end) as 人数,
sum(
case when b.平均成绩>80 then 1
else 0 end)/count(a.学号) as 人数占比
from 学生表 as a left join(
select 学号,avg(分数) as 平均成绩
from 成绩表
group by 学号
) as b
on a.学号=b.学号
group by 班级

【本题考点】
1.使用逻辑树分析方法将复杂问题变成简单问题的能力
2.当遇到 “每个” 问题的时候,要想到用分组汇总
3.查询最小 n 个数据的问题:先排序(order by),然后使用 limit 取出前 n 行数据
遇到有筛选条件的统计数量问题时,使用 case 表达式筛选出符合条件的行为 1,否则为 0。然后用汇总函数(sum)对 case 表达式输出列求和。

图解SQL面试题 (学习笔记) 003多表查询----2_第24张图片
图解SQL面试题 (学习笔记) 003多表查询----2_第25张图片
有筛选条件的统计数量问题的万能模板

select sum(
case when <判断表达式> then 1
       else 0
end
) as 数量
from 信息表;

【举一反三】

  1. 查询最小 / 最大的 N 个数据的问题
    某网站有购买记录表,找出消费最大的 2 名顾客,输出顾客 ID 和消费金额
    图解SQL面试题 (学习笔记) 003多表查询----2_第26张图片
select 顾客ID,消费金额
from 购买记录表
order by 消费金额 desc
limit 2;
  1. 分组汇总问题
    某网站有顾客表和消费表,请统计每个城市的顾客平均消费在 1000 元以上的人数,输出城市,人数
    图解SQL面试题 (学习笔记) 003多表查询----2_第27张图片
select a.城市,
sum(
case when b.平均消费>1000 then 1
else 0 end) as 人数
from 顾客表 as a left join(
select ID,avg(消费金额) as 平均消费
from 消费表
group by ID
) as b
on a.ID=b.ID
group by 城市;

你可能感兴趣的