递归公共表表达式（CTE）在SQL中允许你通过自引用查询以集基方式遍历层次化数据。结构由一个锚定成员（基本案例，通常是manager_id IS NULL的根节点）和一个递归成员（将CTE与自身根据父子关系连接的迭代部分）组成。数据库引擎重复执行递归成员，直到不再返回新行，逐步构建结果集，而无需显式的迭代构造。
这种方法利用了SQL的声明性特性，允许优化器选择高效的连接算法（通常是哈希或合并连接），而不是CURSOR或WHILE循环固有的逐行处理。语法在PostgreSQL和MySQL中使用WITH RECURSIVE，在SQL Server中则简单使用WITH（其中递归是隐式的），随后是CTE名称和列列表。
一家拥有12,000名员工的跨国公司需要生成完整的报告链以满足SOX合规审计的要求。现有系统使用一个T-SQL CURSOR迭代每个员工，递归调用标量函数来查找他们的经理，并逐步构建层次结构。这个过程耗时47分钟，持有Employees表的锁，阻止HR在工作时间进行更新，并且在处理超过100层的深层次结构时，常常因堆栈溢出错误而失败（这种情况在工程部门的矩阵结构中很常见）。
解决方案A：优化的CURSOR与临时表。 团队考虑重写游标，先将结果转储到临时表中，然后在最后进行批量插入。这将把锁定时间从47分钟降低到大约40分钟。优点：代码变化最小，遗留团队熟悉的模式。缺点：仍然是逐行处理，仍然容易出现深层递归堆栈溢出，仅仅缓解而非解决性能问题。
解决方案B：HierarchyID数据类型。 迁移表以使用SQL Server的原生HierarchyID类型，该类型将树位置存储为优化遍历的编码路径。优点：O(1)子树检索，内置方法如GetAncestor()和GetDescendant()，对于读密集型工作负载极快。缺点：需要大规模的模式迁移（12,000行加上历史数据），在重组期间维护复杂（更新一个经理需要重新计算所有后代路径），由于公司考虑迁移到PostgreSQL，被锁定在SQL Server上。
解决方案C：带循环检测的递归CTE**。** 实现一个递归CTE，将员工表与自身根据manager_id连接，使用路径数组来跟踪访问的节点，以防止由于循环引用（因数据输入错误而发生过两次）导致的无限循环。优点：纯ANSI SQL标准（在迁移期间可移植到PostgreSQL），集基执行将运行时间减少到4分钟12秒，执行期间没有持有表锁（使用快照隔离），处理任意深度而不造成堆栈溢出，自动检测数据质量问题（循环）。
团队选择了解决方案C。实现使用递归CTE，带有一个path列，在PostgreSQL的数组聚合（或者在SQL Server中的字符串连接）中累积员工ID，带有WHERE子句检查新的manager_id是否不存在于累积路径中。结果是91%的性能提升，消除了生产锁定，提前检测到之前导致应用程序崩溃的循环报告关系。
为什么递归CTE会终止，如果数据包含循环引用会发生什么？
候选人常常认为递归CTE有内置的循环检测，但标准的SQL递归仅在递归成员返回零新行时终止。如果员工A报告给B，B报告给C，而C又返回给A，则CTE会无限运行（或直到达到实现限制，例如SQL Server默认的100递归级别）。解决方案需要通过在路径列中累积已访问的节点ID（使用数组或分隔字符串）进行手动循环检测，并筛选WHERE new_id != ALL(path_array)。现代PostgreSQL（14+）和SQL Server（2022+）支持标准的SQL:1999 CYCLE子句：WITH RECURSIVE cte AS (...) CYCLE id SET is_cycle USING path，这会自动处理此问题。
递归CTE与基于游标的方法之间的执行计划有何不同，这对并发有什么影响？
初级候选人常常声称CTE“更快”，却没有理解执行模型。在SQL Server或PostgreSQL中，CURSOR迫使引擎物化结果集并逐行迭代，通常使用Keyset或Static游标类型，这要求在迭代期间对所需的锁或tempdb资源进行锁定。这在上述例子中会在整个47分钟的过程中在底层表上创建共享锁（或更新锁）。相反，递归CTE是一个单一的SELECT语句。在读取已提交快照隔离（RCSI）或快照隔离下，它读取数据的一致时间点视图而不持有锁，使用版本存储来替代。优化器通常会选择对递归成员的嵌套循环连接，并对父子索引进行索引查找操作，使其为O(n log n)，而不是游标方法的O(n²)。
递归CTE和嵌套集合模型用于层次数据之间有什么不同？你会在何时选择其中一种？
候选人经常混淆遍历方法与存储模型。递归CTE是一种查询时间的遍历技术，适用于邻接列表（parent_id外键）。嵌套集合模型（左/右值）是一种存储设计模式，预先计算树遍历路径。对于写密集型工作负载（频繁的经理更改），使用邻接列表的递归CTE*更优，因为更新单个parent_id的复杂度为O(1)，而嵌套集合则要求对所有右值的更新复杂度为O(n)。对于读密集型、静态层次结构（每月变化的组织图），嵌套集合提供O(1)子树检索（WHERE left BETWEEN parent.left AND parent.right）对比递归CTE的O(n)。混合方法使用闭合表（单独的表存储所有祖先-后代对），这在遍历和查找所有子节点时提供O(1)，但成本是O(n²)的存储和更复杂的维护。选择取决于读/写比率：当写入 > 5% 的操作时使用递归CTE；对于主要静态树则使用嵌套集合或闭合表。