PostgreSQL 中的 CTE 和递归查询如何工作？

Question

Accepted Answer

**CTE**（Common Table Expression），通过 **`WITH`** 定义，是一个命名的临时结果集，使复杂查询更易读。PostgreSQL 完全支持 CTE，包括用于遍历分层/树形数据的 **recursive** CTE（`WITH RECURSIVE`）。

## 基本 CTE — 易读的查询构建

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

CTE 将复杂查询分解为命名的、易读的步骤（链式的，每个引用前一个）— 比嵌套子查询清晰得多。

## 递归 CTE — 遍历层级

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

**递归 CTE** 有一个基础情况（起始行）和一个递归步骤（`UNION ALL` 连接回 CTE 本身），迭代直到没有新行 — 非常适合 **树和层级结构**（组织结构图、分类树、物料清单、图遍历）。

## CTE 物化的说明（Postgres 特定）

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

Postgres 12 之前，CTE 总是一个