滴滴 2020 年面试题：如何找出最小的 N 个数?

【题目】

“学生表” 里记录了学生的学号、入学时间等信息。“成绩表” 里是学生选课成绩的信息。两个表中的学号一一对应。（滴滴 2020 年面试题）

现在需要：

1. 筛选出 2017 年入学的 “计算机” 专业年龄最小的 3 位同学名单（姓名、年龄）

2. 统计每个班同学各科成绩平均分大于 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 临时表；

下图是 case 和 sum 结合起来统计人数的 sql 过程：

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 行数据

4. 遇到有筛选条件的统计数量问题时，使用 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 城市;

推荐：如何从零学会 sql？