子查询
# 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)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
注意：
- 必须指定别名，不然会报错
- 在 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;
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)
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)
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)
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
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)
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);
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)
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);
2
3
4
5
6
7
8
简单来说，就是作用域的问题。P2这个名字仅仅在子查询内有效，而P1这个名字在整改查询都有效。换句话说，就是“内部可以看到外部，而外部看不到内部”。
