PostgreSQL CTE 递归查询

1 min入门2026/9/12

PostgreSQL CTE 递归查询的完整教学讲解。

基本 CTE

换行写法：WITH 简单 CTE WITH <CTE名> AS (SELECT ...) SELECT * FROM <CTE名>;

-- 使用 CTE 简化查询
WITH active_users AS (
  SELECT id, username, email FROM users WHERE status = 1
)
SELECT * FROM active_users ORDER BY username;

换行写法：多个 CTE WITH <CTE1> AS (...), <CTE2> AS (...) SELECT ... FROM <CTE1> JOIN <CTE2>

-- 多个 CTE 组合查询
WITH user_counts AS (
  SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id
),
user_totals AS (
  SELECT user_id, SUM(total_amount) AS total FROM orders GROUP BY user_id
)
SELECT u.username, uc.order_count, ut.total
FROM users u
LEFT JOIN user_counts uc ON u.id = uc.user_id
LEFT JOIN user_totals ut ON u.id = ut.user_id;

换行写法：CTE 引用前一个 CTE WITH <CTE1> AS (...), <CTE2> AS (SELECT ... FROM <CTE1>) SELECT * FROM <CTE2>

-- 后一个 CTE 引用前一个
WITH order_stats AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
),
heavy_users AS (
  SELECT user_id FROM order_stats WHERE cnt > 10
)
SELECT u.username FROM users u JOIN heavy_users h ON u.id = h.user_id;

CTE 数据操作

换行写法：WITH 配合 INSERT WITH <CTE> AS (SELECT ...) INSERT INTO <表> SELECT * FROM <CTE>;

-- 将查询结果插入新表
WITH active AS (SELECT * FROM users WHERE status = 1)
INSERT INTO active_users_backup SELECT * FROM active;

换行写法：WITH 配合 UPDATE WITH <CTE> AS (SELECT ...) UPDATE <表> SET ... FROM <CTE> WHERE ...

-- 基于 CTE 更新
WITH user_totals AS (
  SELECT user_id, SUM(total_amount) AS total FROM orders GROUP BY user_id
)
UPDATE users SET balance = balance - ut.total
FROM user_totals ut
WHERE users.id = ut.user_id;

换行写法：WITH 配合 DELETE WITH <CTE> AS (SELECT ...) DELETE FROM <表> WHERE <列> IN (SELECT ... FROM <CTE>)

-- 基于 CTE 删除
WITH inactive AS (SELECT id FROM users WHERE status = 0)
DELETE FROM orders WHERE user_id IN (SELECT id FROM inactive);

换行写法：WITH RETURNING 返回 WITH <CTE> AS (INSERT ... RETURNING ...) SELECT * FROM <CTE>

-- 插入并返回结果供后续使用
WITH inserted AS (
  INSERT INTO users (username, email) VALUES ('zhangsan', 'zs@example.com')
  RETURNING id, username
)
SELECT * FROM inserted;

递归 CTE 基础

换行写法：WITH RECURSIVE 基本结构 WITH RECURSIVE <CTE名> AS (<基础查询> UNION [ALL] <递归查询>) SELECT * FROM <CTE名>

-- 生成 1 到 10 的序列
WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

换行写法：UNION 去重递归 WITH RECURSIVE <CTE名> AS (<基础> UNION <递归>) SELECT * FROM <CTE名>

-- 使用 UNION 去重递归
WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;

层级数据递归

换行写法：组织架构树查询 WITH RECURSIVE <CTE名> AS (基础 UNION ALL 递归) SELECT * FROM <CTE名>

-- 查询员工层级关系
WITH RECURSIVE employee_tree AS (
  -- 基础查询：顶层员工
  SELECT id, name, manager_id, 1 AS level, name::TEXT AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- 递归查询：下属员工
  SELECT e.id, e.name, e.manager_id, et.level + 1, et.path || ' -> ' || e.name
  FROM employees e
  JOIN employee_tree et ON e.manager_id = et.id
)
SELECT id, name, level, path FROM employee_tree ORDER BY path;

换行写法：分类树查询 WITH RECURSIVE <CTE名> AS (基础 UNION ALL 递归) SELECT * FROM <CTE名>

-- 查询分类及其所有子分类
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth, name::TEXT AS full_path
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.depth + 1, ct.full_path || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, name, depth, full_path FROM category_tree ORDER BY full_path;

换行写法：从指定节点向下查询 WITH RECURSIVE <CTE名> AS (基础 UNION ALL 递归) SELECT * FROM <CTE名>

-- 查询指定部门的所有子部门
WITH RECURSIVE sub_departments AS (
  SELECT id, name, parent_id FROM departments WHERE id = 5
  UNION ALL
  SELECT d.id, d.name, d.parent_id
  FROM departments d
  JOIN sub_departments sd ON d.parent_id = sd.id
)
SELECT * FROM sub_departments;

