本教程共 50 篇 · 第 31 篇 · 更新于 2026-07-31 · 约 10 分钟阅读

31. CTE 公用表表达式（WITH）

本节目标：学会用 WITH 子句写出 CTE（公用表表达式），把复杂查询拆成可读的临时结果集，能在一条语句里多次引用同一个 CTE，并理解它和子查询、临时表的区别。

CTE 的全称是 Common Table Expression（公用表表达式）。通俗点说，它允许你在一条查询里，先「造一个临时表」，再基于它继续查询。这个临时表只在当前这条语句里存在，查完就消失，不会真的写进数据库，也不会留下任何痕迹。

它的关键字是 WITH。我常把它当成「给子查询起个名字」，让嵌套层层套的 SQL 变得顺眼。

基本语法

WITH 名字 (列1, 列2, ...) AS (
    -- 这里写子查询
    SELECT ...
)
-- 主查询，像用普通表一样用上面的名字
SELECT ... FROM 名字;

列名列表可以省略，省略时列名直接继承子查询里的列名。如果子查询里用了 AS 给列起别名，主查询就用那个别名来引用。

一个简单例子

先用全书统一的示例表 users 和 orders（结构见前文）：

CREATE TABLE users (
    id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username   VARCHAR(50) NOT NULL,
    email      VARCHAR(100),
    age        INT,
    created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE orders (
    id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    INT,
    amount     NUMERIC(10,2),
    status     TEXT,
    created_at TIMESTAMP DEFAULT now()
);

还是用 users 和 orders。先算出「每个用户的订单总额」，再找出总额超过 100 的用户：

WITH user_total AS (
    SELECT
        user_id,
        SUM(amount) AS total
    FROM orders
    WHERE user_id IS NOT NULL
    GROUP BY user_id
)
SELECT
    u.username,
    ut.total
FROM user_total ut
JOIN users u ON u.id = ut.user_id
WHERE ut.total > 100
ORDER BY ut.total DESC;

这里 user_total 就是 CTE。它把「分组求和」这一步单独拎出来，主查询只要关心「怎么用这个结果」。比起把 GROUP BY 塞进 FROM 后面的子查询，这样读起来清晰很多。

Note

CTE 的名字只在它所在的这条 SELECT（或 INSERT/UPDATE/DELETE）里有效。你不能在另一条语句里引用它，也不能在 CTE 定义之前就引用自己（递归除外，见第 32 章）。

把 CTE 当表来连接

CTE 可以像普通表一样参与 JOIN、用在 WHERE 里、出现在 SELECT 列表的子查询中。上例里我们就把 user_total 和 users 连了起来。注意 CTE 的名字在主查询里要保证唯一，不能和真实表重名冲突（若重名，CTE 优先于真实表）。

一次定义，多次引用

CTE 最大的好处之一：同一个 CTE 可以在主查询里被引用多次，而不必重写子查询。

WITH paid_orders AS (
    SELECT user_id, SUM(amount) AS paid_total
    FROM orders
    WHERE status = 'paid' AND user_id IS NOT NULL
    GROUP BY user_id
)
SELECT
    u.username,
    paid_orders.paid_total,
    (SELECT AVG(paid_total) FROM paid_orders) AS 全站平均
FROM paid_orders
JOIN users u ON u.id = paid_orders.user_id
WHERE paid_orders.paid_total > (SELECT AVG(paid_total) FROM paid_orders);

paid_orders 被引用了三次，但 PostgreSQL 只需要算一次（在多数情况下；优化器也可能内联展开）。这比把同样的子查询复制三遍省事，也更好维护。

定义多个 CTE

一条 WITH 里可以写多个 CTE，用逗号隔开，后面的 CTE 还能引用前面的：

WITH
user_total AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    WHERE user_id IS NOT NULL
    GROUP BY user_id
),
big_spenders AS (
    SELECT * FROM user_total WHERE total > 100
)
SELECT u.username, bs.total
FROM big_spenders bs
JOIN users u ON u.id = bs.user_id;

big_spenders 直接建立在 user_total 之上，逻辑像流水线一样一层层走。这种「上游算好、下游接着用」的写法，把复杂查询拆成了好懂的步骤。

