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),新节点已在路径中则排除。
原理流程
- 锚点成员取根节点,path 初始化为节点 ID。
- 递归成员连接子节点,WHERE 条件排除 path 中已存在的节点。
- 数据库达到递归深度上限或无新行时停止。
代码 / 示例
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 的语法差异对照表
- [ ] 树形结构的物化路径、闭包表、枚举路径等其他建模方案