开发工具：
- mysql-8.0
- DataGrip
数据源：chapter13_user.csv
id,name,sex,class E001,李明,男,一班 E002,张华,男,一班 E003,薛娟,女,二班
数据源：chapter13_score.csv
id,score,month_num E001,687,1月 E002,667,1月 E003,686,1月 E001,616,2月 E002,699,2月 E003,503,2月 E001,596,3月 E002,622,3月 E003,593,3月
（1）子查询的分类
（1.1）select子查询
select子查询是指select后面是一个完整的select语句。比如，我们要获取每位同学每次月考的成绩与全部同学全部成绩的平均值。
select id, score, (select avg(score) from demo.chapter13_score) as avg_score from demo.chapter13_score; -- 窗口函数实现 select id,score,avg(score) over() as avg_score from demo.chapter13_score;
查询结果：
（1.2）from子查询
from子查询是指from后面是一个完整的select语句。比如，我们要获取每次月考中平均成绩在600分以上的同学的基本信息。
-- 方案一 select avg_table.id, avg_table.avg_score, chapter13_user.name, chapter13_user.sex, chapter13_user.class from (select id, avg(score) as avg_score from demo.chapter13_score group by id having avg_score > 600) as avg_table left join demo.chapter13_user on demo.chapter13_user.id = avg_table.id; -- 方案二 select avg_table.id, avg_table.avg_score, chapter13_user.name, chapter13_user.sex, chapter13_user.class from (select id, avg(score) as avg_score from demo.chapter13_score group by id ) as avg_table left join demo.chapter13_user on demo.chapter13_user.id = avg_table.id where avg_table.avg_score > 600;
运行上面的代码，具体运行结果如下表所示。
（1.3）where子查询
where子查询是指where后面是一个完整的select语句，用它查询出来的结果进行条件筛选。比如我们要把平均成绩大于600分的同学的每次月考成绩提取出来
select id, score, month_num from demo.chapter13_score where id in (select id from demo.chapter13_score group by id having avg(score) > 600) ;
运行上面的代码，就会得到平均成绩大于600分的每位同学的每次月考成绩，具体运行结果如下表所示。
在where后面除了可以使用in，我们还可以使用>、<、!=等其他比较运算符，比如，我们要获取chapter13_score表中大于平均成绩的成绩记录，可以通过如下代码实现：
select id, score, month_num from demo.chapter13_score where score > (select avg(score) from demo.chapter13_score) ;
运行上面的代码，具体运行结果如下表所示。
我们要获取每个人几个月中成绩第二的数据
-- where子查询实现 select * from demo.chapter13_score as t1 where score = (select score from demo.chapter13_score t2 where t1.id = t2.id order by score desc limit 1,1); -- 窗口函数 select id, score, month_num from (select id, score, month_num, row_number() over(partition by id order by score desc ) as rank_num from demo.chapter13_score) as rank_table where rank_num = 2;
（2）with建立临时表
from子查询语句本质上相当于建立了一张临时表，这种方法有一个缺点是如果我们要对子查询部分重复使用，此部分代码就需要重复执行，这样是很耗费时间和计算资源的。解决重复计算问题有两种办法：第一种就是在数据库中建立一张实际存在的表；第二种是在一段代码最开始部分建立一张临时表，然后在代码的后面部分可以一直调用这张临时表。我们这里主要讲一下第二种方法的实现，即通过with来建立临时表，建立临时表的这部分查询在同一个程序中只执行一次，并将查询结果存储在用户的临时表空间中，可以被多次使用，直到整个程序结束。
我们来举个例子，比如，我们现在要给每位同学的平均成绩加一个标签，大于600或小于600，我们可以通过子查询的方式先把平均成绩大于600分的同学筛选出来，然后加一个常数列大于600；再通过子查询的方式先把平均成绩小于600分的同学筛选出来，然后加一个常数列小于600；最后把上面的两张表通过union的形式连接起来。具体实现代码如下：
select id, avg(score) as avg_score, '大于600' as score_bin from demo.chapter13_score group by id having avg_score > 600 union all select id, avg(score) as avg_score, '小于600' as score_bin from demo.chapter13_score group by id having avg_score < 600;
运行上面的代码，具体运行结果如下表所示。
用with建立临时表的实现代码如下：
-- with用法,创建临时表 with avg_score_table as ( select id, avg(score) as avg_score from demo.chapter13_score group by id ) select id,avg_score,'大于600' as score_bin from avg_score_table where avg_score > 600 union all select id,avg_score,'小于600' as score_bin from avg_score_table where avg_score < 600;
with建立临时表的结构如下：
with临时表名as( 临时表建立语句部分 ) -- 开始正式查询 select * from 临时表名
上面的演示中with只建立了一张临时表，我们还可以使用with同时建立多张临时表，结构如下：
with临时表名1 as( 临时表建立语句部分 ), 临时表名2 as( 临时表建立语句部分 ), 临时表名3 as( 临时表建立语句部分 ), ...... 临时表名n as( 临时表建立语句部分 ), -- 开始正式查询 select * from 临时表名
比如我们现在要获取男性同学的平均成绩，那么我们就可以先分别生成两张临时表，一张是每位同学的平均成绩表，另一张是男性信息表，然后将这两张表进行连接，且把性别（sex）字段为空的数据过滤掉即可，具体实现代码如下：
-- 比如我们现在要获取男性同学的平均成绩 -- 平均成绩 with avg_score_table as ( select id, avg(score) as avg_score from demo.chapter13_score group by id ), -- 建立男性临时表 user_table as ( select id,name,sex from chapter13_user where sex = '男' ) select user_table.id, user_table.name, user_table.sex, avg_score_table.avg_score from user_table left join avg_score_table on avg_score_table.id = user_table.id;
运行上面的代码，具体运行结果如下表所示。
