一道常见 SQL 面试题的一些思考-优快云博客

本文链接：https://blog.youkuaiyun.com/xmg_zs/article/details/130936390

一、前言

事情是这样的，之前在两次小公司的面试过程中都遇到了类似的 SQL 面试题，面试完后在总结这道面试题的过程中遇到了一些坑，同时也回顾起了很多遗忘的知识点，在这里分享一下自己的一个思考过程，希望大家看完能有所收获，如有错漏之处，请指正！

二、题目与解析

数据表 student 数据如下：

id	name	subject	score
1	张三	英语	81
2	张三	数学	75
3	张三	语文	67
4	李四	数学	90
5	李四	英语	81
6	李四	语文	100
7	王五	数学	100
8	王五	英语	90
9	王五	语文	81
10	刘六	数学	81
11	陈七	数学	99

建表语句：

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `subject` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `score` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `student` VALUES (1, '英语', '张三', 81);
INSERT INTO `student` VALUES (2, '数学', '张三', 75);
INSERT INTO `student` VALUES (3, '语文', '张三', 67);
INSERT INTO `student` VALUES (4, '数学', '李四', 90);
INSERT INTO `student` VALUES (5, '英语', '李四', 81);
INSERT INTO `student` VALUES (6, '语文', '李四', 100);
INSERT INTO `student` VALUES (7, '数学', '王五', 100);
INSERT INTO `student` VALUES (8, '英语', '王五', 90);
INSERT INTO `student` VALUES (9, '语文', '王五', 81);
INSERT INTO `student` VALUES (10, '数学', '刘六', 81);
INSERT INTO `student` VALUES (11, '数学', '陈七', 99);

题目一：请找出各个科目的成绩都大于80的学生姓名

解法一：用子查询 + WHERE + NOT IN

一开始没有注意到是需要各科成绩都大于80，写的是 SELECT DISTINCT name FROM student WHERE score > 80，然后觉得肯定没这么简单才反应过来是全部都要大于 80，因为是要找到所有都大于 80 的嘛，所以就想到可以先找出有成绩小于等于 80 的学生，其他学生就是所有科目都大于 80的，自然就想到用 子查询 然后再 NOT IN 一下就可以了

SELECT DISTINCT `name` FROM student WHERE `name` NOT IN (SELECT DISTINCT `name` FROM student WHERE score <= 80)

解法二：用子查询 + HAVING + NOT IN

这种方式跟上一种方式其实很像，只是过滤的方式不同，WHERE 是针对行过滤，而 HAVING 是针对分组过滤的，后面详细介绍两者的区别，但是这两种方式可能用 HAVING 会更好一点吧，因为 WHERE 需要一行一行去比较，而 HAVING 是一个一个分组去比较，相对来说应该会快一点，这只是我的个人观点，如有错误，请指正！

SELECT `name` FROM student GROUP BY `name` HAVING `name` NOT IN (SELECT DISTINCT `name` FROM student WHERE score <= 80)

解法三：用 HAVING + MIN 函数

这题还可以用 HAVING来解决，用这种方式应该是最简单高效的，太久没用 HAVING，很多细节都忘记了，讲解完题目再给出 HAVING 的相关知识点吧，已经遗忘的小伙伴们可以先看完 HAVING 的讲解再来看题

注意：score 字段必须是整数类型的才能用MIN函数比较，我一开始没注意直接用的是字符串的类型，然后结果一直不对，使用 SELECT name, MIN(score) FROM student GROUP BY name 才发现字符串类型不能用MIN函数，不然比较的是 ASCII 值，所以如果是字符串类型的可以用解法三的方式来解决或者通过 MIN(score + 0) 转成整数类型

SELECT `name` FROM `student` GROUP BY `name` HAVING MIN(score) > 80

解法四：用 HAVING + 计数的方式

