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;
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ 感谢发布原创作品，程序园因你更精彩
页:
[1]