SQL中的CTE用法初步（Common Table Expression公共表表达式）

CTE，全称 Common Table Expression（公共表表达式），是 SQL 的一个强大特性。
概念

CTE是在写 SQL 时临时定义的可复用“虚拟表”，用 WITH 开头。
可以让复杂 SQL 变得更简洁、可读、可复用，还能实现递归查询（树结构）。
基本形式

1. WITH temp AS (
2.     SELECT id, price FROM product WHERE price > 100
3. )
4. SELECT * FROM temp WHERE id < 1000;

复制代码

其中，temp 是临时表，通过CTE让SQL 更清晰，避免重复子查询。
价值

1. 提高代码的可读性和可维护性

这是 CTE 最直接的好处。当你的 SQL 逻辑非常复杂，包含多层嵌套的子查询（Subquery）时，代码会变得像“洋葱”一样难以阅读。CTE 允许你将逻辑扁平化，按顺序定义数据处理步骤：

1. SELECT * FROM (
2.     SELECT * FROM (
3.         SELECT UserID, SUM(Amount) as Total FROM Orders GROUP BY UserID
4.     ) AS UserTotals WHERE Total > 1000
5. ) AS HighValueUsers
6. JOIN Users ON HighValueUsers.UserID = Users.ID;

复制代码

这种写法需要从最里面往外读，逻辑不仅费劲，而且容易出错。
如果用CTE：

1. WITH UserTotals AS (
2.     -- 第一步：计算每个用户的总金额
3.     SELECT UserID, SUM(Amount) as Total
4.     FROM Orders
5.     GROUP BY UserID
6. ),
7. HighValueUsers AS (
8.     -- 第二步：筛选高价值用户
9.     SELECT *
10.     FROM UserTotals
11.     WHERE Total > 1000
12. )
13. -- 第三步：最终查询
14. SELECT *
15. FROM HighValueUsers
16. JOIN Users ON HighValueUsers.UserID = Users.ID;

复制代码

2. 实现递归查询 (Recursive CTE)

这是 CTE 的杀手级功能。普通的子查询无法引用自身，而 CTE 可以。这在处理层级数据（Hierarchical Data）时非常有用，例如：
公司组织架构（查找某人的所有下属，或者查找某人的所有上级）。
菜单/分类树（无限级分类）。
图结构数据（路径查找）。

1. -- 生成 1 到 10 的序列
2. WITH RECURSIVE NumberSequence AS (
3.     -- 初始成员 (Anchor Member)
4.     SELECT 1 AS n
5.     UNION ALL
6.     -- 递归成员 (Recursive Member)
7.     SELECT n + 1
8.     FROM NumberSequence
9.     WHERE n < 10
10. )
11. SELECT * FROM NumberSequence;

复制代码

晚点我们再看更实际的例子。
3. 在同一查询中多次复用

如果你在一个复杂的查询中需要多次用到同一个中间结果集：

• 使用子查询： 你必须把那段 SQL 代码复制粘贴两遍（或者数据库引擎需要计算两遍）。
  
• 使用 CTE： 你只需要定义一次，然后在后面的主查询中可以多次引用它（例如 JOIN 它自己）。
  

1. WITH MonthlySales AS (
2.     SELECT Month, SUM(Sales) as TotalSales
3.     FROM Orders
4.     GROUP BY Month
5. )
6. SELECT
7.     CurrentMonth.Month,
8.     CurrentMonth.TotalSales,
9.     CurrentMonth.TotalSales - LastMonth.TotalSales AS Growth
10. FROM MonthlySales AS CurrentMonth
11. LEFT JOIN MonthlySales AS LastMonth
12.     ON CurrentMonth.Month = LastMonth.Month + 1;

复制代码

4. 配合窗口函数进行数据清洗

常用于去重或分页场景。例如，删除表中的重复记录（保留 ID 最大的那条）：

1. WITH DuplicateRows AS (
2.     SELECT *,
3.            ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID DESC) as rn
4.     FROM Users
5. )
6. DELETE FROM DuplicateRows WHERE rn > 1;

复制代码

虽然这看起来像是从 CTE 删除，但实际上是删除了底层表 Users 对应的数据。

取决于数据库支持情况，SQL Server 和 PostgreSQL 支持较好

性能

看了上面说的优点，你可能会以为CTE总是一次计算，多次复用。但是实际效果可能让你失望。
CTE更像是语法糖，而非性能优化器。和普通SQL一样，使用不当也会带来性能问题。
对于非递归 CTE (Non-Recursive CTEs)：

它们通常与使用子查询或派生表在性能上相同。虽然有优点，但是不是性能上的：

• 可读性： 复杂的逻辑可以分解成多个命名步骤，使代码更容易理解。
  
• 模块化： 同一个 CTE 可以在主查询中多次引用（尽管它通常只执行一次，除非优化器选择重新计算）。
  

对于递归 CTE (Recursive CTEs)：

递归 CTE 是一个强大的功能，但在性能上需要注意。不当的递归条件或大量数据可能导致查询时间过长，甚至耗尽资源。它们通常是解决这类问题的唯一 SQL 方式，除非使用存储过程中的循环。
最大的误解：自动物化

