一道面试题:写出SQL语句实现下述功能

最新推荐文章于 2024-05-16 10:27:08 发布

ncepu_Chen

最新推荐文章于 2024-05-16 10:27:08 发布

阅读量2k

点赞数 3

分类专栏：随笔 # 数据库文章标签： SQL SQL面试子查询关联子查询连接表

本文链接：https://blog.csdn.net/ncepu_Chen/article/details/102511943

版权

随笔同时被 2 个专栏收录

7 篇文章 5 订阅

订阅专栏

数据库

7 篇文章 1 订阅

订阅专栏

一道面试题:写出SQL语句实现下述功能

题目: 根据表结构写出SQL语句实现下述功能
解析
附录: 测试数据

在这里,感谢@瑾同学,给我发过来一道特别有趣的SQL题目,题目如下:

题目: 根据表结构写出SQL语句实现下述功能

创建四张表如下:

-- 学生表
CREATE TABLE `Student`
(
    `s_id`   VARCHAR(20), -- 学生学号
    `s_name` VARCHAR(20), -- 学生姓名
    PRIMARY KEY (`s_id`)
);

-- 课程表
CREATE TABLE `Course`
(
    `c_id`   VARCHAR(20), -- 课程编号
    `c_name` VARCHAR(20), -- 课程名称
    `t_id`   VARCHAR(20), -- 教师编号
    PRIMARY KEY (`c_id`)
);

-- 教师表
CREATE TABLE `Teacher`
(
    `t_id`   VARCHAR(20), -- 教师编号
    `t_name` VARCHAR(20), -- 教师姓名
    PRIMARY KEY (`t_id`)
);

-- 成绩表
CREATE TABLE `Score`
(
    `s_id`    VARCHAR(20), -- 学生学号
    `c_id`    VARCHAR(20), -- 课程编号
    `s_score` INT(3),      -- 成绩
    PRIMARY KEY (`s_id`, `c_id`)
);

分别写出SQL语句完成如下功能:

查询姓张的老师的个数(难度:★☆☆☆)
查询平均成绩大于60的所有学生的学号和平均成绩(难度:★★☆☆)
查询没学过张三老师的课的学生的学号,姓名(难度:★★★☆)
查询课程编号为01的课程比课程编号为02的课程成绩高的所有学生的学号(难度:★★★☆)
查询和02号同学学习的课程完全相同的其他同学的学号与姓名(难度:★★★★)

解析

题目一: 模糊查询(难度:★☆☆☆)

题目一直接模糊查询即可,没有难度

select count(*) from `Teacher` where `t_name` like '张%';

题目二: 聚合查询(难度:★★☆☆)

题目二是一个聚合查询,也比较简单

select `s_id`, avg(`s_score`) from Score 	-- 查询项为 学生id, 学生平均分
group by `s_id` 							-- 以学生id分组进行聚合查询
having avg(`s_score`) > 60 					-- 定义筛选条件: 只显示平均分为60以上的

在这里需要注意的是,在这里对聚合条件进行过滤,不能使用where从句,而应该使用having从句.where从句和having从句的区别有以下三点:

从作用时机来看:
- where从句是一个约束声明,在查询数据库的结果返回之前对数据库中的查询条件进行约束,即在结果返回之前起作用.
- having从句是一个过滤声明,在查询数据库的结果返回之后对查询结果进行过滤进行过滤,即在结果返回之后起作用.
从作用对象来看:
- where从句约束的是数据库表中原有的字段,因此where从句中不能使用聚合函数.
- having从句约束是select从句中选出的字段,因此having从句中可以使用聚合函数.
下面举例说明这一点
- 下面这种场景,既可以使用where从句,也可以使用having从句,因为所约束的字段s_score既是数据库表中原有字段,也是select从句选中的字段.
```
-- 下面两个SQL语句都是有效的
select `s_id`, `c_id`, `s_score` from `Score` where `s_score` > 50;
select `s_id`, `c_id`, `s_score` from `Score` having `s_score` > 50;
```
- 下面这个场景,不能使用having从句,因为所约束的字段s_score没有在select从句被选中.
```
-- 使用where语句有效
select `s_id`, `c_id` from `Score` where `s_score` > 50;

-- 使用having语句无效: Unknown column 's_score' in 'having clause'
select `s_id`, `c_id` from `Score` having `s_score` > 50;
```
- 下面这个场景,不能使用where从句,因为所约束的字段avg_score不是数据库表中原有的字段.
```
-- 使用having从句有效
select `s_id`, avg(`s_score`) as avg_score from Score group by `s_id` having avg_score > 60;

-- 使用where从句无效: Unknown column 'avg_score' in 'having clause'
select `s_id`, avg(`s_score`) as avg_score from Score where avg_score > 60 group by `s_id`;
```
在不使用聚合查询(group by或聚合函数如count(),min())时,having从句中的条件会被合并到where从句中¹.
从书写位置上来看: where从句要写在聚合(grouping by)从句之前,而having从句要写在聚合(grouping by)从句之后.

