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

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

概念

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

基本形式

WITH temp AS (     SELECT id, price FROM product WHERE price > 100 ) SELECT * FROM temp WHERE id < 1000; 

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

价值

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

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

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

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

WITH UserTotals AS (     -- 第一步：计算每个用户的总金额     SELECT UserID, SUM(Amount) as Total      FROM Orders      GROUP BY UserID ), HighValueUsers AS (     -- 第二步：筛选高价值用户     SELECT *      FROM UserTotals      WHERE Total > 1000 ) -- 第三步：最终查询 SELECT *  FROM HighValueUsers JOIN Users ON HighValueUsers.UserID = Users.ID; 

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

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

-- 生成 1 到 10 的序列 WITH RECURSIVE NumberSequence AS (     -- 初始成员 (Anchor Member)     SELECT 1 AS n     UNION ALL     -- 递归成员 (Recursive Member)     SELECT n + 1      FROM NumberSequence      WHERE n < 10 ) SELECT * FROM NumberSequence; 

晚点我们再看更实际的例子。

3. 在同一查询中多次复用

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

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

WITH MonthlySales AS (     SELECT Month, SUM(Sales) as TotalSales      FROM Orders      GROUP BY Month ) SELECT      CurrentMonth.Month,      CurrentMonth.TotalSales,      CurrentMonth.TotalSales - LastMonth.TotalSales AS Growth FROM MonthlySales AS CurrentMonth LEFT JOIN MonthlySales AS LastMonth      ON CurrentMonth.Month = LastMonth.Month + 1; 

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

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

WITH DuplicateRows AS (     SELECT *,             ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID DESC) as rn     FROM Users ) 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 查询所有子节点、所有父节点

创建层级表

CREATE TABLE category (     id          SERIAL PRIMARY KEY,     name        TEXT NOT NULL,     parent_id   INT REFERENCES category(id) ); 

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

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

使用CTE

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

WITH RECURSIVE tree AS (     SELECT id, name, parent_id, 1 AS level, name AS path     FROM category     WHERE parent_id IS NULL      UNION ALL      SELECT c.id, c.name, c.parent_id,            t.level + 1 AS level,            t.path || ' > ' || c.name     FROM category c     JOIN tree t ON c.parent_id = t.id ) SELECT id,        repeat('  ', level - 1) || name AS display_name,        path FROM tree ORDER BY path; 

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

WITH RECURSIVE sub_tree AS (     -- 起点     SELECT id, name, parent_id     FROM category     WHERE id = 2          UNION ALL      -- 向下递归     SELECT c.id, c.name, c.parent_id     FROM category c     JOIN sub_tree st ON c.parent_id = st.id ) SELECT * FROM sub_tree; 

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

WITH RECURSIVE parents AS (     -- 起点     SELECT id, name, parent_id     FROM category     WHERE id = 6          UNION ALL          -- 向上递归到根节点     SELECT c.id, c.name, c.parent_id     FROM category c     JOIN parents p ON c.id = p.parent_id ) SELECT * FROM parents; 

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

WITH RECURSIVE tree AS (     SELECT id, name, parent_id, 1 AS level     FROM category     WHERE parent_id IS NULL  -- 从根节点开始          UNION ALL          SELECT c.id, c.name, c.parent_id, t.level + 1     FROM category c     JOIN tree t ON c.parent_id = t.id ) SELECT * FROM tree ORDER BY level, id;