Tip

当你发现一条 SQL 里子查询嵌套了子查询、自己都快看晕时，就是改用 CTE 的好时机。先用 WITH 把每一步的中间结果命名出来，可读性会立刻提升。

CTE 也能用在 INSERT / UPDATE / DELETE

CTE 不只能配 SELECT。在 INSERT 里，它常用来先筛再插：

-- 先准备一张接收表
CREATE TABLE vip_orders (
    order_id INT,
    amount NUMERIC(10,2)
);

WITH 高额 AS (
    SELECT id, amount FROM orders WHERE amount > 100
)
INSERT INTO vip_orders (order_id, amount)
SELECT id, amount FROM 高额;

在 DELETE 里，它能避免「直接在 DELETE 的 WHERE 里写复杂子查询」导致的绕：

WITH 目标 AS (
    SELECT id FROM users WHERE age > 60
)
DELETE FROM orders WHERE user_id IN (SELECT id FROM 目标);

CTE、子查询、临时表怎么选

方式	存活范围	适合场景
子查询	仅当前位置	一次性的简单中间结果
CTE（`WITH`）	当前整条语句	同一步骤要复用、逻辑分多层
临时表（`CREATE TEMP TABLE`）	整个会话	要在多条语句间反复用、体量很大

不是非用 CTE 不可。如果只是一次性、很简单的中间结果，直接写子查询也行。但遇到下面这些情况，我更倾向 CTE：

同一个中间结果要用好几次；
查询逻辑分好几步，层层递进；
后面第 32 章要讲的「递归查询」，必须用 WITH RECURSIVE，是 CTE 的进阶形态。

Warning

很多人以为「把子查询写成 CTE 就能变快」。其实 PostgreSQL 12 起，优化器就能把 CTE 自动内联（inline）进主查询，默认情况和等价子查询性能差不多。CTE 主要价值是可读性，不是性能。如果你想强制让它先算出来存一份（物化），可以加 MATERIALIZED 提示；反之想强制内联用 NOT MATERIALIZED，例如 WITH cte AS MATERIALIZED (...) SELECT ...。到底是否真物化，最终看执行计划为准，别迷信它能加速。

常见错误

把 CTE 当成「建了张永久表」，结果在另一条语句里引用它报错。
多个 CTE 之间逗号写错，或忘了主查询要引用 CTE 名字。
期望 CTE 自动提速，结果发现和子查询没差别——它本就是可读性工具。

用 CTE 做数据去重

CTE 很适合处理「重复行只留一条」的需求。比如 orders 里如果同一用户同一金额出现了重复，想保留每组的第一条：

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY user_id, amount
               ORDER BY created_at
           ) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

这里 CTE 先把「编号」算出来，主查询再筛 rn = 1，逻辑比嵌套子查询清楚。注意这步只是「查出来」，真要删重复行，可把上面结果写回新表，或配合 DELETE ... WHERE id IN (子查询)。

CTE 与递归的关系

本章的 WITH 是非递归 CTE。只要在 WITH 后面加一个 RECURSIVE 关键字，CTE 就能引用自己，处理树形、层级数据——这正是第 32 章的主题。你可以把 RECURSIVE 理解为「CTE 允许自引用」的开关，普通 CTE 不允许自己引用自己。

Tip

一条 WITH 里可以同时有普通 CTE 和递归 CTE，但只要有任意一个需要自引用，关键字就得写 WITH RECURSIVE，并且命名要避免循环依赖导致优化器困惑。

CTE 的作用域规则

CTE 的名字只在它所在的整条语句里有效，且只能被「后面的」部分引用。同一条 WITH 里，后面的 CTE 可以引用前面的（如 big_spenders 引用 user_total），但前面的不能引用后面的，主查询可以引用所有 CTE。如果写成前向引用，PostgreSQL 会报 relation "xxx" does not exist。这点比临时表省心——临时表要建完才能用，而 CTE 声明即用的同时又有清晰的顺序约束。

小结

CTE 就是「给一段子查询起个名字，临时用一下」。它不产生真实表、不改数据，只让查询更好读、更好写，还能在同一语句里被多次引用。下章我们把它升级成递归 CTE，用来处理树形、层级数据。