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

转载于 2025-12-05 12:30:02 发布

0 ·

CC 4.0 BY-SA版权

原文链接：https://www.cnblogs.com/somefuture/p/19307282

文章标签：

#it

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

概念

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

基本形式

/* by 01130.hk - online tools website : 01130.hk/zh/constellation.html */
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 允许你将逻辑扁平化，按顺序定义数据处理步骤：

/* by 01130.hk - online tools website : 01130.hk/zh/constellation.html */
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;