要指定常见表达式，请使用WITH子句，该子句包含一个或多个逗号分隔的子句。每个子句都提供一个子查询，该子查询生成结果集，并将子查询与名称关联。以下示例定义了名为 cte1 和 cte2 的 CTE，在WITH子句中，并在后续的SELECT语句中引用它们：

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

在包含WITH子句的语句中，每个 CTE 名称都可以引用相应的 CTE 结果集。

CTE 名称可以在其他 CTE 中引用，以便定义基于其他 CTE 的 CTE。

CTE 可以引用自己以定义递归 CTE。常见的递归 CTE 应用场景包括系列生成和对层次结构或树形数据的遍历。

常见表达式是语句语法的可选部分。它们使用WITH子句定义：

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

cte_name命名单个常见表达式，并且可以在包含WITH子句的语句中引用该表达式。

《子查询》部分的AS (子查询)称为“CTE的子查询”，它生产CTE结果集。代码AS后的括号是必需的。

常规表达式是递归的，如果其子查询引用自己的名称。代码RECURSIVE关键字必须包括，如果CTE中的任何CTE是递归的。更多信息，请参见递归常规表达式。

确定CTE的列名如下：

WITH子句在以下上下文中允许：

只能在同一个级别上出现一个WITH子句。代码WITH后面跟随WITH不允许在同一个级别上出现，所以这不是合法的：

WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...

要使语句合法，使用单个WITH子句，使用逗号分隔子句：

WITH cte1 AS (...), cte2 AS (...) SELECT ...

然而，语句可以包含多个WITH子句，如果它们出现在不同的级别上：

WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

WITH子句可以定义一个或多个常规表达式，但是每个CTE名称都必须在该子句中唯一。这不是合法的：

WITH cte1 AS (...), cte1 AS (...) SELECT ...

要使语句合法，定义CTE的唯一名称：

WITH cte1 AS (...), cte2 AS (...) SELECT ...

CTE可以引用自己或其他CTE：

在解决对象名称的引用时，派生表隐藏CTE；CTE隐藏基本表、TEMPORARY表和视图。名称解析是从同一个查询块中搜索对象，然后顺序地搜索外部块直到找到对象名称为止。

有关递归CTE的特定语法考虑，请见Recursive Common Table Expressions。

递归CTE 是指具有子查询引用自己的名称的CTE。例如：

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

CTE子查询还具有以下递归部分：

SELECT n + 1 FROM cte WHERE n < 5

在每个迭代中，SELECT 生成一行，其中的新值是前一个行集中的值加1。第一个迭代操作初始行集（1）并生成1+1=2；第二个迭代操作第一个迭代的行集（2）并生成2+1=3；以此类推。直到递归结束，这发生在n不再小于5时。

如果递归部分的CTE生成的列值比非递归部分更宽，可能需要在非递归部分中扩展该列以避免数据截断。考虑以下语句：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

在非严格SQL模式下，该语句生成以下输出：

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

所有str列值都是'abc'，因为非递归SELECT确定列宽。因此，递归SELECT生成的更宽的str值将被截断。

在严格SQL模式下，该语句生成错误：

ERROR 1406 (22001): Data too long for column 'str' at row 1

为了解决这个问题，使语句不生成截断或错误，请在非递归SELECT中使用CAST()以使str列更宽：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

现在语句生成以下结果，无截断：

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

可以通过名称访问列，而不是位置，这意味着递归部分可以访问非递归部分的列，即使它们的位置不同，如CTE示例所示：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 1 AS p, -1 AS q
  UNION ALL
  SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;

因为p在一行中是从前一个行中的q派生出来的，反之亦然，在每个连续行的输出中，正负值的顺序会交换：

+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+

在递归CTE子查询中有一些语法约束：

这些约束来自SQL标准，除了MySQL中提到的特定排除。

对于递归CTE，EXPLAIN输出行显示SELECT部分的Extra列中的Recursive。

EXPLAIN显示的成本估算表示每次迭代的成本，这可能与总成本不同。优化器无法预测迭代次数，因为它无法预测WHERE子句何时变为假。

CTE实际成本也可能受到结果集大小的影响。CTE生成许多行可能需要内部临时表转换为磁盘格式，并且可能会受到性能 penalty。如果是这样，增加允许的内存临时表大小可能会改善性能；请参见Section 10.4.4, “MySQL 中的内部临时表使用”。

对于递归CTE，重要的是递归SELECT部分包括终止递归的条件。作为开发技术来防止递归CTE的运行，可以强制终止执行：

假设一个递归CTE错误地没有写入递归执行终止条件：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT * FROM cte;

默认情况下，cte_max_recursion_depth 的值为1000，导致CTE在递归超过1000级时终止。应用程序可以更改会话值以适应其需求：

SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

您也可以将全局cte_max_recursion_depth 值设置，以影响所有随后开始的会话。

对于执行时间较长或在某些情况下需要设置cte_max_recursion_depth 值非常高的查询，可以使用会话超时来保护深度递归。要做到这一点，请在执行CTE语句之前执行以下语句：