题目三: 多层嵌套子查询(难度:★★★☆)

题目要求查询没学过张三老师的课的学生的学号和姓名,我们可以进行三次子查询并将他们嵌套起来,内层的查询结果作为外层查询的条件.

先查询张三老师上过的所有课程的c_id集合:

select `c_id` from `Course` join `Teacher` using (`t_id`) where `Teacher`.`t_name` = '张三';

再查询所有上过’张三’老师课的学生的s_id集合:

select distinct `s_id` from `Score` where `c_id` in ${张三老师课程的c_id集合};

最后对上述学生集合取反集:

select `s_id`, `s_name` from `Student` where `s_id` not in ${上过张三老师课的学生的s_id集合};

将上面三个子查询嵌套起来,得到这道题的结果:

select `s_id`, `s_name` from `Student` where `s_id` not in (
	select distinct `s_id` from `Score` where `c_id` in (
		select `c_id` from `Course` join `Teacher` using (`t_id`) where `Teacher`.`t_name` = '张三'));

查看其运行计划如下:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	Student	ALL					6	100	Using where
2	SUBQUERY	Score	ALL					12	100	Using where
2	SUBQUERY	Course	eq_ref	PRIMARY	PRIMARY	62	sql_test.Score.c_id	1	100	Distinct
2	SUBQUERY	Teacher	ALL	PRIMARY				4	75	Using where; Distinct; Using join buffer (Block Nested Loop)

题目四: 连接子查询(难度:★★★☆)

题目要求查找课程编号为01的课程比课程编号为02的课程成绩高的所有学生的学号,因此我们可以通过两次子查询分别得到两门课的成绩表,再进行连接查询.

分别查询两门课的成绩表:

(select * from `Score` where `c_id` = '01') as score_of_course01
(select * from `Score` where `c_id` = '02') as score_of_course02

将这两张成绩表进行连接查询:

select `Score_of_course02`.`s_id`
from `Score_of_course01` join `Score_of_course02` using (`s_id`)
where `Score_of_course01`.`s_score` > `Score_of_course02`.`s_score`

将上面两步合成在一起,得到这道题的结果:

select `Score_of_course02`.`s_id`
from (select * from `Score` where `c_id` = '01') as `Score_of_course01`
join (select * from `Score` where `c_id` = '02') as `Score_of_course02` using (`s_id`)
where `Score_of_course01`.`s_score` > `Score_of_course02`.`s_score`

查看其运行计划如下:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL					12	100
1	PRIMARY	<derived3>	ref	<auto_key0>	<auto_key0>	62	score_of_course01.s_id	2	100	Using where
3	DERIVED	score	ALL					12	100	Using where
2	DERIVED	score	ALL					12	100	Using where

题目五: 子查询+聚合查询(难度:★★★★)

题目要求查询和02号同学学习的课程完全相同的其他同学,我们反其道而行之,考虑和02号同学学习的课程有所不同的同学,其中包含两种情况:

情况1: 有一部分同学上过了’02’号同学没有上过的某一门课(上多了),要找到这些同学,需要下面两步:
1. 先找到02号同学上过的所有课程的c_id集合:
```
select `c_id` from `Score` where `s_id`='02';
```
2. 再找到上过上述集合外的课程的同学:
```
select distinct `s_id` from `Score`
where `c_id` not in ${'02'号同学上过的所有课程的`c_id`集合};
```
情况2: 还有一部分同学没有上过’02’号同学没上过的课,但没上过某门’02’号同学上过的课(上少了).我们可以对筛掉情况1后剩下的同学进行聚合查询,统计每个同学上过的课的门数,来筛掉这部分同学.
1. 查询’02’号同学上过的课程门数
```
select count(*) from `Score` where `s_id` = '02';
```
2. 聚合查询统计每个学生上过的课程门数,过滤留下与’02’号同学上过的课程门数相同的人
```
select `s_id` from `Score` group by `s_id` having count(*) = ${'02'号同学上过的课程门数}
```

结合上述两种情况,得到这道题目的结果:

select distinct `s_id` from `Score`	where `s_id` != '02'	-- 先排除掉2号同学自身
-- 排除掉情况1中的同学
and s_id not in (select distinct `s_id` from `Score` where `c_id` not in (select `c_id` from `Score` where `s_id` = '02'))
-- 排除掉情况2中的同学
group by s_id having count(*) = (select count(*) from `Score` where `s_id` = '02');

