罗罗编程学习记录
本页内容 展开查看

05.7.3 CTE 与递归查询

所属:全栈开发面试题库 / 05 数据库与存储 / 05.7 SQL 语言与查询技巧

覆盖范围

  • WITH 子句与普通 CTE 的作用、可读性
  • CTE 与子查询、临时表的区别
  • 递归 CTE(WITH RECURSIVE)的锚点与递归成员
  • 层级树、组织结构、菜单路径展开
  • 环路检测与深度限制
  • 物化 CTE(MATERIALIZED)与执行次数

高频题速览

编号 题目 难度 频率
05.7.3-001 CTE 是什么?和子查询有什么区别 基础 ★★★★★
05.7.3-002 递归 CTE 的两个组成部分 进阶 ★★★★★
05.7.3-003 怎么查组织结构/菜单树 进阶 ★★★★
05.7.3-004 递归查询如何防止环路死循环 高级 ★★★★
05.7.3-005 CTE 会被执行多次吗 进阶 ★★★
05.7.3-006 递归 CTE 的深度与性能限制 高级 ★★★

递归 CTE 结构主线

WITH RECURSIVE cte AS (
  锚点成员(SELECT 起点行)
  UNION ALL
  递归成员(引用 cte 自身,向深层迭代)
)
SELECT * FROM cte;

05.7.3-001 CTE 是什么?和子查询有什么区别?

频率 ★★★★★ | 难度 基础

一句话结论

CTE(Common Table Expression,WITH 子句)是给子查询起一个名字的临时结果集,作用域仅限当前语句;它提升可读性、可在主查询中多次引用同一个 CTE,但语义上通常等价于把子查询内联,是否物化取决于数据库实现。

概念解释

  • CTE:WITH 定义的命名临时结果集。
  • 子查询:嵌套在 FROM/WHERE 中的查询。
  • 复用性:CTE 可在主查询中被多次引用,子查询需重复书写。

代码 / 示例

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

常见误区

  • 误区:CTE 一定比子查询快。正解:多数数据库 CTE 默认被内联展开,性能等价;只有显式物化(如 PG MATERIALIZED)或递归 CTE 才有额外语义。
  • 误区:CTE 是临时表。正解:CTE 只在单条语句内有效,语句结束即销毁,不持久化。

追问

  • 问:PostgreSQL 的 MATERIALIZED 关键字是干什么的?答:强制物化 CTE 结果,阻止优化器内联,可用于稳定统计信息或避免重复计算。

05.7.3-002 递归 CTE 由哪两部分组成?

频率 ★★★★★ | 难度 进阶

一句话结论

递归 CTE 由锚点成员(anchor,SELECT 起点行)和递归成员(recursive,引用 CTE 自身的迭代查询)用 UNION ALL 连接,数据库反复执行递归成员直到返回空集,把每轮结果追加。

概念解释

  • 锚点成员:递归起点,不引用自身。
  • 递归成员:引用 CTE 名字,每轮对上一轮结果迭代。
  • 终止条件:递归成员返回空时停止。

代码 / 示例

-- PostgreSQL:WITH RECURSIVE
-- MySQL 8/SQL Server:WITH(无需 RECURSIVE 关键字,数据库自动识别)
WITH RECURSIVE org_tree AS (
  -- 锚点:从顶级员工开始
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- 递归:找下一级
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e JOIN org_tree o ON e.manager_id = o.id
)
SELECT * FROM org_tree;

常见误区

  • 误区:递归 CTE 里用 UNION 也行。正解:UNION 会去重,可能提前终止循环;迭代用 UNION ALL,去重交给外层。
  • 误区:MySQL 也要写 WITH RECURSIVE。正解:MySQL 8 直接用 WITH RECURSIVE;SQL Server 用 WITH(关键字自动识别);PG 必须写 RECURSIVE。

追问

  • 问:递归成员为什么要 JOIN 自身?答:通过 cte JOIN 表 把上一轮结果与下一层数据连接,逐层展开。

05.7.3-003 怎么用递归 CTE 展开组织结构或菜单树?

频率 ★★★★ | 难度 进阶

一句话结论

以根节点为锚点,通过自连接 manager_id = parent_id 逐层向下递归,记录 level 深度和 path 路径字符串,即可在单条 SQL 里展开任意层级的组织树或菜单树。

代码 / 示例

