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

一级目录

二级目录

三级目录

如何比较日期数据

【题目】
下面是某公司每天的营业额，表名为 “日销”。“日期” 这一列的数据类型是日期类型（date）。

请找出所有比前一天（昨天）营业额更高的数据。（前一天的意思，如果 “当天” 是 1 月 2 号，“昨天”（前一天）就是 1 号）
例如需要返回一下结果：

【解题思路】
1.交叉联结
首先我们来复习一下之前课程《从零学会 sql》里讲过的交叉联结（corss join）的概念。
使用交叉联结会将两个表中所有的数据两两组合。如下图，是对表 “text” 自身进行交叉联结的结果：

直接使用交叉联结的业务需求比较少见，往往需要结合具体条件，对数据进行有目的的提取，本题需要结合的条件就是 “前一天”。
2.本题的日销表交叉联结的结果（部分）如下。这个交叉联结的结果表，可以看作左边三列是表 a，右边三列是表 b。

红色框中的每一行数据，左边是 “当天” 数据，右边是 “前一天” 的数据。比如第一个红色框中左边是 “当天” 数据（2 号），右边是 “前一天” 的数据（1 号）。
题目要求，销售额条件是：“当天” > “昨天”（前一天）。所以，对于上面的表，我们只需要找到表 a 中销售额（当天）大于 b 中销售额（昨天）的数据。

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

例如：日期 1（2019-01-02），日期 2（2019-01-01），两个日期在函数里互换位置，就是下面的结果

另一个关于时间计算的函数是：
timestampdiff(时间类型, 日期 1, 日期 2)
这个函数和上面 diffdate 的正、负号规则刚好相反。
日期 1 大于日期 2，结果为负，日期 1 小于日期 2，结果为正。
在 “时间类型” 的参数位置，通过添加 “day”, “hour”, “second” 等关键词，来规定计算天数差、小时数差、还是分钟数差。示例如下图：

【解题步骤】

1. 将日销表进行交叉联结
   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;

得到结果：

3. 找出 a 中销售额大于 b 中销售额的数据
where a. 销售额（万元） > b. 销售额（万元）
得到结果：

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.销售额（万元）;

【本题考点】
1）考察逻辑思维能力，可以使用课程《分析方法》中的逻辑树分析方法将复杂问题拆解成一个一个可以解决的子问题
2）考察多表联结
3）针对时间的处理语句是在业务中经常用到的，需要熟练掌握。
4） 尤其考察对不同 sql 数据格式处理的掌握程度

【举一反三】
下面是气温表，名为 weather，date 列的数据格式为 date，请找出比前一天温度更高的 ID 和日期

参考答案：

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 查来输出想要的结果呢？
示例查询结果如下：

【解题思路】
第一步：理清换座位的逻辑
查询目的是改变相邻学生的座位号。为了理清逻辑，在原表中插入一列叫做“奇偶数”，对应表示 “座位号” 的值是 “奇数” 还是“偶数”。

然后比较原始表里的 “座位号” 和交换结果里的“座位号”（对比分析方法），可以发现下图的规律。

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 年面试题）


现在需要：
筛选出 2017 年入学的 “计算机” 专业年龄最小的 3 位同学名单（姓名、年龄）
统计每个班同学各科成绩平均分大于 80 分的人数和人数占比

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

1. 先找出符合要求的同学
   筛选条件：入学时间是 2017，专业是计算机。year(日期)函数用来获取日期的年份

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

2. 最小的 3 位同学名单（姓名、年龄）
先使用 order by 对年龄排序（从小到大，也就是升序 asc），然后使用 limit 输出前 3 行数据，就是年龄最小的 3 位。

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

问题 2：统计每个班同学各科成绩平均分大于 80 分的人数和人数占比
一看是不是有点懵？
别着急，我们用逻辑树分析方法，把这个复杂问题拆解为一个一个可以解决的简单问题：
（1）每位同学的平均成绩
（2）平均分大于 80 分的人数
（3）平均分大于 80 分的人数占比
（4）输出结果是班级，平均分大于 80 分的人数，平均分大于 80 分的人数占比

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

select 学号,avg(分数) as 平均成绩
from 成绩表
group by 学号;

2. 平均成绩 > 80 的人数
可以使用使用 sum 函数和 case 表达式来统计平均成绩大于 80 的人数

select sum(
case when 平均成绩>80 then 1 
                     else 0 
end) as 人数
from 临时表；

3. 平均成绩大于 80 分的人数占比
   平均成绩 > 80 的人数占比 =（平均成绩 > 80 的人数）/ 总人数
   总人数是表行数：count(学号)。所以平均成绩 > 80 的人数占比就是：

select sum(
case when 平均成绩>80 then 1
                     else 0 
end)/count(学号) as 人数占比
from 临时表；

4. 输出结果是班级、人数、人数占比
   班级在 “学生表” 中，这涉及到需要将 “学生表” 和“临时表”2 张表，需要用到多表联结。联结两表的是“学号”，如下：

因为要保留 “学生表” 班级的全部数据，所以使用左联结。

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 如下：

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 表达式输出列求和。

有筛选条件的统计数量问题的万能模板

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

【举一反三】

1. 查询最小 / 最大的 N 个数据的问题
   某网站有购买记录表，找出消费最大的 2 名顾客，输出顾客 ID 和消费金额
   

select 顾客ID,消费金额
from 购买记录表
order by 消费金额 desc
limit 2;

2. 分组汇总问题
   某网站有顾客表和消费表，请统计每个城市的顾客平均消费在 1000 元以上的人数，输出城市，人数
   

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 城市;