SQL Academy · 课时

公用表表达式（WITH）

Q: 「公用表表达式（WITH）」这节课中我会学到什么？

将嵌套查询重构为易读的 WITH 子句，串联 CTE，并学习 PostgreSQL 12+ 中的物化规则。 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

将嵌套查询重构为易读的 WITH 子句，串联 CTE，并学习 PostgreSQL 12+ 中的物化规则。

公用表表达式（WITH） 是 CoddyKit 上的免费 SQL Academy 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

什么是 CTE

公共表表达式（CTE）是使用 WITH 定义的命名子查询：

WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT user_id, COUNT(*)
FROM paid_orders
GROUP BY user_id;

为什么使用 CTE

三大优点：

可读性 — 将一个 200 行的查询拆分为多个命名步骤
复用 — 在多处引用相同的中间结果
递归 — 只有 CTE 支持递归查询（下一课介绍）

串联 CTE

在一个 WITH 中定义多个 CTE，并在后一个 CTE 中使用前一个：

WITH last_30 AS (
  SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days'
),
per_user AS (
  SELECT user_id, SUM(total) AS revenue FROM last_30 GROUP BY user_id
)
SELECT u.email, p.revenue
FROM users u
JOIN per_user p ON p.user_id = u.id
ORDER BY p.revenue DESC LIMIT 20;

复用 CTE

如果同一个中间结果要使用两次，CTE 可以清楚地表达意图：

WITH recent_users AS (
  SELECT id FROM users WHERE created_at >= NOW() - INTERVAL '7 days'
)
SELECT 'new orders'  AS metric, COUNT(*) FROM orders
  WHERE user_id IN (SELECT id FROM recent_users)
UNION ALL
SELECT 'new revenue', SUM(total) FROM orders
  WHERE user_id IN (SELECT id FROM recent_users);

CTE 物化（PostgreSQL ≤ 11）

较早版本的 PG 始终会物化 CTE 的输出，相当于设置了一道规划器屏障。从 PG 12 开始，规划器默认会将 CTE 内联，除非您另行指定：

-- Force the old materialise behaviour (rarely needed):
WITH x AS MATERIALIZED (SELECT ...) ...

-- Force inlining (default):
WITH x AS NOT MATERIALIZED (SELECT ...) ...

数据修改型 CTE

CTE 可以使用 INSERT/UPDATE/DELETE，这对于以原子方式在表之间移动行非常方便：

WITH moved AS (
  DELETE FROM orders WHERE status = 'archived' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;

DML 中的 CTE

使用 RETURNING + WITH 实现“查找并操作”：

WITH cancelled AS (
  UPDATE orders SET status = 'cancelled'
  WHERE created_at < NOW() - INTERVAL '14 days'
    AND status = 'pending'
  RETURNING id
)
INSERT INTO audit_log (event, order_id)
SELECT 'auto-cancel', id FROM cancelled;

执行顺序

数据修改型 CTE 在同一个快照中独立运行。每条语句看到的都是所有修改开始之前的状态 — 这可能令人意外，但行为是可预测的。

CTE、子查询与视图

比较如下：

子查询 — 内联定义；使用一次
CTE — 有名称；在当前查询中可多次使用；查询结束后消失
视图 — 有名称；持久保存；可跨查询复用

不要过度使用 CTE

把所有内容都封装在 CTE 中可以提高查询的可读性，但也可能掩盖成本。当中间结果非常庞大时，规划器可能选择比等价 JOIN 更差的执行计划。

回顾

CTE 为中间查询命名。

提高可读性
支持在一个查询中复用
递归查询所必需
现代 PG 默认会将其内联

快速检查

哪个关键字用于开始定义公共表表达式？

常见问题解答

「公用表表达式（WITH）」课时是免费的吗？

是的 — 「公用表表达式（WITH）」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「公用表表达式（WITH）」这节课中我会学到什么？

将嵌套查询重构为易读的 WITH 子句，串联 CTE，并学习 PostgreSQL 12+ 中的物化规则。你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 3 节课，共 4 节。

「公用表表达式（WITH）」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy