CTE 的全称是 Common Table Expressions，有时候也缩写为 CTEs，在 SQL 标准中使用 WITH clause表达[1]。在 ANSI standard 标准的 SQL:1999 中定义，参考：ISO/IEC 9075-2:1999[7][8]。该特性支持在 MySQL 8.0 中正式被引入[9]。
通常，如果不需要编写特别复杂的 SQL ，更为具体的，如果无需编写带有递归功能的 SQL 时，CTE 通常都不是必须的。所以，该语法功能，对于分析型的场景或者数据开发等场景更为常用。
这里概述 CTE 的两个常用功能：简化子查询结构、实现递归执行。其中，实现递归执行，是 CTE 独特的能力，是对 SQL 能力的一种补充。
简化子查询结构
CTE 功能提供了一种新的子查询或临时表的写法。可以把后续 SQL 中需要使用的临时表或子查询独立的放在WITH子句中，使得结构看起来更为清晰一些。
例如，如下两个写法在语义上是等价的：
WITH
t_mid as (
SELECT * FROM t_1 JOIN t_2
)
SELECT * FROM t_mid;
SELECT *
FROM
(
SELECT * FROM t_1 JOIN t_2
) t_mid;
递归执行
该功能是 CTE 的核心能力。例如，在表中查询好友的好友、查询主管的主管、递归数据生成等功能。
递归执行方式概述
在各个数据库的关于 CTE 介绍都会较为详细的描述，这里对该执行过程做一个简单的描述。
对于递归 CTE 对应的 SQL 总是有类似结构：
WITH RECURSIVE table_cte (col_01 ... ) AS (
SELECT ... -- return initial row set
UNION ALL
SELECT ... FROM table_cte -- return additional row sets
)
在该结构中：
- 第一个
SELECT为数据初始化
- 第二个
SELECT则为递归执行的部分，该部分在引用表table_cte时，总是使用该表的最后一条生成记录
斐波那契数列的生成
这是一个MySQL文档中的示例[10]。
WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
SELECT 1, 0, 1
UNION ALL
SELECT n + 1, next_fib_n, fib_n + next_fib_n
FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;
+------+-------+------------+
| n | fib_n | next_fib_n |
+------+-------+------------+
| 1 | 0 | 1 |
| 2 | 1 | 1 |
| 3 | 1 | 2 |
| 4 | 2 | 3 |
| 5 | 3 | 5 |
...
| 10 | 34 | 55 |
+------+-------+------------+
根据前面小结“递归执行方式概述”中所描述，在每次执行上述的第二个 SELECT，即SELECT n + 1, next_fib_n, fib_n + next_fib_n FROM fibonacci时，总是使用 CTE 表fibonacci最后生成的记录。所以，该SQL中引用的列n 、fib_n和next_fib_n均为表fibonacci最后生成的记录，即上一条记录。
所以，该 SQL 第一列返回的是一个自增的序列；第二列，即为斐波那契数列；第三列是用于计算的中间列。
广度优先还是深度优先
在 MySQL 的递归遍历中，并没有像 PostgreSQL 那样可以通过 SQL 关键字去控制。在文档中，也没有描述是广度优先还是深度优先。这里通过一些 SQL 变量来看看，到底是广度优先还是深度优先。
先说结论吧：MySQL 使用的是广度优先遍历。关注验证过程的，可以继续阅读这一段的示例和解读。
这里使用 MySQL 官方文档中另一个示例。该示例包含一个企业人员组织架构的关系：
CREATE TABLE employees (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
manager_id INT NULL,
INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333), # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);
我们可以使用如下的递归 CTE 语句进行组织架构遍历：
WITH RECURSIVE employee_paths (id, name, path) AS
(
SELECT id, name, CAST(id AS CHAR(200))
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
FROM employee_paths AS ep JOIN employees AS e
ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;
我们对这条 SQL 语句进行如下修改，以观察是广度优先还是深度优先：
WITH RECURSIVE employee_paths (id, name, path,seq) AS
(
SELECT id, name, CAST(id AS CHAR(200)),@seq:=1
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, CONCAT(ep.path, ',', e.id),@seq:=@seq+1
FROM employee_paths AS ep JOIN employees AS e
ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY seq;
+------+---------+-----------------+------+
| id | name | path | seq |
+------+---------+-----------------+------+
| 333 | Yasmina | 333 | 1 |
| 198 | John | 333,198 | 2 |
| 692 | Tarek | 333,692 | 3 |
| 29 | Pedro | 333,198,29 | 4 |
| 123 | Adil | 333,692,123 | 5 |
| 72 | Pierre | 333,198,29,72 | 6 |
| 4610 | Sarah | 333,198,29,4610 | 7 |
+------+---------+-----------------+------+
该结果的最后一列 seq，反映了输出的先后顺序。可以看到，这里使用的广度优先遍历。
参考链接
