打赏银两×

博主辛苦了，我要打赏银两给博主，犒劳犒劳站长。

支付宝支付

微信支付

QQ钱包

MySQL 子查询（嵌套查询）介绍

学习笔记马富天 2018-12-14 11:52:57 34 0

【摘要】子查询也可以叫做嵌套查询，即在一个 SQL 查询中包含了其它子 SQL 查询的结果，应用场景还是很常见的，本文会给出一个具体的实例仅供大家和自己学习参考使用。

相信大家在脚本语言的使用上都快忘记了 SQL 子查询的功能了，通常都是使用多条 SQL 来完成查询某一数据，既然我们是做技术的，就应该多运用一些高级的查询方法。

子查询一般会出现在 WHERE 和 FROM 子句、以及 SELECT、HAVING、EXISTS 子句等等中，如：

SELECT * FROM `student` WHERE id IN(SELECT id FROM `student`);

SELECT * FROM (SELECT * FROM `student`) AS tmp;

SELECT (SELECT id FROM `student` AS s2 WHERE s1.id = s2.id) AS id  FROM `student` AS s1;

SELECT * FROM `student` GROUP BY class_id HAVING class_id IN (SELECT id FROM `class`);

SELECT * FROM `student` WHERE EXISTS(SELECT 1)；

通过以上总结出来的几种子查询方式，可以在此基础上进行扩展。

以下创建一些表用来做测试数据，一共三张表，学生表 student,班主任 teacher,班级表 class：

1、查询各位班主任（含教师姓名）带领的班级（含班级名称）每位学生的平均成绩：

```
SELECT
```
```
	t.id,
```
```
	t.`name` AS "班主任",
```

	(SELECT c.class_name FROM `class` AS c WHERE c.id = t.class_id ) AS "班级名称",

	(SELECT AVG(score) FROM `student` AS s WHERE s.class_id = t.class_id) AS "班级平均成绩"

```
FROM 
```
```
	`teacher` AS t;
```

2、查询各班学生成绩在 90 分及以上的人数（班主任名称 + 班级名称 + 90分及以上的人数）：

```
SELECT 
```
```
	t.id,
```
```
	t.`name` AS "班主任",
```

	(SELECT c.class_name FROM `class` AS c WHERE c.id = t.class_id) AS "班级名称",

	(SELECT count(*) FROM `student` AS s WHERE s.class_id = t.class_id AND s.score >= 90) AS "90分及以上的学生人数"

```
FROM
```
```
	`teacher` AS t;
```

3、查询学生成绩大于平均成绩的学生姓名

先把平均成绩查询出来，然后查找成绩大于平均成绩的学生

```
SELECT
```
```
	s.id,
```
```
	s.`name` AS "学生姓名"
```
```
FROM
```
```
	`student` AS s
```
```
WHERE
```

	s.score > (SELECT AVG(score) FROM `student`);

4、查询每个班级中成绩最高的学生姓名以及对应的成绩

```
SELECT 
```
```
	c.id,
```
```
	c.class_name AS "班级名称",
```

	(SELECT MAX(s.score) FROM `student` AS s WHERE s.class_id = c.id) AS "最高分"

```
FROM
```
```
	`class` AS c;
```

4-1、查询每个班级中成绩最高的学生姓名以及对应的成绩，并按成绩升序排序

这里将子查询结果作为一张临时表。

```
SELECT
```

	(SELECT c.class_name FROM `class` AS c WHERE c.id = class_id) AS "班级名称",

```
	tmp.score AS "最高分",
```
```
	tmp.name AS "学生姓名"
```
```
FROM
```

	(SELECT * FROM `student` ORDER BY score DESC) as tmp

```
GROUP BY
```
```
	tmp.class_id
```
```
ORDER BY
```
```
	tmp.score ASC;
```

（备注：GROUP BY 按类别聚合输出，每个类别只输出一条记录，而 having 就是限制每个类别的条件，比如 class_id 等于或者小于啥啥啥的）

5、查询 "物联网工程","计算机科学与技术" 这两个班级的学生人数（前提是不知道这两个班级的班级 id ）

```
SELECT
```
```
	c.id,
```
```
	c.class_name AS "班级名称",
```

	(SELECT COUNT(*) FROM `student` AS s WHERE s.class_id = c.id) AS "班级人数"