很多人认为 CTE 会像临时表一样将结果集存储起来，并在后续多次引用时直接使用这个存储的结果。
这种方式有个专业名词叫“物化”（materialization），指数据库先运行 CTE、将结果存到临时存储（内存或磁盘），然后重用。
相反，会执行多次的方式在CTE 场景中叫做“内联”（inline）；
事实是在大多数主流数据库中，非递归 CTE 通常不会自动实现。优化器会将 CTE 的定义合并到主查询中。这意味着如果一个 CTE 被引用了多次，优化器可能会选择：

• 重新计算：
  如果 CTE 被引用多次，数据库可能多次执行其内部逻辑，导致性能浪费（尤其大数据量时）。这在 SQL Server 和早期 MySQL 中特别明显。参考即将死亡的stackoverflow: https://stackoverflow.com/questions/62312318/cte-executed-multiple-times
  
• 计算一次并重用：
  现代优化器（如 PostgreSQL 12+ 或 Oracle）有时会自动物化以避免重复计算，但这依赖查询统计、数据分布和配置，不是 100% 可靠。MySQL 和 SQL Server 更保守，不会默认缓存。
  

PostgreSQL (12+) 可以用 WITH ... AS MATERIALIZED 强制物化；NOT MATERIALIZED 强制内联。
Oracle (19c+)用 /*+ MATERIALIZE */ 或 /*+ INLINE */ 进行提示。

虽然 CTE 提高了可读性，但它只是将逻辑前移了。如果 CTE 内部包含了非常耗时的操作（如全表扫描、复杂的连接、大量计算），那么主查询的性能依然会很差。
要确保 CTE 的定义尽可能高效地返回所需的数据。如果可能，通过 WHERE 子句或 JOIN 条件尽早过滤数据。
所以，查 EXPLAIN 验证执行计划 —— 这是 CTE 性能的唯一真相。

递归CTE例子

下面我们跑一遍在 PostgreSQL 里执行的示例：
包括：

• 创建层级表（树结构）
  
• 插入测试数据（模拟类目树 / 部门树）
  
• 使用 递归 CTE 查询所有子节点、所有父节点
  

创建层级表

1. CREATE TABLE category (
2.     id          SERIAL PRIMARY KEY,
3.     name        TEXT NOT NULL,
4.     parent_id   INT REFERENCES category(id)
5. );

复制代码

插入层级数据（模拟 3 层树）

1. INSERT INTO category (id, name, parent_id) VALUES
2.     (1, '电子产品', NULL),
3.     (2, '手机', 1),
4.     (3, '安卓手机', 2),
5.     (4, '苹果手机', 2),
6.     (5, '电脑', 1),
7.     (6, '笔记本电脑', 5),
8.     (7, '台式机', 5);

复制代码

使用CTE

咱们看看插入的数据是啥样的（带缩进）

1. WITH RECURSIVE tree AS (
2.     SELECT id, name, parent_id, 1 AS level, name AS path
3.     FROM category
4.     WHERE parent_id IS NULL
6.     UNION ALL
8.     SELECT c.id, c.name, c.parent_id,
9.            t.level + 1 AS level,
10.            t.path || ' > ' || c.name
11.     FROM category c
12.     JOIN tree t ON c.parent_id = t.id
13. )
14. SELECT id,
15.        repeat('  ', level - 1) || name AS display_name,
16.        path
17. FROM tree
18. ORDER BY path;

复制代码

查询 手机(id=2) 下的所有子类目：安卓、苹果

1. WITH RECURSIVE sub_tree AS (
2.     -- 起点
3.     SELECT id, name, parent_id
4.     FROM category
5.     WHERE id = 2
6.    
7.     UNION ALL
9.     -- 向下递归
10.     SELECT c.id, c.name, c.parent_id
11.     FROM category c
12.     JOIN sub_tree st ON c.parent_id = st.id
13. )
14. SELECT * FROM sub_tree;

复制代码

查询 笔记本电脑(id=6) 的所有上级：

1. WITH RECURSIVE parents AS (
2.     -- 起点
3.     SELECT id, name, parent_id
4.     FROM category
5.     WHERE id = 6
6.    
7.     UNION ALL
8.    
9.     -- 向上递归到根节点
10.     SELECT c.id, c.name, c.parent_id
11.     FROM category c
12.     JOIN parents p ON c.id = p.parent_id
13. )
14. SELECT * FROM parents;

复制代码

查询所有节点，并附带层级深度（level）

1. WITH RECURSIVE tree AS (
2.     SELECT id, name, parent_id, 1 AS level
3.     FROM category
4.     WHERE parent_id IS NULL  -- 从根节点开始
5.    
6.     UNION ALL
7.    
8.     SELECT c.id, c.name, c.parent_id, t.level + 1
9.     FROM category c
10.     JOIN tree t ON c.parent_id = t.id
11. )
12. SELECT *
13. FROM tree
14. ORDER BY level, id;

复制代码

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！