CTE 公共表表达式
High Contrast
Dark Mode
Light Mode
Sepia
Forest
2 min read326 words

CTE 公共表表达式

CTE（Common Table Expression，公共表表达式）让你像给子查询起名字一样，把复杂的查询拆解成可读的命名步骤。不但更易读，某些场景下还比嵌套子查询更高效。

基本 CTE 语法

WITH cte_name AS (
SELECT ...
FROM ...
WHERE ...
)
SELECT *
FROM cte_name
WHERE ...;

场景一：让复杂查询变可读

不用 CTE，找出每个用户最近一次购买的订单：

-- ❌ 嵌套子查询，难以阅读
SELECT u.email, o.total_amount, o.created_at
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.id = (
SELECT id FROM orders
WHERE user_id = u.id
ORDER BY created_at DESC
LIMIT 1
);

用 CTE 改写：

-- ✅ CTE：每步有名字，逻辑清晰
WITH latest_orders AS (
SELECT DISTINCT ON (user_id)
id, user_id, total_amount, created_at
FROM orders
ORDER BY user_id, created_at DESC
)
SELECT
u.email,
lo.total_amount,
lo.created_at AS last_purchase_at
FROM users u
JOIN latest_orders lo ON lo.user_id = u.id;

场景二：多步计算（链式 CTE）

-- 多个 CTE 可以链式定义，后面的可以引用前面的
WITH
-- 第一步：计算过去 30 天的活跃用户
active_users AS (
SELECT DISTINCT user_id
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
),
-- 第二步：计算这些活跃用户的消费总额
user_spending AS (
SELECT o.user_id, SUM(o.total_amount) AS total_spent
FROM orders o
JOIN active_users au ON o.user_id = au.user_id
GROUP BY o.user_id
),
-- 第三步：按消费金额分级
user_tiers AS (
SELECT
user_id,
total_spent,
CASE
WHEN total_spent >= 1000 THEN 'platinum'
WHEN total_spent >= 500  THEN 'gold'
WHEN total_spent >= 100  THEN 'silver'
ELSE 'bronze'
END AS tier
FROM user_spending
)
-- 最终查询：合并用户信息
SELECT
u.email,
ut.total_spent,
ut.tier
FROM users u
JOIN user_tiers ut ON u.id = ut.user_id
ORDER BY ut.total_spent DESC;

场景三：递归 CTE（树形结构）

递归 CTE 是处理层级数据（评论树、组织架构、分类树）的强大工具：

-- 示例：公司组织架构
CREATE TABLE employees (
id         INTEGER PRIMARY KEY,
name       TEXT NOT NULL,
manager_id INTEGER REFERENCES employees(id),  -- 自引用
title      TEXT
);
INSERT INTO employees VALUES
(1, 'Alice', NULL, 'CEO'),
(2, 'Bob', 1, 'VP Engineering'),
(3, 'Carol', 1, 'VP Marketing'),
(4, 'Dave', 2, 'Engineering Manager'),
(5, 'Eve', 2, 'Senior Engineer'),
(6, 'Frank', 4, 'Engineer');
-- 递归 CTE：从 CEO 开始，找出所有汇报关系
WITH RECURSIVE org_tree AS (
-- 基础查询（锚点）：从 CEO 开始
SELECT
id, name, manager_id, title,
0 AS depth,               -- 层级深度
name AS path              -- 汇报路径
FROM employees
WHERE manager_id IS NULL   -- CEO 没有上级
UNION ALL
-- 递归查询：找出下一级汇报
SELECT
e.id, e.name, e.manager_id, e.title,
ot.depth + 1,
ot.path || ' → ' || e.name
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT
repeat('  ', depth) || name AS indented_name,
title,
path
FROM org_tree
ORDER BY path;
-- 输出：
-- Alice                CEO       Alice
--   Bob               VP Eng    Alice → Bob
--     Dave            Eng Mgr   Alice → Bob → Dave
--       Frank         Engineer  Alice → Bob → Dave → Frank
--     Eve             Sr. Eng   Alice → Bob → Eve
--   Carol             VP Mktg   Alice → Carol

递归 CTE 的更多应用

生成连续日期序列

-- 生成过去 30 天的日期序列（用于填充报表中的空白日期）
WITH RECURSIVE date_series AS (
SELECT CURRENT_DATE - INTERVAL '29 days' AS date_day
UNION ALL
SELECT date_day + INTERVAL '1 day'
FROM date_series
WHERE date_day < CURRENT_DATE
)
SELECT
ds.date_day,
COALESCE(COUNT(o.id), 0) AS order_count
FROM date_series ds
LEFT JOIN orders o ON o.created_at::date = ds.date_day
GROUP BY ds.date_day
ORDER BY ds.date_day;

查找所有子分类

-- 找出指定分类（id=5）下的所有子分类（任意深度）
WITH RECURSIVE subcategories AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories
WHERE id = 5
UNION ALL
SELECT c.id, c.name, c.parent_id, sc.depth + 1
FROM categories c
JOIN subcategories sc ON c.parent_id = sc.id
)
SELECT id, name, depth
FROM subcategories
ORDER BY depth, name;

CTE 的性能注意事项

PostgreSQL 12 之前：CTE 是"优化栅栏"——计划器不会把 CTE 内的条件
与外部查询合并优化。每个 CTE 都被单独计算（materialized）。
PostgreSQL 12+：计划器默认会对可优化的 CTE 进行内联（inline），
不再自动 materialize，性能通常更好。
手动控制 CTE 行为：
WITH cte AS MATERIALIZED (...)     -- 强制物化（PostgreSQL 12+）
WITH cte AS NOT MATERIALIZED (...)  -- 强制内联

本节记录清单

下一节：窗口函数：ROW_NUMBER、RANK、LAG、LEAD——窗口函数能在不减少行数的情况下计算聚合值，是排名、同比、移动平均等分析场景的利器。