通用表表达式

通用表表达式（Common Table Expressions，CTE）是一个命名的临时结果集，作用范围是当前语句，不实际作为对象存储，仅在查询执行期间被使用。OceanBase 数据库支持非递归的 CTE 和递归的 CTE。

CTE 语法

通用表表达式是 DML 语句语法的可选部分，使用 WITH 子句定义。如果 WITH 子句包含多个子句可以使用逗号分隔。每个子句提供一个子查询以产生结果集，并将子查询与名称相关联。语法如下：

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

参数解释如下：

• cte_name 用于命名通用表表达式，可被包含 WITH 子句的的表引用。

• AS(subquery) 部分被称为"CTE 子查询"，用于生成 CTE 结果集。AS 后面必须带括号。

如果 CTE 子查询引用其自己的名称，则通用表表达式是递归的。如果 WITH 子句中的 CTE 都是递归的，则必须包含 RECURSIVE 关键字。

以下示例中，在 WITH 子句中定义名为 cte1 和 cte2 的通用表表达式，并在 WITH 子句后面的顶层 SELECT 语句中引用：

WITH
   cte1 AS (SELECT col1, col2 FROM tbl1),
   cte2 AS (SELECT col3, col4 FROM tbl2)
SELECT col2, col4 FROM cte1 JOIN cte2
WHERE cte1.col1 = cte2.col3;

如果 CTE 名称后面有一个带括号的名称列表，则这些名称是列名称，列表中的名称数必须与结果集中的列数相同。否则，列名来自 AS(subquery) 部分中第一个 Select List。

WITH 子句适用场景

在以下场景允许使用 WITH 子句：

• 在 SELECT 语句的开头。

  WITH ... SELECT ...
  

• 在子查询（包括派生表子查询）的开头。

  SELECT ... WHERE id IN (WITH ... SELECT ...) ...
  SELECT * FROM (WITH ... SELECT ...) AS dt ...
  

• 对于包含 SELECT 语句的语句，紧接在 SELECT 之前。

  INSERT ... WITH ... SELECT ...
  REPLACE ... WITH ... SELECT ...
  CREATE TABLE ... WITH ... SELECT ...
  CREATE VIEW ... WITH ... SELECT ...
  

同一级别只允许一个 WITH 子句。如果 WITH 子句包含多个子句可以使用逗号分隔。

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

WITH 子句可以定义一个或多个通用表表达式，但每个 CTE 名称对于该子句必须是唯一的。以下示例是非法的：

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

递归 CTE

递归通用表表达式是指具有引用其自身名称的子查询的表达式。

递归 CTE 的结构

递归 CTE 具有以下结构：

• 如果 WITH 子句中的 CTE 引用自身，则 WITH 子句必须以 WITH RECURSIVE 开头。否则不要求必须带 RECURSIVE。

• 递归 CTE 子查询有两部分，由 UNION [ALL] 或 UNION DISTINCT 分隔：

  SELECT ...      -- 返回初始行集
  UNION ALL
  SELECT ...      -- 返回额外的行集
  

  第一个 SELECT 为 CTE 生成一个或多个初始行，并且不引用 CTE 名称。第二个 SELECT 产生额外的行并通过在其 FROM 子句中引用 CTE 名称进行递归。当这部分不产生新行时，递归结束。 因此，递归 CTE 由一个非递归 SELECT 部分和一个递归 SELECT 部分组成。每个 SELECT 部分可以是多个 SELECT 语句的联合。

• CTE 结果列的类型仅从非递归 SELECT 部分的列类型中推断出来，并且所有列都可以为空。类型的确定会忽略递归 SELECT 部分。如果非递归和递归部分由 UNION DISTINCT 分隔，则消除重复行。这可以避免执行传递闭包的查询时产生无限循环。

• 递归部分的每次迭代仅对前一次迭代产生的行进行操作。如果递归部分有多个查询块，则每个查询块的迭代按未指定的顺序进行调度，并且每个查询块操作的行是其上一次迭代或自上次迭代结束后由其他查询块生成的行。

示例如下：

WITH RECURSIVE cte1 (n) AS
(
  SELECT 1                /*非递归部分，它检索单个行以生成初始行集*/
  UNION ALL
  SELECT n + 2 FROM cte1 WHERE n < 10     /*递归部分，生成一个比前一行集中的 n 值大 2 的新值，直至 n 不小于 10*/
)
SELECT * FROM cte1;

适用条件

以下语法约束适用于递归 CTE 子查询：

• 递归 SELECT 部分不得包含以下结构：

  • 聚合函数，例如 SUM()

  • 窗口函数

  • GROUP BY

  • ORDER BY

  • DISTINCT

• 递归 SELECT 部分必须仅在其 FROM 子句中的子查询中引用一次。它可以引用 CTE 以外的表并与 CTE 进行连接。如果在这样的连接中使用，则 CTE 不得位于 LEFT JOIN 的右侧。

对于递归 CTE，EXPLAIN 显示的成本估算代表每次迭代的成本，可能与总成本有很大不同。优化器无法预测迭代次数，因为它无法预测 WHERE 子句在什么时候变为 False。