```
FROM
```
```
	`class` AS c
```
```
WHERE
```

	c.class_name in("计算机科学与技术","物联网工程");

6、查询各位班主任（含教师姓名）带领的班级（含班级名称）每位学生的平均成绩，并按照平均成绩从高到低排序：

```
SELECT
```

	(SELECT class_name FROM `class` AS c WHERE c.id = tmp.class_id) AS "班级名称",

	(SELECT t.name FROM `teacher` AS t WHERE t.class_id = tmp.class_id) AS "班主任",

```
	tmp.avg_score AS "评价成绩"
```
```
FROM
```
```
	(SELECT
```
```
		s.class_id,
```
```
		avg(score) AS avg_score
```
```
	FROM
```
```
		`student` AS s
```
```
	GROUP BY
```
```
		s.class_id
```
```
	ORDER BY
```
```
		avg_score DESC) AS tmp;
```

以上的例子，都是自己设想出来的，其实只要掌握了子查询的方法，就能够写出很多复制的 SQL 来实现查询功能。总体来说，同一个查询功能实现的方法可以多种，并不是唯一的，然后重点在于采用出效率最高的一种方式。

例子中用到的所有基本信息：

```
CREATE TABLE `student` (
```
```
  `id` int(11) NOT NULL AUTO_INCREMENT,
```
```
  `name` varchar(255) DEFAULT NULL,
```
```
  `class_id` int(11) DEFAULT NULL,
```
```
  `score` int(11) DEFAULT NULL,
```
```
  PRIMARY KEY (`id`)
```
```
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

INSERT INTO `student` VALUES ('1', '刘一', '1', '90');

INSERT INTO `student` VALUES ('2', '陈二', '1', '88');

INSERT INTO `student` VALUES ('3', '张三', '2', '92');

INSERT INTO `student` VALUES ('4', '李四', '1', '96');

INSERT INTO `student` VALUES ('5', '王五', '2', '94');

INSERT INTO `student` VALUES ('6', '赵六', '2', '96');

INSERT INTO `student` VALUES ('7', '孙七', '1', '90');

INSERT INTO `student` VALUES ('8', '周八', '2', '87');

INSERT INTO `student` VALUES ('9', '吴九', '3', '89');

INSERT INTO `student` VALUES ('10', '郑十', '3', '93');

```
CREATE TABLE `teacher` (
```
```
  `id` int(11) NOT NULL AUTO_INCREMENT,
```
```
  `name` varchar(255) DEFAULT NULL,
```
```
  `class_id` int(11) DEFAULT NULL,
```
```
  PRIMARY KEY (`id`)
```
```
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

INSERT INTO `teacher` VALUES ('1', '刘德华', '1');

INSERT INTO `teacher` VALUES ('2', '黎明', '2');

INSERT INTO `teacher` VALUES ('3', '梁朝伟', '3');

```
CREATE TABLE `class` (
```
```
  `id` int(11) NOT NULL AUTO_INCREMENT,
```

  `class_name` varchar(255) DEFAULT NULL,

```
  PRIMARY KEY (`id`)
```
```
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

INSERT INTO `class` VALUES ('1', '计算机科学与技术');

INSERT INTO `class` VALUES ('2', '软件工程');

INSERT INTO `class` VALUES ('3', '物联网工程');

小提示：MySQL 中五种常用的聚合函数：max(字段名) 取最大值、min(字段名)取最小值、sum(字段名) 求和、avg(字段名) 求平均值、count(字段名) 统计记录总数，并通常与 GROUP BY、Having 用在一起，按类别统计,分组就是将数据分为多个小组，以小组为单位进行查询。如下：

select 类别, sum(字段) as 数量之和 from A

group by 类别

having sum(字段) > 18

版权归马富天个人博客所有

本文标题：《MySQL 子查询（嵌套查询）介绍》

本文链接地址：http://www.mafutian.com/401.html

转载请务必注明出处，小生将不胜感激，谢谢! 喜欢本文或觉得本文对您有帮助，请分享给您的朋友 ^_^

顶0

踩0

上一篇《 QQ、微信内置浏览器的 UA（useragent）》下一篇《 ASCII、ANSI、GB2312、GBK、UNICODE、UTF-8 五种编码的简介》

暂无评论

评论审核未开启