因为 HAVING 是先分组再过滤的，所以就想到先统计分组后每个分组的数量，然后再统计每个分组为分数大于 80 的数量，只要两者相等就是每科成绩都大于 80，比如张三分组后 count(*) = 3，但是 SUM(IF(score > 80, 1, 0)) = 1，因为一科英语 81 大于 80，两者不相等，所以不符合题目要求的各科都大于 80

SELECT `name` FROM `student` GROUP BY `name` HAVING COUNT(*) = SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END)
或
SELECT `name` FROM `student` GROUP BY `name` HAVING COUNT(*) = SUM(IF(score > 80, 1, 0))

题目二：请找出只选修了数学的学生姓名

解法一：用子查询 + WHERE + NOT IN

这道题同样可以用子查询 + NOT IN的方式来解决，因为我们是想找到只选修了数学的学生，那我们就通过逆向思维先找到选修了其他学科的学生，其他学生自然就是只选修了数学的学生

SELECT DISTINCT `name` FROM student WHERE `name` NOT IN (SELECT DISTINCT `name` FROM student WHERE `subject` != '数学');

解法二：用子查询 + HAVING + NOT IN

这是与一个朋友讨论这道题的时候他给出的一个写法，他当时给出的是下面第一行的写法，那时候对 HAVING 不太熟悉，所以也没有发现问题所在，后面实际去测试的时候发现语法都错误了，简单来说就是 HAVING 后面能使用的元素只有 3 种：常数、聚合函数和聚合键，后面再仔细讲解这三种元素，下面给出可行的解法，其实跟解法一也是类似的，只是一个用 WHERE 一个用 HAVING

错解：SELECT `name` FROM student GROUP BY `name` HAVING `subject` NOT IN ('语文', '英语')
正解：SELECT `name` FROM student GROUP BY `name` HAVING `name` NOT IN (SELECT DISTINCT `name` FROM student WHERE `subject` != '数学')

解法三：用 HAVING + 计数的方式

因为 HAVING 是先分组再过滤的，所以就想到先统计分组后每个分组的数量，然后再统计每个分组选修了数学的数量，只要相等就是只选修了数学，不等就是还选修了其他的科目

SELECT `name` FROM student GROUP BY `name` HAVING COUNT(*) = SUM(CASE WHEN `subject` = '数学' THEN 1 ELSE 0 END)
或
SELECT `name` FROM student GROUP BY `name` HAVING COUNT(*) = SUM(IF(`subject` = '数学', 1, 0))

三、知识点补充

1、HAVING 介绍

HAVING 通常与 GROUP BY 搭配使用，写在 GROUP BY 的后面，虽然也可以单独使用，但是一般有 HAVING 就有 GROUP BY，当然 GROUP BY 是可以单独使用的。GROUP BY 的功能是分组，对同类记录进行分组，比如按姓名划分，记录由多变少，而 HAVING 的作用是由多变少之后再变少，针对组内进行过滤，比如上面的例子，根据姓名分组后，属于张三的数据聚合在一起后，再通过 MIN 函数进行过滤，如下图所示

2、 HAVING 的使用注意事项

HAVING 子句的构成要素：常数、聚合函数、聚合键，聚合函数就是 MIN、MAX、AVG、COUNT、SUM 等函数，聚合键就是 GROUP BY 子句中指定的列名，例如 SELECT name FROM student GROUP BY name HAVING subject NOT IN (‘语文’, ‘英语’) 这样写就会报错，因为 HAVING 后面的 subject 不是这三要素之一，不能直接用，改成 SELECT name FROM student GROUP BY name, subject HAVING subject NOT IN (‘语文’, ‘英语’) 这样才不会报错，此时的 subject 就是聚合键；还有像 HAVING COUNT(*）= 5 这种，COUNT(*) 就是聚合函数，3 是常数
HAVING 是分组后对组内元素进行过滤，所以是出现在 GROUP BY 后面的，而 WHERE 是在分组前对每一行进行过滤，是出现在 GROUP BY 前面的
HAVING 子句的各要素之间可以通过运算符 (>, <, =, !=, <>)、IN、NOT IN 等进行计算