CTE（Common Table Expression）就像是可以从中查询数据的表。CTE 有一些特性：

有名字
存在于单个语句的作用域中
可以被多次引用
可以引用自己（递归）或其它 CTE

这是一个最简单的 CTE：

mysql> WITH cte AS (SELECT 1)
       SELECT * FROM cte;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.02 sec)

在这个例子中，WITH 从句定义了一个 CTE，它的名字是 cte，它有一列数据，列名是 1，它有一行记录，这行记录中唯一的值是 1。随后的 top-level SELECT 语句中引用了 cte。

Common Table Expressions

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

要定义 CTE，需要使用 WITH从句。在 WITH从句中，使用逗号分隔多个子从句，每个子从句定义一个 CTE。在每个子从句中，指定该 CTE 的名字（cte_name)，指定每列的名字（col_name 或其它方式），指定该 CTE 的子查询（AS (subquery)），为其生成一些数据。

在下面这个例子中，WITH 从句中定义了两个 CTE cte1 和 cte2，top-level SELECT 语句中将 cte1、cte2、衍生表 dt 做了 JOIN 操作：

mysql> WITH
    cte1 (a, b) AS (SELECT 1, 3),
    cte2 (c, d) AS (SELECT 1, 2)
SELECT * FROM cte1 JOIN cte2
    ON cte1.a = cte2.c
JOIN (SELECT 1 AS e, 5 AS f) AS dt
    ON cte1.a = dt.e;
+---+---+---+---+---+---+
| a | b | c | d | e | f |
+---+---+---+---+---+---+
| 1 | 3 | 1 | 2 | 1 | 5 |
+---+---+---+---+---+---+

一个 CTE 可以引用它自己或者其它 CTE：

一个 CTE 可以引用它自己，定义一个递归的 CTE。
在一个 WITH 子句中，后定义的 CTE 可以引用在它之前定义的 CTE。
CTE 可以引用定义在更外层的 CTE，可以引用同级的 CTE，但不能引用较内层的 CTE。

在下面这个例子中，WITH 从句中定义了两个 CTE cte1 和 cte2，cte2 引用了在它之前定义的 cte1：

mysql> WITH 
    cte1 (a, b) AS (SELECT 1, 3),
    cte2 AS (SELECT * FROM cte1)
SELECT * FROM cte2;
+---+---+
| a | b |
+---+---+
| 1 | 3 |
+---+---+

Recursive Common Table Expressions

当一个 CTE 引用它自己时，就定义了一个递归的 CTE。

递归 CTE 子查询：

SELECT ...      -- return initial row set
UNION {ALL | DISTINCT}
SELECT ...      -- return additional row sets

一个递归的 CTE 由一个非递归的 SELECT 部分和一个递归的 SELECT 部分组成。两部分由 UNION ALL 或者 UNION DISTINCT 分开。非递归的 SELECT 部分只执行一次，产生最初的记录。第一次迭代，递归的 SELECT 部分根据最初的记录生成新的记录，第二次迭代，递归的 SELECT 部分根据第一次迭代产生的记录产生新的记录，依次类推。当递归的 SELECT 部分未产生新的记录时，递归结束。

---
title: recursive CTE subquery
---
flowchart LR
    Start([Start]) --> N[nonrecursive SELECT part]
    N --> R[recursive SELECT part]
    R --> NewRows{produces new rows ?}
    NewRows -- no --> E([End])
    NewRows -- yes --> R

在这个官方给出的示例中：

mysql> WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+

CTE cte 由非递归的 SELECT 1 和递归的 SELECT n + 1 FROM cte WHERE n < 5 组成，这两部分以 UNION ALL 隔开。以下是每次迭代的结果：

iteration	result
initial	1
1	2
2	3
3	4
4	5

示例、语法限制、注意事项

官方文档为递归 CTE 提供了丰富的例子。

CTE 在使用时，有一些需要注意的地方，这里无法详尽说明，比如列名的确定、WITH 从句可以出现的上下文、在同一个层级中只能出现一个 WITH 从句、对象的名字解析、CTE 中列的类型的推断、如何解决列的类型推断产生的问题、递归 CTE 的语法限制、EXPLAIN 递归 CTE、如何限制递归、CTE 相对于衍生表的优势。

dushaoshuai / dushaoshuai.github.io

MySQL: CTE #84

Common Table Expressions

Recursive Common Table Expressions

示例、语法限制、注意事项