CTE

功能介绍

CTE（Common Table Expressions）表示公用表表达式，是一个临时命名结果集，用于简化SQL。是一个statement级别的子句表达式，以WITH开头，后跟表达式名称。包括以下两类：

• Recursive CTE表示CTE可以迭代访问自身的场景，能够实现SQL的递归查询功能。

• Non Recursive CTE表示非递归CTE，即CTE不使用递归，不迭代访问自己。

语法

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

Recursive CTE

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

Non Recursive CTE

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;

约束

• recursive part不能包含聚合函数、窗口函数、GROUP BY、ORDER BY、DISTINCT；

• recursive part中访问CTE自身只能访问一次，并且只能在FROM子句访问，不可以在子查询中访问；

• recursive part子句中访问CTE时，CTE不可以在left join的右侧。

说明

• Recursive CTE子句必须以WITH RECURSIVE开头，否则会提示报错信息ERROR 1146 (42S02): Table 'cte_name' doesn't exist；

• 如果CTE没有指向自己形成迭代，RECURSIVE可以有但无效果；

• Recursive CTE包含两部分查询子句，通过UNION ALL或UNION连接：

  SELECT ...      -- non recursive part, return initial row set
  UNION [ALL]
  SELECT ...      -- recursive part, return additional row sets				

  其中第一个查询子句non recursive part负责生成初始数据，这个查询子句不能指向CTE自身。第二个查询子句生成附加行，并且会在FROM子句中访问CTE自身。迭代执行会在第二个查询子句recursive part无法查出新数据时停止；

• Recursive CTE的返回类型由non recursive part决定，并且全部为nullable；

• 每轮迭代时，recursive part引用自身的数据仅限上一次迭代产生的数据，而非之前产生的全部数据；

• 变量基于变量名和位置决定在每轮迭代中的赋值：

  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;

  如上所示，n、p、q变量在non recursive part赋值后，在recursive part中基于变量名去做赋值。recursive part执行完成以后，基于project中的位置对变量赋值。因此以上示例的输出为：

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

• 通过limit控制recursive次数，即recursive cte的union子句后面可以接limit。

参数说明

cte_max_recursion_depth：recursive part子句迭代执行次数的上限，默认值为500。