mysql求和 子查询_MySQL 子查询（嵌套查询）介绍

MySQL 子查询(嵌套查询)介绍

马富天

2018-12-14 11:52:57

24

【摘要】子查询也可以叫做嵌套查询，即在一个 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

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

顶0

踩0

我要打赏