从 01 开始
2023-11-26
目录

子查询

# 150.子查询

前面学习了视图这个非常方便的工具，本节将学习以视图为基础的子查询（subquery） 。子查询的特点概括起来就是一张一次性视图。

‍ ‍

# 什么是子查询

有时候我们想要执行一个复杂的查询，获得结果，再从该结果中查询数据。

听起来是不是可以用视图解决？但问题是，如果每次都新建视图，再查询，效率就太低了；因为不是每个复杂的查询都是频繁用到的，此时我们就可以用子查询。示例：

select product_type, cnt_product
from (
    select product_type, count(*) as cnt_product
    from Product
    group by product_type 
) as ProductSum;

--结果：
+--------------+-------------+
| product_type | cnt_product |
+--------------+-------------+
| 衣服         |           2 |
| 办公用品     |           2 |
| 厨房用具     |           4 |
+--------------+-------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

‍ 注意：

  1. 必须指定别名，不然会报错
  2. 在 Oracle 的 FROM 子句中，不能使用 AS （会发生错误），上述 SQL 需去除 as 关键字。 ‍ 可以看到，这和从视图中查询数据的结果是一样的。子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。

虽然“AS ProductSum ”就是子查询的名称，但由于该名称是一次性的，因此不会像视图那样保存到磁盘，而是在 SELECT 语句执行之后就消失了。

# 子查询的层数

子查询里还可以再嵌套一层子查询：

SELECT product_type, cnt_product
  FROM (SELECT *
          FROM (SELECT product_type, COUNT(*) AS cnt_product
                  FROM Product
                 GROUP BY product_type) AS ProductSum 
         WHERE cnt_product = 4) AS ProductSum2;
1
2
3
4
5
6

‍ 原则上没有限制子查询的层数，但如果太多的话几乎没有可读性，性能也越来越差，因此，请尽量避免使用多层嵌套的子查询。

# 标量子查询（scalar subquery）

标量：单一的意思，在数据库之外的领域也经常使用。

之前我们学习的 SQL 基本上都是返回多行结果，即使是返回一行的，也会有多列；

而标量子查询就是只能返回 1 行 1 列的查询 ，也就是返回表中某一行的某一列的值（或者是用聚合函数计算后的值），例如“10”或者“东京都”这样的值。

为什么要用标量子查询？由于它返回的是单一的值，因此标量子查询的返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中。

例如，我们想要查询出销售单价高于平均销售单价的商品。如果像这样用聚合函数，是会出错的：

-- 在where子句中不能使用聚合函数
select product_id, product_name, sale_prict
from Product
where sale_price > avg(sale_price)
1
2
3
4

‍ 这时标量子查询就可以发挥它的功效了：

select product_id, product_name, sale_price
from Product
where sale_price > (select avg(sale_price) from Product);

-- 结果：
+------------+--------------+------------+
| product_id | product_name | sale_price |
+------------+--------------+------------+
| 0003       | 运动T恤      |       4000 |
| 0004       | 菜刀         |       3000 |
| 0005       | 高压锅       |       6800 |
+------------+--------------+------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

特别注意：标量子查询绝不能返回多行结果，否则就仅仅是一个普通的子查询了，也就不能用比较运算符了 ‍

# 标量子查询的书写位置

标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

例如，在 select 中使用：

select product_id,
    product_name,
    sale_price,
    (select avg(sale_price) from Product) as avg_price
from product;

-- 结果：
+------------+--------------+------------+-----------+
| product_id | product_name | sale_price | avg_price |
+------------+--------------+------------+-----------+
| 0001       | T恤衫        |       1000 | 2097.5000 |
| 0002       | 打孔器       |        500 | 2097.5000 |
| 0003       | 运动T恤      |       4000 | 2097.5000 |
| 0004       | 菜刀         |       3000 | 2097.5000 |
| 0005       | 高压锅       |       6800 | 2097.5000 |
| 0006       | 叉子         |        500 | 2097.5000 |
| 0007       | 擦菜板       |        880 | 2097.5000 |
| 0008       | 圆珠笔       |        100 | 2097.5000 |
+------------+--------------+------------+-----------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

‍ 注意不能直接写聚合函数，例如这样写是会出错的：

SELECT 
    product_id, 
    product_name,
    sale_price, 
    avg(sale_price) as sale_price_all
from Product
1
2
3
4
5
6

‍ 在 having 子句中使用：

select product_type,  avg(sale_price)
from product
group by product_type
having avg(sale_price) > (select avg(sale_price) from Product);