WITH RECURSIVE menu_tree AS (
  SELECT id, name, parent_id, 1 AS lvl,
         CAST(name AS VARCHAR(1000)) AS path
  FROM menus
  WHERE parent_id IS NULL
  UNION ALL
  SELECT m.id, m.name, m.parent_id, mt.lvl + 1,
         CAST(mt.path || ' > ' || m.name AS VARCHAR(1000))
  FROM menus m JOIN menu_tree mt ON m.parent_id = mt.id
)
SELECT lvl, path FROM menu_tree ORDER BY path;

-- 输出:
-- 1 系统管理
-- 2 系统管理 > 用户管理
-- 3 系统管理 > 用户管理 > 新增用户

常见误区

  • 误区:path 拼接不做类型转换。正解:递归列类型要一致,PG 用 CAST,MySQL 用 CONVERT,否则列长度不匹配报错。
  • 误区:层级深度不限。正解:实际数据树很深时递归会跑很多轮,需加深度限制或改用专用图存储。

05.7.3-004 递归查询如何防止环路死循环?

频率 ★★★★ | 难度 高级

一句话结论

在递归 CTE 中维护一个已访问路径(数组或 path 字符串),每次递归判断新节点是否已在路径中,若已存在则停止;同时设置最大深度限制作为兜底。

概念解释

  • 环路:数据中出现 A -> B -> A 或自引用错误时,递归会无限循环。
  • 路径集合:用数组累积已访问 id,递归时 WHERE id NOT IN (path)。
  • 深度兜底:level > N 时停止。

代码 / 示例

WITH RECURSIVE walk AS (
  SELECT id, name, manager_id, 1 AS lvl,
         ARRAY[id] AS visited
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, w.lvl + 1,
         w.visited || e.id
  FROM employees e JOIN walk w ON e.manager_id = w.id
  WHERE e.id <> ALL(w.visited)   -- 环路检测
    AND w.lvl < 20                -- 深度兜底
)
SELECT * FROM walk;

常见误区

  • 误区:数据库会自动检测环路。正解:标准递归 CTE 不自动检测,出现环路会一直跑到语句超时或达到递归上限。
  • 误区:只加深度限制就够了。正解:深度限制会在循环到上限时报错,环路检测才能正常跳过坏数据。

追问

  • 问:Oracle 的 CONNECT BY NOCYCLE 呢?答:Oracle 有 CONNECT BY NOCYCLE + CONNECT_BY_ISCYCLE 专门处理环路;递归 CTE 需手动实现。

05.7.3-005 CTE 在主查询中被引用多次,会执行几次?

频率 ★★★ | 难度 进阶

一句话结论

取决于数据库:PostgreSQL 旧版 CTE 是优化屏障(物化一次),主查询引用多次只算一次;MySQL/SQL Server 多把 CTE 内联展开,引用几次就内联几次;PG 12+ 可内联,用 MATERIALIZED 强制物化。

概念解释

  • 内联展开:把 CTE 像子查询一样嵌入主查询,可能多次执行。
  • 物化:先算出 CTE 结果存临时集,主查询多次引用都读这个集。

代码 / 示例

WITH heavy AS (SELECT ... FROM big_table WHERE ...)
SELECT * FROM heavy a JOIN heavy b ON a.id = b.parent_id;
-- PG 12+:优化器决定是否内联;强制物化用 MATERIALIZED
WITH heavy AS MATERIALIZED (SELECT ...)

常见误区

  • 误区:CTE 一定只算一次。正解:跨库行为不一致,重查询要关注执行计划是否重复扫描基表。

05.7.3-006 递归 CTE 有什么深度与性能限制?

频率 ★★★ | 难度 高级

一句话结论

递归 CTE 是逐轮迭代执行,深度大时轮次多、性能差;数据库有递归深度上限(如 PostgreSQL max_recursion_depth、MySQL cte_max_recursion_depth),超限制报错;超深图关系应改用图数据库或专用递归存储。

概念解释

  • 逐轮迭代:每轮一次 JOIN,N 层树需 N 轮。
  • 深度上限:默认值较小(MySQL 默认 1000),需按业务调整。
  • 适用边界:适合树/层级(浅深),不适合任意图遍历。

代码 / 示例

-- MySQL 查看/调整递归深度上限
SHOW VARIABLES LIKE 'cte_max_recursion_depth';
SET cte_max_recursion_depth = 10000;