SET max_execution_time = 1000; -- impose one second timeout

或者，您可以在CTE语句本身中包含优化提示：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

您也可以使用 LIMIT 在递归查询中来限制返回到最外层SELECT 的最大行数，例如：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;

您可以在设置超时限制的同时或代替设置超时限制。因此，以下CTE在返回十万行或运行一秒（1000毫秒）后终止， whichever occurs first：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

如果一个递归查询没有执行时间限制进入无限循环，您可以在另一个会话中使用KILL QUERY 终止它。从会话本身，客户端程序可能提供了终止查询的方式。例如，在mysql 中，输入Control+C 可以中断当前语句。

如前所述，递归常见表达式（CTE）常用于生成系列和遍历树状或层次结构数据。这部分展示了一些简单的示例这些技术。

斐波那契数列从两个数字0和1开始（或1和1），每个数字都是前两个数字的和。使用递归通用表达式可以生成斐波那契数列，如果每个由递归SELECT产生的行都可以访问前两个数字。以下CTE使用0和1作为第一个两个数字生成一个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;

CTE的结果如下：

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+

CTE的工作原理：

前面的输出显示了整个CTE结果。要选择其中的一部分，添加适当的WHERE子句到顶级SELECT。例如，要选择第8个斐波那契数字，做如下操作：

mysql> WITH RECURSIVE fibonacci ...
       ...
       SELECT fib_n FROM fibonacci WHERE n = 8;
+-------+
| fib_n |
+-------+
|    13 |
+-------+

通用表达式可以生成一系列连续日期，这对于生成包含所有日期的摘要非常有用，其中包括日期不在摘要数据中的日期。

假设一个销售数据表包含以下行：

mysql> SELECT * FROM sales ORDER BY date, price;
+------------+--------+
| date       | price  |
+------------+--------+
| 2017-01-03 | 100.00 |
| 2017-01-03 | 200.00 |
| 2017-01-06 |  50.00 |
| 2017-01-08 |  10.00 |
| 2017-01-08 |  20.00 |
| 2017-01-08 | 150.00 |
| 2017-01-10 |   5.00 |
+------------+--------+

这个查询总结了每天的销售额：

mysql> SELECT date, SUM(price) AS sum_price
       FROM sales
       GROUP BY date
       ORDER BY date;
+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-06 |     50.00 |
| 2017-01-08 |    180.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+

然而，这个结果包含了“holes”，即日期不在销售数据中的日期。使用递归CTE生成日期系列，可以与销售数据进行LEFT JOIN，生成包含所有日期的结果。

以下是生成日期系列CTE的代码：

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT * FROM dates;

CTE的结果如下：

+------------+
| date       |
+------------+
| 2017-01-03 |
| 2017-01-04 |
| 2017-01-05 |
| 2017-01-06 |
| 2017-01-07 |
| 2017-01-08 |
| 2017-01-09 |
| 2017-01-10 |
+------------+

CTE的工作原理：

将CTE与LEFT JOIN对销售数据表进行连接，生成了每天的销售摘要：

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
FROM dates LEFT JOIN sales ON dates.date = sales.date
GROUP BY dates.date
ORDER BY dates.date;

输出结果如下：

+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-04 |      0.00 |
| 2017-01-05 |      0.00 |
| 2017-01-06 |     50.00 |
| 2017-01-07 |      0.00 |
| 2017-01-08 |    180.00 |
| 2017-01-09 |      0.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+

需要注意的一些点：

递归常规表达式非常有用，可以遍历形成层次结构的数据。考虑这些语句，创建一个小数据集，显示每个员工在公司中的员工名称和ID号，以及员工的经理ID号。公司的CEO（总裁）没有经理ID（NULL）。

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);

结果数据集如下：

mysql> SELECT * FROM employees ORDER BY id;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  123 | Adil    |        692 |
|  198 | John    |        333 |
|  333 | Yasmina |       NULL |
|  692 | Tarek   |        333 |
| 4610 | Sarah   |         29 |
+------+---------+------------+

为了生成员工的组织结构（即从CEO到员工），使用递归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;

CTE产生的输出：

+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+

CTE是如何工作的：

要找到特定员工或员工的路径，添加WHERE子句到顶级SELECT。例如，要显示Tarek和Sarah的结果，修改该SELECT如下：

mysql> WITH RECURSIVE ...
       ...
       SELECT * FROM employees_extended
       WHERE id IN (692, 4610)
       ORDER BY path;
+------+-------+-----------------+
| id   | name  | path            |
+------+-------+-----------------+
| 4610 | Sarah | 333,198,29,4610 |
|  692 | Tarek | 333,692         |
+------+-------+-----------------+

Common table 表达式（CTE）与某些构造相似：

由于这些相似之处，CTE和派生表可以互换使用。例如，这些语句是等价的：

WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;

然而，CTE相比派生表有以下优势：

CTE 类似于使用 CREATE [TEMPORARY] TABLE 创建的表，但不需要定义或删除。为 CTE，您不需要创建表的权限。