全部产品
Search
文档中心

云原生数据库 PolarDB:CTE

更新时间:Jun 21, 2023

本文介绍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。