算法工程师-SQL进阶：神奇的自连接与子查询
自连接与子查询是SQL中非常重要的两项技术，自连接是针对相同的表的联结方法，是SQL语言面向集合编程思想的集中体现，而子查询有很多变式，关联子查询技术是在实际中进行行与行之间的比较时非常重要的方法，特别是与自连接相结合的自关联子查询。一旦熟练掌握以上两种技术，实际中很多问题便能迎刃而解。
本小节，我们分为两个部分：
第一部分介绍自连接、子查询相关的基础概念（熟悉的同学可以跳过）；
第二部分是实战篇，将通过具体事例来学习一下如何应用自连接以及关联子查询技术。
目录：
一、概念篇
1、视图
视图的本质是一个SELECT语句。
大家对视图应该都比较了解了，一般我们会把频繁使用的SELECT语句(中间查询结果)保存为一个视图，然后只需把视图当做一个表来使用，就可以方便地得到想要的结果了。
视图实际上是通过保存查询语句（ SELECT ）来为用户提供便利的。视图本身不会将数据存储到任何设备中，也不会保存到其他任何地方，视图的本质是一个SELECT语句。因此，视图中的数据会随着原表的变化自动更新，这也是将中间查询结果暂时存储在物理表中所不具备的优势。
视图的创建方法：
CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>注意：SELECT 语句需要书写在 AS 关键字之后。SELECT 语句中列的排列顺序和视图中列的排列顺序相同。
2、子查询
子查询就是一次性视图。
子查询就是将用来定义视图的 SELECT 语句直接用于FROM 子句当中。举个例子：
SELECT a,
b
FROM
(SELECT a,
COUNT(*) AS b
FROM A
GROUP BY a) AS TMP;上面的SQL中，"TMP"就是子查询的名称，但由于该名称是一次性的，因此不会像视图那样把SELECT语句保存在存储介质（硬盘）之中，而是在该 SELECT 语句执行之后就消失了。
因此，子查询的本质就是一次性的视图。子查询可以无限嵌套，作为最内层查询会首先执行。
有一种比较特殊的子查询，它的返回结果是单一的值，即返回一个数(标量)，叫做标量子查询。因为，标量子查询仅返回一个数，因此它可以出现在select、where、having、group by、order by子句，几乎任何地方都可以使用标量子查询。
3、关联子查询
关联子查询直观的讲就是：在筛选表A的某些行时，通过在条件中关联上另一个表B并添加一些限定/筛选逻辑，完成表A中行的选择。它通常会使用"限定"或者"限制"这样的语言，一般用于在细分的小组内进行细致化比较时，有一种对集合进行切分的作用（分组）。
下面通过一个例子感受下：有两个班的学生成绩表(score)如下，请筛选出高于各自班平均分的每班的学生。
+----------+------------+-------+
| clazz_id | student_id | score |
+----------+------------+-------+
| 1 | 1 | 98 |
| 1 | 2 | 82 |
| 1 | 3 | 90 |
| 2 | 4 | 94 |
| 2 | 5 | 76 |
| 2 | 6 | 85 |
+----------+------------+-------+使用关联子查询：check每个学生是否符合条件时，都要与该学生所属班内的学生的平均分进行比较。
SQL语句：
select clazz_id,student_id,score
from score AS A
WHERE score >
(SELECT avg(B.score)
FROM score B
WHERE A.clazz_id = B.clazz_id)结果:
+----------+------------+-------+
| clazz_id | student_id | score |
+----------+------------+-------+
| 1 | 1 | 98 |
| 2 | 4 | 94 |
+----------+------------+-------+4、联结(JOIN)
联结（JOIN）运算，简单来说，就是将其他表中的列添加过来，进行“添加列”的运算。该操作通常用于无法从一张表中获取期望数据（列）的情况。
常见的几种联结(连接)运算如下：
- 内连接：(INNER) JOIN，返回两张表都匹配上的行。
- 左连接：LEFT JOIN，返回左表的全部行，左表是主表，如果右表没有匹配的行，则右表字段用NULL代替。
- 右连接：RIGHT JOIN，返回右表的全部行，右表是主表，如果左表没有匹配的行，则左表字段用NULL代替。
- 全连接：FULL JOIN，返回左表和右表中的所有行，任一方没有另一方的匹配值，都用NULL代替。
- 交叉连接(笛卡尔积)：CROSS JOIN，返回左表中的所有行，而且左表中的每一行与右表中的所有行组合。
5、自连接
一般来说，连接操作大都是以不同的表或视图为对象进行的，但针对相同的表或相同的视图的连接也并没有被禁止。这种针对相同的表进行的连接被称为“自连接”（self join）。
举个例子：使用自连接的方法，重写上面关联子查询的SQL，即求出每班大于该班平均分的学生。
SELECT min(A.clazz_id),
A.student_id,
min(A.score)
FROM score AS A,
score AS B
WHERE A.clazz_id = B.clazz_id
GROUP BY A.student_id HAVING min(A.score) > avg(B.score)可以看到这是两个相同的score表进行连接，可以把自连接理解成两个相同的集合的连接。
一般来说，关联子查询和自连接是可以等价替换的，即能用自连接写的SQL，也能用关联子查询写出来。在后面的实战篇会有很多这样的例子。
还有一点就是，如果连接条件中A.clazz_id=B.clazz_id，用的不是等号连接，而是“<、>、<>”这种不等号，这种连接叫做非等值连接，这种连接方式在实际中也常常使用。
二、实战篇
1、排列与组合
eg1. 假设有一张存放了商品名称及价格的表，表里有“苹果、橘子、香蕉”这 3 条记录。现在我们需要获取其中两种不同商品的组合，有几种组合方法呢？
【排列问题】
排列问题，简单理解就是考虑物品的次序，比如说，<苹果，香蕉>与<香蕉，苹果>是两个不同的结果，即考虑他们出现的顺序。
SQL参考：用于获取排列的 SQL 语句
SELECT P1.name AS name_1,
P2.name AS name_2
FROM Products P1,
Products P2
WHERE P1.name <> P2.name;结果：
name_1 name_2
------ ------
苹果 橘子
苹果 香蕉
橘子 苹果
橘子 香蕉
香蕉 苹果
香蕉 橘子【组合问题】
组合问题，就是不考虑物品的次序，将<苹果，香蕉>与<香蕉，苹果>视作同一个结果，忽略他们出现的顺序。
SQL参考：用于获取组合的 SQL 语句
SELECT P1.name AS name_1,
P2.name AS name_2
FROM Products P1,
Products P2
WHERE P1.name > P2.name;name_1 name_2
------ ------
苹果 橘子
香蕉 橘子
香蕉 苹果【扩展】：获取三种不同商品的组合，忽略次序
SELECT P1.name AS name_1,
P2.name AS name_2,
P3.name AS name_3
FROM Products P1,
Products P2,
Products P3
WHERE P1.name > P2.name
AND P2.name > P3.name;结果：
name_1 name_2 name_3
------- -------- --------
香蕉 苹果 橘子解析：
这个例子是自连接的简单应用，用于将表中某一列的item进行排列和组合。在实现的过程中，自连接可以理解为，在具有相同数据元素的集合(表)之间进行连接。这里的自连接条件采用了非等值连接， WHERE P1.name <> P2.name ，它能排除掉由相同元素构成的pair，比较<苹果，苹果>。如果连接条件变为 WHERE P1.name > P2.name ，结果中还会将排列问题转化为组合问题（删掉重复出现的组合）。
2、根据局部列比较行与行
eg2: 请从下面这张商品表里找出价格相等的商品的组合。
SQL参考：
SELECT DISTINCT P1.name,
P1.price
FROM Products P1,
Products P2
WHERE P1.price = P2.price
AND P1.name <> P2.name;结果：
name price
------ ------
苹果 50
葡萄 50
草莓 100
橘子 100
香蕉 100解析：
在这个例子中，Products P1 自连接 Products P2，连接条件是，价格相同但名称不同的物品，那么，连接后的结果会有4列，最终结果我们只筛选两列，当然会有很多重复的情况，需要用distinct去重。
3、排名次序
除了窗口函数外，我们也可以使用通用的通用的SQL语句来输出排名次序，包括组内排名次序。
eg3-1: 请按照价格从高到低的顺序，对下面这张表里的商品进行排序。
SQL参考：
SELECT P1.name,
P1.price,
(SELECT COUNT(P2.price)
FROM Products P2
WHERE P2.price > P1.price) + 1 AS rank_1
FROM Products P1
ORDER BY rank_1;结果：
name price rank_1 rank_2
------- ------ ------- -------
橘子 100 1 1
西瓜 80 2 2
苹果 50 3 3
香蕉 50 3 3
葡萄 50 3 3
柠檬 30 6 4解析：
这段SQL通过使用标量子查询实现位次排序，排序方法看起来很普通，但很容易扩展。例如，去掉标量子查询后边的 +1，就可以从 0 开始给商品排序，而且如果修改成COUNT(DISTINCT P2.price)，那么存在相同位次的记录时，就可以不跳过之后的位次，而是连续输出（相当于窗口函数中的 DENSE_RANK 函数）。
【扩展-1】使用自连接输出排名次序
SELECT P1.name,
MAX(P1.price) AS price,
COUNT(P2.name) +1 AS rank_1
FROM Products P1
LEFT OUTER JOIN Products P2 ON P1.price < P2.price
GROUP BY P1.name
ORDER BY rank_1;【扩展-2】组内排名
eg3-2: 有如下地区-商品-价格表，请安装价格从高低的次序，分别统计每个地区的商品排名，输出分组排序结果。
SQL参考：
（1）使用自连接
SELECT P1.district,
P1.name,
MAX(P1.price) AS price,
COUNT(P2.name) +1 AS rank_1
FROM DistrictProducts P1
LEFT OUTER JOIN DistrictProducts P2 ON P1.district = P2.district /* 使用左连接是为了防止第一名漏掉 */
AND P1.price < P2.price
GROUP BY P1.district,
P1.name;（2）使用关联子查询
SELECT P1.district,
P1.name,
P1.price,
(SELECT COUNT(P2.price)
FROM DistrictProducts P2
WHERE P1.district = P2.district /* 在同一个地区内进行比较 */
AND P2.price > P1.price) + 1 AS rank_1
FROM DistrictProducts P1;结果：
4、时间序列比较：增长、减少、与维持现状
eg4: 假设有一张年营业额明细表，但部分年份存在数据缺失。请查询每一年与过去最临近的年份之间的营业额之差。
SQL参考：
-- （1）使用自内连接，结果里不包含最早的年份
SELECT S2.year AS pre_year,
S1.year AS now_year,
S2.sale AS pre_sale,
S1.sale AS now_sale,
S1.sale - S2.sale AS diff
FROM Sales2 S1,
Sales2 S2
WHERE S2.year =
(SELECT MAX(YEAR)
FROM Sales2 S3
WHERE S1.YEAR > S3.YEAR)
ORDER BY now_year;结果：
解析：这段SQL同时使用了自连接和关联子查询，子查询用于筛选距离now_year最近的年份，并将其用于自连接的连接条件，非常巧妙。从执行结果可以发现，这条 SQL 语句无法获取到最早年份 1990 年的数据。这是因为，表里没有比 1990 年更早的年份，所以在进行内连接的时候 1990 年的数据就被排除掉了。如果想让结果里出现 1990 年的数据，可以使用“自左外连接”来实现。
-- (2)：使用自左外连接，结果里包含最早的年份
SELECT S2.year AS pre_year,
S1.year AS now_year,
S2.sale AS pre_sale,
S1.sale AS now_sale,
S1.sale - S2.sale AS diff
FROM Sales2 S1
LEFT OUTER JOIN Sales2 S2 ON S2.year =
(SELECT MAX(YEAR)
FROM Sales2 S3
WHERE S1.YEAR > S3.YEAR)
ORDER BY now_year;结果：
5、滑动窗口内统计
除使用窗口函数外，也可以使用更加通用的SQL标准语法，来实现滑动窗口内统计值的计算。
eg5: 有下面有一张银行账户存取款历史记录表 Accounts，处理金额为正数代表存钱，为负数代表取钱。现请依次求出截止到每个处理日期的处理金额的累计值（实际上就是当时的账户余额）。
SQL参考：
SELECT prc_date,
A1.prc_amt,
(SELECT SUM(prc_amt)
FROM Accounts A2
WHERE A1.prc_date >= A2.prc_date) AS onhand_amt
FROM Accounts A1
ORDER BY prc_date;结果：
解析：
相信聪明的读者已经发现了，这里统计累积值的方法，其实与上面计算位次的方法属于同种类型的查询语句。这里只是将 COUNT 替换成了 SUM 而已。
而本小节要讲的是，如何在固定长度的移动窗口内求出统计值。结合本题，请思考如何以3为窗口函数单位，求出窗口内的累计值。
参考SQL：
SELECT prc_date,
A1.prc_amt,
(SELECT SUM(prc_amt)
FROM Accounts A2
WHERE A1.prc_date >= A2.prc_date
AND
(SELECT COUNT(*)
FROM Accounts A3
WHERE A3.prc_date BETWEEN A2.prc_date AND A1.prc_date) <= 3) AS mvg_sum
FROM Accounts A1
ORDER BY prc_date;结果：
三、总结
自连接是不亚于 CASE 表达式的重要技术，关联子查询也是一种非常强大的运算，请大家一定熟练掌握。
说一个需要注意的地方，与多表之间进行的普通连接相比，自连接的性能开销更大（特别是与非等值连接结合使用的时候），因此用于自连接的列推荐使用主键或者在相关列上建立索引。
总结以下几点：
- 将自连接看作不同表之间的连接更容易理解。
- 应把表看作行的集合，用面向集合的方法来思考。
- 自连接经常和非等值连接结合起来使用。
- 自连接的性能开销更大，应尽量给用于连接的列建立索引。
- 使用关联子查询的SQL，往往也可以使用自连接的方式。
- 关联子查询的代码的可读性不好，而且性能也不好，特别是在 SELECT 子句里使用标量子查询时，性能可能会变差。
- 很多工具强大的同时，也有与其能力相当的缺点，希望大家运用智慧合理地利用它。