跳到主要内容

CTE 公共表表达式

问题​

什么是 CTE？递归 CTE 怎么用？CTE 和子查询有什么区别？

答案​

一、CTE 基础​

CTE（Common Table Expression）使用 WITH 子句定义临时命名结果集，提高 SQL 可读性：

WITH active_users AS (
  SELECT id, name, email FROM users WHERE status = 'active'
),
recent_orders AS (
  SELECT user_id, SUM(amount) AS total 
  FROM orders 
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, o.total
FROM active_users u
JOIN recent_orders o ON u.id = o.user_id
ORDER BY o.total DESC;

二、递归 CTE​

递归 CTE 用于处理 树形/层级结构 数据：

WITH RECURSIVE cte AS (
  -- 基础查询（锚点）
  SELECT id, name, parent_id, 1 AS level
  FROM departments WHERE parent_id IS NULL
  
  UNION ALL
  
  -- 递归查询
  SELECT d.id, d.name, d.parent_id, c.level + 1
  FROM departments d
  JOIN cte c ON d.parent_id = c.id
)
SELECT * FROM cte ORDER BY level, id;

执行过程：

实际场景：组织架构树​

-- 查找某员工的所有上级
WITH RECURSIVE managers AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE id = 100  -- 从张三开始
  
  UNION ALL
  
  SELECT e.id, e.name, e.manager_id, m.depth + 1
  FROM employees e
  JOIN managers m ON e.id = m.manager_id
)
SELECT * FROM managers;

实际场景：路径构建​

-- 构建面包屑导航路径
WITH RECURSIVE path AS (
  SELECT id, name, parent_id, name::text AS full_path
  FROM categories WHERE id = 42
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id, c.name || ' > ' || p.full_path
  FROM categories c
  JOIN path p ON c.id = p.parent_id
)
SELECT full_path FROM path WHERE parent_id IS NULL;
-- 结果: "电子产品 > 手机 > iPhone"

三、CTE 的高级用法​

可写 CTE（PostgreSQL 特有）​

-- 在 CTE 中执行 INSERT/UPDATE/DELETE
WITH deleted AS (
  DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days'
  RETURNING *
)
INSERT INTO logs_archive SELECT * FROM deleted;

多个 CTE 串联​

WITH 
step1 AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders GROUP BY user_id
),
step2 AS (
  SELECT user_id, order_count,
    CASE 
      WHEN order_count >= 10 THEN 'VIP'
      WHEN order_count >= 5 THEN '活跃'
      ELSE '普通'
    END AS user_level
  FROM step1
)
SELECT u.name, s.user_level, s.order_count
FROM step2 s JOIN users u ON s.user_id = u.id;

四、CTE vs 子查询​

对比项CTE子查询
可读性✅ 高（命名+分步）❌ 嵌套深时难读
递归✅ 支持❌ 不支持
复用✅ 同一 CTE 可引用多次❌ 需要重复写
可写操作✅ 可包含 DML❌ 通常不行
性能（PG 12+）优化器可选择内联或物化优化器自动优化
性能（PG 12-）强制物化（可能更慢）可被优化器内联
物化控制（PostgreSQL 12+）
-- 强制物化（执行一次，缓存结果）
WITH cte AS MATERIALIZED (SELECT ...)

-- 强制内联（每次引用都重新计算）
WITH cte AS NOT MATERIALIZED (SELECT ...)

PostgreSQL 12+ 默认行为：被引用一次的 CTE 自动内联，多次引用的 CTE 自动物化。

五、递归 CTE 的安全性​

防止无限递归

递归 CTE 如果数据有循环引用（如 A→B→C→A），会无限循环。

防护手段：

WITH RECURSIVE cte AS (
  SELECT id, parent_id, 1 AS depth, ARRAY[id] AS visited
  FROM nodes WHERE id = 1
  
  UNION ALL
  
  SELECT n.id, n.parent_id, c.depth + 1, c.visited || n.id
  FROM nodes n JOIN cte c ON n.parent_id = c.id
  WHERE n.id != ALL(c.visited)  -- 防止循环
    AND c.depth < 100            -- 深度限制
)
SELECT * FROM cte;

常见面试问题​

Q1: CTE 一定比子查询好吗？​

答案：
不一定。在 PostgreSQL 12 之前，CTE 会被强制物化，某些场景比子查询慢。12+ 版本优化器会自动选择。

一般来说：

  • 需要递归 → CTE
  • 同一结果集引用多次 → CTE
  • 简单的一次性子查询 → 子查询即可
  • 复杂分步逻辑 → CTE 可读性好

Q2: MySQL 支持 CTE 吗？​

答案：
MySQL 8.0 开始支持 CTE（包括递归 CTE），但不支持可写 CTE（CTE 中不能包含 INSERT/UPDATE/DELETE）。

Q3: 递归 CTE 有性能问题吗？​

答案：

  • 递归 CTE 每轮产生中间结果集，深度很大时内存消耗高
  • 没有索引优化，每轮递归都是遍历
  • 超大树形结构考虑使用闭包表（Closure Table）或路径枚举方案替代

相关链接​