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

Previous图解面试题：如何交换数据？Next图解面试题：行列互换问题，怎么办？

Last updated 4 years ago

Was this helpful?

滴滴 2020 年面试题：如何找出最小的 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. 每位同学的平均成绩

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 班级

【本题考点】

当遇到 “每个” 问题的时候，要想到用分组汇总
查询最小 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 城市;