一、支持情况
·1.2404.x
在1.2404.x 版本中，只读分析引擎仅支持 Non-Recursive CTE，使用时需要添加Hint /*+ MERGE() */，否则可能无法生成正确的执行计划。在后续版本中，只读分析引擎将逐步支持 Recursive CTE，并且会优化 CTE 的执行性能。
·2.2410.x
在2.2410.x 版本中，只读分析引擎仍然仅支持 Non-Recursive CTE。但是当前版本执行 CTE 语法时已经无需添加Hint /*+ MERGE() */即可正常执行。同时在2.2410.x 版本中，也支持了流式执行的方式，大幅度优化了 CTE 在执行时的性能。
·4.2506.x
在4.2506.x 版本中，只读分析引擎可以支持 Recursive CTE。
二、CTE 简介
公共表达式（Common Table Expressions，CTE）是 SQL 标准的一部分，通常被称为“WITH 子句”。CTE 在 SQL：1999标准首次引入，旨在提供一种简洁而强大的方法来定义临时结果集。这些结果集可以在单个 SQL 语句中被多次引用，大大提高了查询的可读性和可维护性。
WITH 子句使用示例：
— Start defining CTE.WITH CustomerCTE AS ( SELECT customer_id, first_name, last_name, email_address FROM customer)– End defining CTE.SELECT *FROM CustomerCTE; — Reference the CTE.
三、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 [，col_name] …)] | 可选的列名列表，为 CTE 的结果集指定列名。如果省略，将使用子查询中的列名。 |
| AS (subquery) | CTE 内部的子查询，定义 CTE 的内容。 |
| 逗号和额外的 CTEs | 在一个 WITH 子句中，可以定义多个 CTEs，用逗号分隔。每个额外的 CTE 都遵循相同的结构：cte_name [(col_name …)] AS (subquery)。 |
五、非递归 CTE（Non-Recursive 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（Recursive CTE）
在递归 CTE 中，CTE 会引用自身。Recursive CTE 常用于处理树结构或图结构的数据查询，例如计算阶乘，生成序列或遍历层级关系。
Recursive CTE 由 Seed Part Subquery，Union Type，Recursive Part Subquery 三部分组成。Seed Part Subquery 不引用自身，Recursive Part Subquery 一定引用自身。
WITH RECURSIVE cte(n, fact) AS ( SELECT 0, 1 — Seed Part Subquery UNION ALL — Union Type SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5 — Recursive Part Subquery)SELECT n, fact FROM 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;
在这个示例中，递归部分 UNION ALL SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5 会重复调用自身，直到 n 达到5，递归部分输出空行时，结束递归。
·遍历树结构
假设我们有一张表 employees，包含员工的层级关系，其中 id 是员工的唯一标识，name 是员工姓名，manager_id 是该员工的上级员工 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;– Result┌───────┬────────────┬────────────┬───────┐│ id │ name │ manager_id │ level ││ int32 │ varchar │ int32 │ int32 │├───────┼────────────┼────────────┼───────┤│ 1 │ CEO │ │ 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 │└───────┴────────────┴────────────┴───────┘
八、基础 CTE
WITH CustomerCTE AS ( SELECT customer_id, first_name, last_name, email_address FROM customer)SELECT /*+ MERGE() */ * FROM CustomerCTE;
九、多个 CTE
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 /*+ MERGE() */ CTE1.first_name, CTE1.last_name, CTE2.ss_sales_priceFROM CTE1JOIN CTE2 ON CTE1.customer_id = CTE2.ss_customer_sk;
·定义了两个 CTE：CTE1 和 CTE2。
·在最终查询中使用这两个 CTE 的结果集进行 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 || John | Doe | 67.75 || Jane | Smith | 22.99 || Michael | Johnson | 41.50 || Emily | Brown | 89.99 || David | Wilson | 33.25 |+————+———–+—————-+10 rows in set (0.12 sec)
十、嵌套 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 — 假设设置一个阈值, 例如消费超过 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 /*+ MERGE() */ * FROM CustomerDetails;
·SalesSummary 计算每个顾客的总消费。
·TopCustomers 从 SalesSummary 结果集中筛选出消费超过1000的顾客。
·CustomerDetails 将 customer 表中的顾客信息与 TopCustomers 中的结果集进行连接。
·最终的 SELECT 查询从 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 || 1006 | Sarah | Davis | 2250.75 || 1007 | Robert | Taylor | 1955.50 || 1008 | Jennifer | Anderson | 1725.25 || 1009 | William | Thomas | 2015.00 || 1010 | Lisa | Jackson | 1890.75 |+————-+————+———–+————–+10 rows in set (0.15 sec)