什么是 CTE？递归 CTE 的执行原理是什么？

简化版

CTE 是用 WITH 为单条语句中的中间结果命名，主要用于拆分复杂查询和复用逻辑；递归 CTE 由锚点成员与递归成员组成，反复用上一轮产生的行生成下一轮，直到不再产生新行。CTE 是否物化不是固定规律，取决于数据库、版本和查询结构。

详细版

普通 CTE：

WITH department_salary AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employee
  GROUP BY department_id
)
SELECT * FROM department_salary WHERE avg_salary >= 10000;

递归 CTE 通常形如：

WITH RECURSIVE organization AS (
  SELECT id, manager_id, name, 0 AS depth
  FROM employee
  WHERE id = 100

  UNION ALL

  SELECT e.id, e.manager_id, e.name, o.depth + 1
  FROM employee AS e
  JOIN organization AS o ON e.manager_id = o.id
)
SELECT * FROM organization;

第一部分找到根节点，第二部分不断找到下一层下属。真实数据若可能成环，需要增加防环或深度限制，不能只依赖递归最终自然停止。

完整版教学

一、普通 CTE 解决什么问题

CTE（Common Table Expression，公共表表达式）存在于一条语句的作用域中，可以像表一样被后续查询引用。它不会创建持久表，主要价值是给中间结果起名，把嵌套很深的 SQL 拆成可阅读的步骤。

CTE 也不是性能优化开关。优化器可能把它合并进外层查询，也可能物化成中间结果；同一个 CTE 被多次引用是否重复计算，同样依赖具体数据库实现与版本。

二、递归 CTE 的两个成员

递归 CTE 至少包含：

锚点成员：产生初始结果，例如组织树的根节点；
递归成员：引用 CTE 自身，根据上一轮结果产生下一轮；
集合运算：通常用 UNION ALL 连接两部分。

RECURSIVE 描述的是迭代求值，不是函数调用栈式递归。数据库内部通常维护工作表：先放入锚点结果，再反复处理当前工作集，直到下一轮为空或达到限制。

三、为什么通常使用 UNION ALL

UNION ALL 保留所有生成行，不需要每轮全局去重，语义和开销都更直接。UNION 会去重，有时能抑制完全相同的重复行，但不能替代可靠的环检测：只要路径、深度等列不同，同一节点仍可能不断出现。

选择哪一种取决于结果是否允许重复以及递归关系的性质，不能为了“防死循环”机械地改成 UNION。

四、如何防止环和无限递归

组织关系、图关系可能存在脏数据，例如 A 的上级是 B，B 的上级又指向 A。常见防护包括：

维护已访问节点路径，下一轮排除已出现的 id；
增加合理的最大深度条件；
在写入阶段用约束或业务校验避免环；
了解目标数据库的递归深度限制和报错行为。

只加深度上限能阻止无限运行，但会静默截断过深的合法树，因此应同时监控或暴露异常数据。

五、层级、路径和遍历顺序

递归成员可以累加 depth，也可以拼接路径用于展示或防环。但字符串拼路径的语法、数组类型和搜索顺序扩展在不同数据库间差异较大。

最终输出顺序仍需 ORDER BY。递归产生行的内部顺序不构成 SQL 结果顺序保证；要做稳定的深度优先或广度优先展示，需要显式构造可排序字段或使用目标数据库提供的相关能力。

六、CTE、派生表和临时表怎么选

一次查询内拆分逻辑：CTE 或派生表；
需要递归遍历：递归 CTE；
中间结果要跨多条语句复用、建立索引或分阶段处理：临时表可能更合适；
性能敏感：比较执行计划和实际耗时，不根据语法外形判断是否物化。

七、常见误区与追问

结构	作用	面试重点
锚点成员	产生第 0 层或初始行	决定递归从哪里开始
递归成员	引用 CTE 自身扩展下一层	必须能逐轮逼近停止条件
`UNION ALL`	合并每轮结果	不负责天然防环
深度/路径列	记录层级和访问轨迹	用于排序、展示、防环

误区：CTE 一定会物化成临时结果。 是否物化取决于数据库、版本、优化器规则和查询形态；有的会内联，有的会物化，有的还支持显式提示。
误区：递归 CTE 就是程序语言里的函数递归。 SQL 递归 CTE 更像“工作表迭代”：锚点先产出一批行，递归成员再基于上一轮结果继续扩展。
误区：把 UNION ALL 改成 UNION 就能可靠防环。 UNION 只能去掉整行完全相同的重复；如果路径、深度等列不同，同一节点仍可能反复出现。
追问：递归 CTE 什么时候会停止？ 当递归成员不再产生新行，或达到数据库的递归深度、资源限制、用户写的深度条件时停止。
追问：如何查询组织树并避免环？ 常见做法是携带 depth 和已访问路径，递归成员中排除已经出现在路径里的节点，同时设置合理最大深度。
追问：CTE、派生表、临时表怎么取舍？ 单条 SQL 内提升可读性优先 CTE；只局部嵌套可用派生表；跨多条语句复用、需要索引或分阶段处理时才考虑临时表。

记忆钩子：普通 CTE 是“命名中间结果”，递归 CTE 是“锚点起步、工作集一轮轮扩展、直到没有新行”。

八、加强记忆

普通 CTE 是“给本条语句的中间结果起名字”，递归 CTE 是“锚点先播种，递归成员一轮轮扩展，直到新结果为空”。UNION ALL 不负责防环，路径、已访问集合和深度限制才是安全措施；是否物化必须看具体数据库与执行计划。

简化版

详细版