MySQL之子查询_from 子查询

作者：Li_阴宅 | 2024-07-23 20:47:52

踩

from 子查询

（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
3
4
5
6
7
8
9

查询结果：
在这里插入图片描述

（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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

运行上面的代码，具体运行结果如下表所示。
在这里插入图片描述

（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) ;
1
2
3
4
5
6
7
8

运行上面的代码，就会得到平均成绩大于600分的每位同学的每次月考成绩，具体运行结果如下表所示。
在这里插入图片描述在where后面除了可以使用in，我们还可以使用>、<、!=等其他比较运算符，比如，我们要获取chapter13_score表中大于平均成绩的成绩记录，可以通过如下代码实现：

select id,
       score,
       month_num
from demo.chapter13_score
where score > (select avg(score)
     from demo.chapter13_score) ;
1
2
3
4
5
6

运行上面的代码，具体运行结果如下表所示。
在这里插入图片描述
我们要获取每个人几个月中成绩第二的数据

-- 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

在这里插入图片描述

（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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

运行上面的代码，具体运行结果如下表所示。
在这里插入图片描述
用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;
1
2
3
4
5
6
7
8
9
10
11
12

with建立临时表的结构如下：

with临时表名as( 临时表建立语句部分 ) 
-- 开始正式查询 
select * from 临时表名
1
2
3

上面的演示中with只建立了一张临时表，我们还可以使用with同时建立多张临时表，结构如下：

with临时表名1 as( 临时表建立语句部分 ),
临时表名2 as( 临时表建立语句部分 ),  
临时表名3 as( 临时表建立语句部分 ), 
...... 临时表名n as( 临时表建立语句部分 ),
-- 开始正式查询 
select * from 临时表名
1
2
3
4
5
6

比如我们现在要获取男性同学的平均成绩，那么我们就可以先分别生成两张临时表，一张是每位同学的平均成绩表，另一张是男性信息表，然后将这两张表进行连接，且把性别（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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20