-- 结果：
+--------------+-----------------+
| product_type | avg(sale_price) |
+--------------+-----------------+
| 衣服         |       2500.0000 |
| 厨房用具     |       2795.0000 |
+--------------+-----------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

‍ 该查询的含义：看看哪一类商品的平均单价，高于总体的平均单价

# 关联子查询

假设有这样的查询需求：找出每一类商品中，单价高于该类商品平均价的商品。

比如，以厨房用具中的商品为例，有 4 个商品：

商品名称 销售单价
菜刀 3000
高压锅 6800
叉子 500
擦菜板 880

4 种商品的平均价格：(3000 + 6800 + 500 + 880) / 4 = 2795 。因此菜刀和高压锅就是比较高的，这两种商品就是我们要选取的对象。

再比如，衣服分组的平均销售单价是：(1000 + 4000) / 2 = 2500 ，因此运动 T 恤就是要选取的对象。

办公用品分组的平均销售单价是：(500 + 100) / 2 = 300，因此打孔器就是我们要选取的对象。

我们就是要通过 SQL，找出这些商品。

按照商品种类计算平均价格并不是什么难事，但是，如果我们使用前一节（标量子查询）的方法，直接把上述 SELECT 语句使用到 WHERE 子句当中的话，就会发生错误。

-- 发生错误的子查询
SELECT product_id, product_name, sale_price
  FROM Product
 WHERE sale_price > (SELECT AVG(sale_price)
                          FROM Product
                         GROUP BY product_type);
1
2
3
4
5
6

错误原因也很简单，该子查询会返回 3 行结果（2795、2500、300），并不是标量子查询。在 WHERE 子句中使用子查询时，该子查询的结果必须是单一的。 ‍ 此时我们就可以用关联子查询了，我们直接看修改后的 SQL：

select product_type, product_name, sale_price
    from Product as p1
     where sale_price > (select avg(sale_price)
                         from Product as p2
                         where p1.product_type = p2.product_type
                         group by product_type);


-- 结果：
+--------------+--------------+------------+
| product_type | product_name | sale_price |
+--------------+--------------+------------+
| 办公用品     | 打孔器       |        500 |
| 衣服         | 运动T恤      |       4000 |
| 厨房用具     | 菜刀         |       3000 |
| 厨房用具     | 高压锅       |       6800 |
+--------------+--------------+------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

‍ 这样我们就能选取出办公用品、衣服和厨房用具三类商品中高于该类商品的平均销售单价的商品了。

这里起到关键作用的就是在子查询中添加的 WHERE 子句的条件 。该条件的意思就是，在同一商品种类中对各商品的销售单价和平均单价进行比较。

这次由于作为比较对象的都是同一张 Product 表，因此为了进行区别，分别使用了 P1 和 P2 两个别名。在使用关联子查询时，需要在表所对应的列名之前加上表的别名，以“< 表名 >.< 列名 > ”的形式记述。

事实上，上述 SQL 中，即使在子查询中不使用 GROUP BY 子句，也能得到正确的结果。这是因为在 WHERE 子句中追加了“P1.product_type=P2.product_type ”这个条件，使得 AVG 函数按照商品种类进行了平均值计算。但是为了跟前面出错的查询进行对比，这里还是加上了 GROUP BY 子句。

要理解关联子查询也不难，关联子查询也和 GROUP BY 子句一样，可以对集合进行切分。由于是只计算一组商品的平均值，因此关联子查询实际只能返回 1 行结果。这也是关联子查询不出错的关键。

​​

关联子查询执行时，DBMS 内部的执行情况大概是这样的：

​​ ‍

特别注意：结合条件一定要写在子查询中。如果这样写是错误的：

-- 错误的写法
select product_type, product_name, sale_price
    from Product as p1
     where p1.product_type = p2.product_type  --将关联条件移到子查询之外
     and   sale_price > (select avg(sale_price)
                         from Product as p2
                         group by product_type);
1
2
3
4
5
6
7
8

简单来说，就是作用域的问题。P2 这个名字仅仅在子查询内有效，而 P1 这个名字在整改查询都有效。换句话说，就是“内部可以看到外部，而外部看不到内部”。 ‍ （完）

上次更新: 2025/5/9 14:55:39
视图
函数

← 函数→

最近更新
01
学点统计学：轻松识破一本正经的胡说八道
06-05
02
2025 年 5 月记
05-31
03
《贫穷的本质》很棒，但可能不适合你
05-27
更多文章>
Theme by Vdoing | Copyright © 2022-2025 | 粤 ICP 备 2022067627 号 -1 | 粤公网安备 44011302003646 号 | 点击查看十年之约