跳到主要内容

PostgreSQL CTE 公用表表达式教程

CTE（Common Table Expression，公用表表达式）用 WITH 给子查询命名，让复杂查询分层可读；递归 CTE 可以遍历树形与图状数据。

本篇内容​

  • WITH 基本语法与多 CTE 串联
  • CTE 与子查询、视图的取舍
  • 递归 CTE（WITH RECURSIVE）
  • MATERIALIZED / NOT MATERIALIZED 提示

基本用法​

-- 先算高价订单，再统计每个用户的笔数
WITH high_value_orders AS (
  SELECT user_id, amount FROM orders WHERE amount > 1000
)
SELECT u.name, COUNT(*) AS cnt
FROM users u
JOIN high_value_orders h ON h.user_id = u.id
GROUP BY u.name;

多个 CTE 用逗号串联，后者可引用前者：

WITH
active_users AS (
  SELECT id, name FROM users WHERE status = 1
),
user_orders AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT a.name, COALESCE(o.cnt, 0) AS order_cnt
FROM active_users a
LEFT JOIN user_orders o ON o.user_id = a.id;

递归 CTE​

-- 遍历组织树：从根节点开始，逐级找下级
WITH RECURSIVE org_tree AS (
  -- 锚点：根节点
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- 递归项：把下级拼上来
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
)
SELECT id, name, depth FROM org_tree ORDER BY depth, id;
待补充（TODO）
  • 补充：递归 CTE 的终止条件与无限递归风险（SEARCH / CYCLE 子句）
  • 补充：MATERIALIZED 与 NOT MATERIALIZED 对执行计划的影响
  • 补充：数据修改型 CTE（WITH … INSERT / UPDATE / DELETE RETURNING）
  • 补充：递归查询的典型场景（图遍历、物料清单 BOM、日期序列生成）