换行写法：从指定节点向上查询祖先 WITH RECURSIVE <CTE名> AS (基础 UNION ALL 递归) SELECT * FROM <CTE名>

-- 查询指定员工的所有上级
WITH RECURSIVE managers AS (
  SELECT id, name, manager_id FROM employees WHERE id = 100
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN managers m ON e.id = m.manager_id
)
SELECT * FROM managers;

图遍历递归

换行写法：好友关系传递查询 WITH RECURSIVE <CTE名> AS (基础 UNION ALL 递归) SELECT * FROM <CTE名>

-- 查询某人的所有间接好友
WITH RECURSIVE friend_chain AS (
  SELECT user_id, friend_id, 1 AS distance
  FROM friendships
  WHERE user_id = 1
  UNION ALL
  SELECT fc.user_id, f.friend_id, fc.distance + 1
  FROM friendships f
  JOIN friend_chain fc ON f.user_id = fc.friend_id
  WHERE fc.distance < 5
)
SELECT DISTINCT friend_id, MIN(distance) AS min_distance
FROM friend_chain
GROUP BY friend_id;

换行写法：路径图遍历 WITH RECURSIVE <CTE名> AS (基础 UNION ALL 递归) SELECT * FROM <CTE名>

-- 查询两点间所有路径
WITH RECURSIVE paths AS (
  SELECT start_node, end_node, cost, start_node::TEXT AS route
  FROM edges
  WHERE start_node = 'A'
  UNION ALL
  SELECT p.start_node, e.end_node, p.cost + e.cost, p.route || '->' || e.end_node
  FROM edges e
  JOIN paths p ON e.start_node = p.end_node
  WHERE p.route NOT LIKE '%' || e.end_node || '%'
)
SELECT route, cost FROM paths WHERE end_node = 'D' ORDER BY cost;

物化 CTE（PG12+）

换行写法：MATERIALIZED 强制物化 WITH <CTE名> AS MATERIALIZED (SELECT ...) SELECT * FROM <CTE名>

-- 强制物化 CTE 提高重复引用性能
WITH expensive_query AS MATERIALIZED (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.username, eq.cnt FROM users u JOIN expensive_query eq ON u.id = eq.user_id
UNION ALL
SELECT 'total', SUM(cnt) FROM expensive_query;

换行写法：NOT MATERIALIZED 内联展开 WITH <CTE名> AS NOT MATERIALIZED (SELECT ...) SELECT * FROM <CTE名>

-- 强制内联展开让优化器自由下推条件
WITH active_users AS NOT MATERIALIZED (
  SELECT * FROM users WHERE status = 1
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';

递归 CTE 注意事项

换行写法：使用 LIMIT 防止无限递归 WITH RECURSIVE <CTE名> AS (...) SELECT * FROM <CTE名> LIMIT <数量>

-- 限制递归结果数量
WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 1000000
)
SELECT n FROM counter LIMIT 100;

单行写法：设置递归深度限制 SET statement_timeout = '<时长>';

-- 设置语句超时防止递归死循环
SET statement_timeout = '30s';

单行写法：设置递归迭代上限 SET max_recursive_workers = <数值>;

-- 控制递归工作进程数
SET max_recursive_workers = 4;

常见应用场景

换行写法：日期序列生成 WITH RECURSIVE <CTE名> AS (基础 UNION ALL 递归) SELECT * FROM <CTE名>

-- 生成连续日期序列
WITH RECURSIVE date_range AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL '1 day' FROM date_range WHERE day < DATE '2024-01-31'
)
SELECT day FROM date_range;

换行写法：generate_series 替代方案 SELECT generate_series(<起>, <止>, <步长>);

-- 使用内置函数生成序列
SELECT generate_series(1, 10) AS n;
SELECT generate_series(DATE '2024-01-01', DATE '2024-01-31', INTERVAL '1 day') AS day;

换行写法：层级汇总 WITH RECURSIVE <CTE名> AS (基础 UNION ALL 递归) SELECT * FROM <CTE名>

-- 计算每个部门及其子部门的员工总数
WITH RECURSIVE dept_tree AS (
  SELECT id, parent_id FROM departments WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.parent_id FROM departments d JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT dt.id, d.name, COUNT(e.id) AS employee_count
FROM dept_tree dt
JOIN departments d ON dt.id = d.id
LEFT JOIN employees e ON e.dept_id = dt.id
GROUP BY dt.id, d.name;

PostgreSQL CTE 递归查询

基本 CTE#

CTE 数据操作#

递归 CTE 基础#

层级数据递归#

图遍历递归#

物化 CTE（PG12+）#

递归 CTE 注意事项#

常见应用场景#