mysql8 WITH AS（公用表表达式）

公用表表达式（CTE）是一个命名的临时结果集，它存在于单个语句的范围内，以后可以在该语句中引用，可能多次。以下讨论描述了如何编写使用CTE的语句。

常用表表达式

要指定公用表表达式，请使用 WITH具有一个或多个逗号分隔子句的子句。每个子句都提供一个子查询，该子查询产生一个结果集，并将一个名称与该子查询相关联。下面的示例定义名为的CTE cte1和cte2中 WITH子句，并且是指在它们的顶层SELECT下面的WITH子句：

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;

确定给定CTE的列名的过程如下：
如果带括号的名称列表位于CTE名称之后，则这些名称为列名称：

WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

否则，列名来自首的选择列表中SELECT的内部分： AS (subquery)

WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

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 RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

限制公用表表达式递归

对于递归CTE，重要的是递归 SELECT部分包括终止递归的条件。作为一种防止递归CTE失控的开发技术，您可以通过限制执行时间来强制终止：

该cte_max_recursion_depth 系统变量强制执行递归级别的热膨胀系数为数量限制。服务器终止任何递归级别高于此变量值的CTE的执行。
所述max_execution_time 系统变量强制用于执行超时 SELECT在当前会话中执行的语句。
该MAX_EXECUTION_TIME 优化器提示强制为每个查询执行超时SELECT在它出现的语句。

SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion
SET max_execution_time = 1000; -- impose one second timeout

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM 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 |
+------+-------+------------+