查看其运行计划如下:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	score	index	PRIMARY	PRIMARY	124		10	100	Using where; Using index
4	SUBQUERY	Score	ref	PRIMARY	PRIMARY	62	const	2	100	Using where; Using index
2	SUBQUERY	Score	index	PRIMARY	PRIMARY	124		10	100	Using where; Using index
3	SUBQUERY	Score	ref	PRIMARY	PRIMARY	62	const	2	100	Using where; Using index

若大家有更好的解法,欢迎留言讨论

附录: 测试数据

建表语句:

-- 学生表
CREATE TABLE `Student`
(
    `s_id`   VARCHAR(20), -- 学生学号
    `s_name` VARCHAR(20), -- 学生姓名
    PRIMARY KEY (`s_id`)
);

-- 课程表
CREATE TABLE `Course`
(
    `c_id`   VARCHAR(20), -- 课程编号
    `c_name` VARCHAR(20), -- 课程名称
    `t_id`   VARCHAR(20), -- 教师编号
    PRIMARY KEY (`c_id`)
);

-- 教师表
CREATE TABLE `Teacher`
(
    `t_id`   VARCHAR(20), -- 教师编号
    `t_name` VARCHAR(20), -- 教师姓名
    PRIMARY KEY (`t_id`)
);

-- 成绩表
CREATE TABLE `Score`
(
    `s_id`    VARCHAR(20), -- 学生学号
    `c_id`    VARCHAR(20), -- 课程编号
    `s_score` INT(3),      -- 成绩
    PRIMARY KEY (`s_id`, `c_id`)
);

插入数据

-- 学生表数据
INSERT INTO Student (s_id, s_name) VALUES ('01', '学生1');
INSERT INTO Student (s_id, s_name) VALUES ('02', '学生2');
INSERT INTO Student (s_id, s_name) VALUES ('03', '学生3');
INSERT INTO Student (s_id, s_name) VALUES ('04', '学生4');
INSERT INTO Student (s_id, s_name) VALUES ('05', '学生5');
INSERT INTO Student (s_id, s_name) VALUES ('06', '学生6');

-- 课程表数据
INSERT INTO Course (c_id, c_name, t_id) VALUES ('01', '课程1', '01');
INSERT INTO Course (c_id, c_name, t_id) VALUES ('02', '课程2', '01');
INSERT INTO Course (c_id, c_name, t_id) VALUES ('03', '课程3', '02');
INSERT INTO Course (c_id, c_name, t_id) VALUES ('04', '课程4', '02');
INSERT INTO Course (c_id, c_name, t_id) VALUES ('05', '课程5', '03');
INSERT INTO Course (c_id, c_name, t_id) VALUES ('06', '课程6', '04');
INSERT INTO Course (c_id, c_name, t_id) VALUES ('07', '课程7', '04');

-- 教师表数据
INSERT INTO Teacher (t_id, t_name) VALUES ('01', '张三');
INSERT INTO Teacher (t_id, t_name) VALUES ('02', '李四');
INSERT INTO Teacher (t_id, t_name) VALUES ('03', '王五');
INSERT INTO Teacher (t_id, t_name) VALUES ('04', '赵六');

-- 成绩表
INSERT INTO Score (s_id, c_id, s_score) VALUES ('01', '01', 30);
INSERT INTO Score (s_id, c_id, s_score) VALUES ('01', '02', 50);
INSERT INTO Score (s_id, c_id, s_score) VALUES ('02', '01', 60);
INSERT INTO Score (s_id, c_id, s_score) VALUES ('02', '02', 40);
INSERT INTO Score (s_id, c_id, s_score) VALUES ('03', '01', 80);
INSERT INTO Score (s_id, c_id, s_score) VALUES ('03', '02', 70);
INSERT INTO Score (s_id, c_id, s_score) VALUES ('04', '01', 44);
INSERT INTO Score (s_id, c_id, s_score) VALUES ('05', '04', 54);
INSERT INTO Score (s_id, c_id, s_score) VALUES ('06', '04', 46);
INSERT INTO Score (s_id, c_id, s_score) VALUES ('06', '05', 23);

见MySQL官方文档 ↩︎

ncepu_Chen

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
一道面试题:写出SQL语句实现下述功能

文章目录题目答案题目一:模糊查询(难度:★☆☆☆)题目二:聚合查询(难度:★★☆☆)在这里,感谢@瑾同学,给我发过来一道特别有趣的SQL题目,题目如下:题目创建四张表如下:-- 学生表CREATE TABLE `Student`( `s_id` VARCHAR(20), -- 学号 `s_name` VARCHAR(20), -- 学生姓名 PRIMARY ...
复制链接

扫一扫

专栏目录