本篇节选自书籍《对比Excel，轻松学习SQL数据分析》一书，主要讲解数据分析面试中常见的30道SQL面试题。
1 查询每个班学生数
现在有一张全校学生信息表stu_table，这张表存储了每位学生的id、name(姓名)、class(班级)、sex(性别)以及一些其他信息，现在我们想知道每个班有多少学生，该怎么实现呢？
stu_table表如下所示：
| id | name | class | sex |
|---|---|---|---|
| 4 | 张文华 | 二班 | 男 |
| 3 | 李思雨 | 一班 | 女 |
| 1 | 王小凤 | 一班 | 女 |
| 7 | 李智瑞 | 三班 | 男 |
| 6 | 徐文杰 | 二班 | 男 |
| 8 | 徐雨秋 | 三班 | 男 |
| 5 | 张青云 | 二班 | 女 |
| 9 | 孙皓然 | 三班 | 男 |
| 10 | 李春山 | 三班 | 男 |
| 2 | 刘诗迪 | 一班 | 女 |
自己先想一下代码怎么写，然后再参考我的代码。
-
select
-
class
-
,count(id) as stu_num
-
from
-
demo.stu_table
-
group by
-
class
解题思路：
我们是要获取每个班的学生数，首先需要对班级进行分组，使用的是group by；然后再对每个组内的学生进行计数聚合运算，使用的count。最后运行结果如下：
| class | stu_num |
|---|---|
| 二班 | 3 |
| 一班 | 3 |
| 三班 | 4 |
2 查询每个班男女学生数
还是前面的全校学生信息表stu_table，现在我们想知道每个班男生女生分别有多少个？
自己先想一下代码怎么写，然后再参考我的代码。
-
select
-
class
-
,sex
-
,count(id) as stu_num
-
from
-
demo.stu_table
-
group by
-
class
-
,sex
解题思路：
与第一题不同的是，不仅需要每个班级的信息，还需要每个班级里面男女生分别的信息，主要考察的就是按照多列分组聚合的知识，直接在group by后面指明要分组的多列即可，且列与列之间用逗号分隔开。最后运行结果如下：
| class | sex | stu_num |
|---|---|---|
| 二班 | 男 | 2 |
| 一班 | 女 | 3 |
| 三班 | 男 | 4 |
| 二班 | 女 | 1 |
3 姓张的同学有多少个
还是前面的全校学生信息表stu_table，现在我们想知道这张表中姓张的同学有多少个？
自己先想一下代码怎么写，然后再参考我的代码。
-
select
-
count(id) as stu_num
-
from
-
demo.stu_table
-
where name like
"张%"
解题思路：
我们是要获取姓张的同学有多少个，首先需要思考的是怎么去判断同学是否姓张，假设我们表里面存储的姓名都是先姓后名的形式，那就可以用到字符串匹配函数like；知道怎么判断同学是否姓张，接下来就是把这些同学筛选出来，使用的是where条件；最后针对筛选出来的同学进行计数，使用的是count。最后运行结果如下：
| stu_num |
|---|
| 2 |
4 筛选出id第3-5的同学
还是前面的全校学生信息表stu_table，现在我们要获取id从小到大排序以后第3-5位的同学的信息。
自己先想一下代码怎么写，然后再参考我的代码。
-
select
-
*
-
from
-
demo.stu_table
-
order by id asc
-
limit
2,
3
解题思路：
我们要获取id从小到大排序以后第3-5位的同学，因为不确定id是否连续，所以我们没法直接用where条件来筛选id。我们先对id进行升序排列，然后再利用limit进行筛选。最后运行结果如下：
| id | name | class | sex |
|---|---|---|---|
| 3 | 李思雨 | 一班 | 女 |
| 4 | 张文华 | 二班 | 男 |
| 5 | 张青云 | 二班 | 女 |
5 筛选出挂科的同学
现在有一张学生成绩表score_table，这张表存储了每位学生的id、name(姓名)、class(班级)、score(成绩)，现在我们想要把挂科(成绩小于60)的同学信息筛选出来。
score_table表如下所示：
| id | name | class | score |
|---|---|---|---|
| 1 | 王小凤 | 一班 | 88 |
| 2 | 刘诗迪 | 一班 | 70 |
| 3 | 李思雨 | 一班 | 92 |
| 4 | 张文华 | 二班 | 55 |
| 5 | 张青云 | 二班 | 77 |
| 6 | 徐文杰 | 二班 | 77 |
| 7 | 李智瑞 | 三班 | 56 |
| 8 | 徐雨秋 | 三班 | 91 |
| 9 | 孙皓然 | 三班 | 93 |
| 10 | 李春山 | 三班 | 57 |
自己先想一下代码怎么写，然后再参考我的代码。
-
select
-
*
-
from
-
demo.score_table
-
where score <
60
解题思路：
我们要获取挂科同学的信息，只需要加一个where条件用来限定挂科这个条件即可。最后运行结果如下：
| id | name | class | score |
|---|---|---|---|
| 4 | 张文华 | 二班 | 55 |
| 7 | 李智瑞 | 三班 | 56 |
| 10 | 李春山 | 三班 | 57 |
6 筛选姓张的且挂科的同学
我们现在需要根据学生成绩表score_table查找出姓张的且挂科的同学的信息。
自己先想一下代码怎么写，然后再参考我的代码。
-
select
-
*
-
from
-
demo.score_table
-
where score <
60
-
and name like
"张%"
解题思路：
这里面主要是用到了多条件筛选，多个条件之间用and进行关联即可。最后运行结果如下：
| id | name | class | score |
|---|---|---|---|
| 4 | 张文华 | 二班 | 55 |
7 查询销冠获得次数
我们有一张表month_table记录了每月的销售冠军信息，这张表存储了每月销冠的id、name(姓名)、month_num(月份)，现在需要获取销冠次数超过2次的人以及其对应的做销冠次数。
month_table表如下所示：
| id | name | month_num |
|---|---|---|
| E002 | 王小凤 | 1 |
| E001 | 张文华 | 2 |
| E003 | 孙皓然 | 3 |
| E001 | 张文华 | 4 |
| E002 | 王小凤 | 5 |
| E001 | 张文华 | 6 |
| E004 | 李智瑞 | 7 |
| E002 | 王小凤 | 8 |
| E003 | 孙皓然 | 9 |
自己先想一下代码怎么写，然后再参考我的代码。
-
select
-
id
-
,name
-
,count(month_num) num
-
from
-
demo.month_table
-
group by
-
id
-
,name
-
having
-
count(month_num) >
2
解题思路：
我们要获取销冠次数超过2次的人以及其对应的做销冠次数，首先需要获取每个人做销冠的次数，对id进行group by，然后在组内对month_num进行计数即可；然后再对分组聚合后的结果利用having进行条件筛选。最后结果如下：
| id | name | num |
|---|---|---|
| E002 | 王小凤 | 3 |
| E001 | 张文华 | 3 |
8 获取每个部门一整年业绩提升幅度
现在有一个月份销售额记录表sale_table，这个表记录了每年每月的销售额，现在我们想看下今年(2019年)，月销售额最高涨幅是多少？
sale_table表如下所示：
| year_num | month_num | sales |
|---|---|---|
| 2019 | 1 | 2854 |
| 2019 | 2 | 4772 |
| 2019 | 3 | 3542 |
| 2019 | 4 | 1336 |
| 2019 | 5 | 3544 |
| 2018 | 1 | 2293 |
| 2018 | 2 | 2559 |
| 2018 | 3 | 2597 |
| 2018 | 4 | 2363 |
自己先想一下代码怎么写，然后再参考我的代码。
-
select
-
max(sales) as max_sales
-
,min(sales) as min_sales
-
,max(sales)-min(sales) as cha
-
,(max(sales)-min(sales))/min(sales) as growth
-
from
-
demo.sale_table
-
where
-
year_num =
2019
解题思路：
我们要获取今年的最大月涨幅，首先需要通过where条件把今年的每月数据销售额筛选出来；然后再在今年的月销售额里面寻找最大和最小的销售额，对两者进行做差，就是我们想要的结果。最后运行结果如下：
| max_sales | min_sales | cha | growth |
|---|---|---|---|
| 4772 | 1336 | 3436 | 2.5719 |
9 查找每科成绩大于70的学生
我们有一张学生科目成绩表score_info_table，这张表记录了每一位同学每一科目的成绩，每一位同学的每科成绩是一行，现在我们想要通过这张表获取到每科成绩都大于70分的学生。
score_info_table表如下所示：
| id | name | subject | score |
|---|---|---|---|
| 1 | 王小凤 | 语文 | 88 |
| 2 | 张文华 | 数学 | 70 |
| 3 | 徐雨秋 | 英语 | 92 |
| 1 | 王小凤 | 语文 | 55 |
| 2 | 张文华 | 数学 | 77 |
| 3 | 徐雨秋 | 英语 | 77 |
| 1 | 王小凤 | 语文 | 72 |
| 2 | 张文华 | 数学 | 91 |
| 3 | 徐雨秋 | 英语 | 93 |
自己先想一下代码怎么写，然后再参考我的代码。
-
select
-
id
-
,name
-
from
-
demo.score_info_table
-
group by
-
id
-
,name
-
having
-
min(score) >
70
解题思路：
我们是要获取每科成绩大于70的学生，只要能够保证最小成绩是大于70分的，就说明这位同学每科成绩都大于70分.所以第一步就是先获取每位同学的最小成绩，先对name进行group by分组，再在组内求最小值，然后将最小成绩大于70分的同学通过having筛选出来即可。最后运行结果如下：
| id | name |
|---|---|
| 3 | 徐雨秋 |
10 删除重复值
现在有一个学生信息表stu_info_table，这张表存储了每位学生id、name(姓名)、class(班级)、grade(年级)，现在我们想获取这个学校所有年级以及所有班级的信息,即哪些年级有哪些班级，该怎么获取？
stu_info_table表如下所示：
| id | name | class | grade |
|---|---|---|---|
| 1 | 王小凤 | 一班 | 一年级 |
| 2 | 刘诗迪 | 一班 | 二年级 |
| 3 | 李思雨 | 一班 | 一年级 |
| 4 | 张文华 | 二班 | 二年级 |
| 5 | 张青云 | 二班 | 一年级 |
| 6 | 徐文杰 | 二班 | 二年级 |
| 7 | 李智瑞 | 一班 | 一年级 |
| 8 | 徐雨秋 | 二班 | 二年级 |
| 9 | 孙皓然 | 一班 | 一年级 |
自己先想一下代码怎么写，然后再参考我的代码。
-
select
-
grade
-
,class
-
from
-
demo.stu_info_table
-
group by
-
grade
-
,class
-
order by
-
grade
解题思路：
stu_table表中id列是主键，即不重复的，但是class和grade是重复的，多个id会属于同一个class和grade。我们只要class和grade信息，所以是需要对这两列进行去重，去重我们除了用distinct以外，还可以用group by。最后运行结果如下：
| grade | class |
|---|---|
| 一年级 | 一班 |
| 一年级 | 二班 |
| 二年级 | 一班 |
| 二年级 | 二班 |
想进一步了解更多内容的同学，可以点击下方链接：
转载：https://blog.csdn.net/junhongzhang/article/details/111188977