常见误区

  • 误区:递归 CTE 能替代图数据库。正解:多跳图查询(最短路径、推荐)用递归 CTE 会指数膨胀,应改用 Neo4j 等。

追问

  • 问:递归 CTE 能不能做最短路径?答:理论可行但会枚举所有路径,性能差,生产不建议。

05.7.3-007 CTE 和临时表区别?

频率 ★★ | 难度 进阶

一句话结论

CTE 只在单条语句内有效,不持久化;临时表(CREATE TEMP TABLE)在会话内有效,可多次查询和索引;复杂中间结果用临时表。


05.7.3-008 递归 CTE 能做什么业务?

频率 ★★ | 难度 进阶

一句话结论

组织架构树、菜单树、账单分层、物料 BOM 展开、路径枚举;不适合任意图遍历和最短路径。


05.7.3-009 多个 CTE 能互相引用吗?

频率 ★★ | 难度 基础

一句话结论

可以,WITH a AS (...), b AS (...引用 a...) 链式定义;b 在 a 之后定义即可。

面试高频追问

问:CTE 会被执行几次?

答:取决于数据库。PG 旧版 CTE 是优化屏障(物化一次),MySQL/SQL Server 多内联展开;引用多次可能内联多次。

问:递归 CTE 为什么会死循环?

答:数据有环路(A->B->A)时递归无限执行;标准 CTE 不自动检测,要手动维护已访问路径。

问:递归 CTE 适合什么业务?

答:组织架构树、菜单树、BOM 展开;不适合任意图遍历和最短路径,那应该用图数据库。

问:多个 CTE 能互相引用吗?

答:可以,WITH a AS (...), b AS (...引用 a...) 链式定义。

核心要点速记

概念 说明
CTE WITH 子句,单语句有效
递归 CTE 锚点成员 + 递归成员,UNION ALL
环路检测 维护已访问路径,防止死循环
物化 CTE PG MATERIALIZED 强制物化

适用场景:组织树、菜单树、BOM 展开;不适合任意图遍历。

一句话记忆:递归 CTE = 锚点 + 递归;环路检测是必须的。

相关概念辨析

CTE vs 临时表

  • CTE:单条语句内有效,不持久化,无统计信息,优化器重写。
  • 临时表:会话内有效,可多次查询,可建索引,有统计信息。
  • 选择:单条查询用 CTE;多步中间结果用临时表。

递归 CTE vs 层级查询

  • 递归 CTE:标准 SQL,通用,跨库支持。
  • Oracle CONNECT BY:Oracle 专有语法,性能好但不通用。
  • 选择:通用场景用递归 CTE;Oracle 优化用 CONNECT BY。

05.7.3-009 递归 CTE 如何处理环路检测?

频率 ★★ | 难度 进阶

一句话结论

递归 CTE 用路径数组或 visited 集合记录已访问节点,遇到重复节点即停止,避免环导致无限递归。

概念解释

  • 环路:层级数据中 A->B->C->A 形成环,普通递归 CTE 会无限循环。
  • 检测手段:在递归部分记录路径(如 path = parent || ',' || child),新节点已在路径中则排除。

原理流程

  1. 锚点成员取根节点,path 初始化为节点 ID。
  2. 递归成员连接子节点,WHERE 条件排除 path 中已存在的节点。
  3. 数据库达到递归深度上限或无新行时停止。

代码 / 示例

WITH RECURSIVE org_tree AS (
  SELECT id, parent_id, name, CAST(id AS CHAR(100)) AS path
  FROM org WHERE parent_id IS NULL
  UNION ALL
  SELECT o.id, o.parent_id, o.name,
         CONCAT(t.path, ',', o.id)
  FROM org o JOIN org_tree t ON o.parent_id = t.id
  WHERE FIND_IN_SET(o.id, t.path) = 0  -- 环检测
)
SELECT * FROM org_tree;

常见误区

  • 误区:UNION 能自动去重防环。正解:UNION 按所有列去重,path 不同不会去重,仍需显式检测。

追问延伸

  • MySQL 递归深度上限:cte_max_recursion_depth 默认 1000。
  • 环路问题:生产中应在数据层保证无环,CTE 检测只是兜底。

待整理清单

  • [ ] LATERAL 子查询与 CTE 的配合
  • [ ] 递归 CTE 在 MySQL/PG/SQL Server/Oracle 的语法差异对照表
  • [ ] 树形结构的物化路径、闭包表、枚举路径等其他建模方案