一、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_priceFROM CTE1JOIN 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 计算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, levelFROM employee_hierarchyORDER 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）方式展开。