TDSQL Boundless CTE 语法使用说明

公共表表达式（Common Table Expressions，CTE）是 SQL 标准的重要组成部分，用于在单条 SQL 语句中定义可被多次引用的临时结果集。TDSQL Boundless 只读分析实例从早期版本起逐步支持 CTE 语法，并在最新版本中提供完整的递归 CTE 与流式执行能力。本文档介绍 TDSQL Boundless 只读分析实例对 CTE 的支持情况、语法结构、使用示例与使用限制。
CTE 简介
CTE 在 SQL:1999 标准中首次引入，通常通过 WITH 子句定义，因此也称为 WITH 子句。CTE 在单条 SQL 语句中定义临时结果集，并允许在该语句的后续部分多次引用，从而提高复杂查询的可读性和可维护性。
CTE 分为以下两类：
非递归 CTE（Non-Recursive CTE）：CTE 仅引用其他表或之前定义的 CTE，不引用自身，适用于将多步查询拆分为多个中间结果。
递归 CTE（Recursive CTE）：CTE 在定义中引用自身，适用于树形结构、图结构等具有层级关系的数据查询。
下列代码是一个最简单的 CTE 示例。
-- 定义 CTE。
WITH CustomerCTE AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
)
-- 引用 CTE。
SELECT *
FROM CustomerCTE;
支持情况
TDSQL Boundless 只读分析实例对 CTE 的支持情况如下表。
非递归 CTE
递归 CTE
是否需要 Hint /*+ MERGE() */
流式执行
支持
支持
不需要
支持
CTE 的优势
在复杂的 SQL 查询中，使用 CTE 具有以下优势。
简化查询：CTE 通过将复杂查询拆分为命名的中间结果集，提高 SQL 的组织性和可维护性，避免在多次引用相同子查询时出现重复代码。
提高可读性：CTE 使用具备业务含义的命名表示中间结果，使复杂 SQL 更易理解。
避免重复计算：在物化模式下，CTE 的中间结果可在同一条 SQL 中被多次引用，避免相同操作的重复执行。
支持递归查询：递归 CTE 能够处理层级结构数据（例如组织架构、目录树），简化树形数据查询逻辑。
语法结构
CTE 的基本语法结构如下。
with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
参数说明如下表。
参数项
描述
WITH
CTE 定义的开始关键字。
RECURSIVE
可选关键字。包含 RECURSIVE 时表示允许在 CTE 中引用自身，用于构造递归查询。
cte_name
CTE 的名称，可在后续查询中被引用。
col_name
可选的列名列表，用于指定 CTE 结果集的列名。如果省略，则使用子查询中的列名。
subquery
CTE 内部的子查询，定义 CTE 的内容。
多个 CTE
一个 WITH 子句中可定义多个 CTE，相互之间使用半角逗号分隔。
非递归 CTE
非递归 CTE 中，CTE 仅引用其他表或之前定义的 CTE，不会引用自身。它适用于将多步查询拆分为多个中间结果，通过逐层计算构建最终的查询结果。
非递归 CTE 的基本结构如下。
WITH
    cte1 AS (SELECT * FROM t1, t2),
    cte2 AS (SELECT i1, i2 FROM cte1 WHERE i3 > 10),
    cte3 AS (SELECT * FROM cte2, t3 WHERE cte2.i1 = t3.i1)
SELECT * FROM cte3;
单个 CTE
下列示例使用单个非递归 CTE 完成简单查询。
WITH CustomerCTE AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
)
SELECT *
FROM CustomerCTE;
多个 CTE
下列示例在同一个 WITH 子句中定义两个 CTE，并在最终查询中使用 JOIN 进行关联。
WITH
    CTE1 AS (
        SELECT customer_id, first_name, last_name, email_address
        FROM customer
    ),
    CTE2 AS (
        SELECT ss_item_sk, ss_customer_sk, ss_sold_date_sk, ss_sales_price
        FROM store_sales
    )
SELECT CTE1.first_name, CTE1.last_name, CTE2.ss_sales_price
FROM CTE1
JOIN CTE2 ON CTE1.customer_id = CTE2.ss_customer_sk;
该示例的执行逻辑如下：
定义 CTE1 和 CTE2 两个 CTE，分别从 customer 表和 store_sales 表读取数据。
在最终查询中将两个 CTE 通过 customer_id 字段进行 JOIN。
执行结果如下。
+------------+-----------+----------------+
﻿
| first_name | last_name | ss_sales_price |
﻿
+------------+-----------+----------------+
﻿
| John       | Doe       |          45.99 |
| Jane       | Smith     |          32.50 |
| Michael    | Johnson   |          78.25 |
| Emily      | Brown     |          19.99 |
| David      | Wilson    |          55.00 |
﻿
+------------+-----------+----------------+
5 rows in set (0.12 sec)
嵌套 CTE
下列示例使用三个 CTE 完成嵌套查询：先按客户汇总销售额，再筛选高消费客户，最后关联客户信息表。
WITH
    SalesSummary AS (
        SELECT ss_customer_sk, SUM(ss_net_paid) AS total_spent
        FROM store_sales
        GROUP BY ss_customer_sk
    ),
    TopCustomers AS (
        SELECT ss_customer_sk, total_spent
        FROM SalesSummary
        WHERE total_spent > 1000
    ),
    CustomerDetails AS (
        SELECT c.customer_id, c.first_name, c.last_name, tc.total_spent
        FROM customer c
        JOIN TopCustomers tc ON c.customer_id = tc.ss_customer_sk
    )
