公用表表达式（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 |
+------+-------+------------+

结语

推荐下我的开源作品 PiperChat
PiperChat 是一款简洁高效的即时通讯服务，提供多种技术供开发者选择，帮助开发者快速构建高并发的即时通讯服务。
帮助企业快速接入 IM系统 / 聊天室系统 / 客服系统 等其他实时消息类系统，如果对你有用请求给我一个Star呀!