SELECT *
FROM CustomerDetails;
该示例的执行逻辑如下：
SalesSummary 计算每个客户的总消费金额。
TopCustomers 从 SalesSummary 中筛选出消费金额超过 1000 的客户。
CustomerDetails 将 customer 表的客户信息与 TopCustomers 进行关联。
最终查询从 CustomerDetails 中提取所有数据。
执行结果如下。
+-------------+------------+-----------+-------------+
﻿
| customer_id | first_name | last_name | total_spent |
﻿
+-------------+------------+-----------+-------------+
﻿
|        1001 | John       | Doe       |     1523.75 |
|        1002 | Jane       | Smith     |     2105.50 |
|        1003 | Michael    | Johnson   |     1789.99 |
|        1004 | Emily      | Brown     |     1650.25 |
|        1005 | David      | Wilson    |     1875.00 |
﻿
+-------------+------------+-----------+-------------+
5 rows in set (0.15 sec)
递归 CTE
递归 CTE 在定义中引用自身，适用于处理具有层级关系的数据查询，例如计算阶乘、生成序列、遍历树形结构等。
递归 CTE 由以下三部分组成：
种子部分（Seed Part Subquery）：递归的起点，不引用自身。
联合方式（Union Type）：使用 UNION ALL 或 UNION DISTINCT 连接种子部分与递归部分。
递归部分（Recursive Part Subquery）：必须引用自身，用于产生新行。
递归 CTE 的基本语法如下。
WITH RECURSIVE cte(n, fact) AS (
    SELECT 0, 1                                              -- 种子部分
    UNION ALL                                                -- 联合方式
    SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5        -- 递归部分
)
SELECT n, fact FROM cte;
注意：
递归 CTE 的递归深度由系统变量 cte_max_recursion_depth 控制，默认值为1000。
当递归次数超过 cte_max_recursion_depth 时，查询返回错误：Recursive query aborted after N iterations. Try increasing @@cte_max_recursion_depth to a larger value。
可通过 SET cte_max_recursion_depth = N; 调整递归深度上限。
计算阶乘
下列示例使用递归 CTE 计算0到5的阶乘。
WITH RECURSIVE cte(n, fact) AS (
    SELECT 0, 1
    UNION ALL
    SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5
)
SELECT n, fact FROM cte;
执行流程如下：
种子部分输出初始行 (0, 1)。
递归部分基于上一轮结果计算 n + 1 和 (n + 1) * fact，直至 n 达到5。
当递归部分输出空结果集时，递归结束。
遍历树形结构
下列示例使用递归 CTE 遍历员工层级结构，从 CEO 开始向下查询所有下属。
首先创建并初始化员工表 employees，其中 manager_id 表示该员工的直接上级。
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);
﻿
INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'CEO', NULL),
    (2, 'Manager 1', 1),
    (3, 'Manager 2', 1),
    (4, 'Employee 1', 2),
    (5, 'Employee 2', 2),
    (6, 'Employee 3', 3);
使用递归 CTE 遍历员工层级。
WITH RECURSIVE employee_hierarchy AS (
    -- 种子部分：从 CEO 开始
    SELECT
        id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
﻿
    UNION ALL
﻿
    -- 递归部分：查找每个员工的下属
    SELECT
        e.id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON eh.id = e.manager_id
)
SELECT id, name, manager_id, level
FROM employee_hierarchy
ORDER BY level, manager_id;
执行结果如下。
+----+------------+------------+-------+
﻿
| id | name       | manager_id | level |
﻿
+----+------------+------------+-------+
﻿
|  1 | CEO        |       NULL |     1 |
|  2 | Manager 1  |          1 |     2 |
|  3 | Manager 2  |          1 |     2 |
|  4 | Employee 1 |          2 |     3 |
|  5 | Employee 2 |          2 |     3 |
|  6 | Employee 3 |          3 |     3 |
﻿
+----+------------+------------+-------+
6 rows in set (0.05 sec)
使用限制
使用 CTE 时，请注意以下限制。
递归 CTE 的递归部分必须引用 CTE 自身且仅能引用一次，不允许在子查询中引用。
递归 CTE 的递归部分不支持 ORDER BY、聚合函数、窗口函数、DISTINCT 和 LIMIT 操作。
递归 CTE 的递归深度受系统变量 cte_max_recursion_depth 限制，默认值为1000。超过限制时查询返回错误，可通过调整该变量上调上限。
当 CTE 在外层查询中被多次引用时，引擎会自动选择物化（Materialization）方式执行；当仅引用一次时，引擎默认采用内联（Merge）方式展开。
非递归 CTE	递归 CTE	是否需要 `Hint /+ MERGE() /`	流式执行
支持	支持	不需要	支持
参数项	描述
`WITH`	CTE 定义的开始关键字。
`RECURSIVE`	可选关键字。包含 `RECURSIVE` 时表示允许在 CTE 中引用自身，用于构造递归查询。
`cte_name`	CTE 的名称，可在后续查询中被引用。
`col_name`	可选的列名列表，用于指定 CTE 结果集的列名。如果省略，则使用子查询中的列名。
`subquery`	CTE 内部的子查询，定义 CTE 的内容。
多个 CTE	一个 `WITH` 子句中可定义多个 CTE，相互之间使用半角逗号分隔。
CTE 语法使用说明

本页目录：

CTE 简介

支持情况

CTE 的优势

语法结构

非递归 CTE

单个 CTE

多个 CTE

嵌套 CTE

递归 CTE

计算阶乘

遍历树形结构

使用限制
