想象你是一位族谱管理员，面对一本厚达 500 页的老家谱。有人问你：“帮我把张三爷的所有后代找出来，按辈分排好。”你不可能每翻一页就停下记一个名字再从头翻——那效率太低。真正的做法是：从怀里掏出一本空白便签簿，把“张三”写在第一页；然后翻家谱，找到张三的儿女，记在第二页；再翻家谱，找到这些儿女的儿女，记在第三页……如此一页页地“按代翻查”，直到某一页再也写不下新名字才停笔。这便签簿就是 CTE，按代翻查的过程就是递归。PostgreSQL 的 CTE 递归查询，正是把这个“便签簿 + 逐代展开”的思维搬进了数据库，让你用一条 SQL 就能优雅地遍历树形与图状数据。

岗位关联

对 AI 从业者而言，掌握递归 CTE 意味着你能用一条 SQL 完成组织架构分析、知识图谱可达性计算、BOM 物料树拆解等高频数据处理任务，是数据工程师从“单表操作者”跃升为“复杂数据结构驾驭者”的分水岭技能。

目录
  1. 可视化演示
  2. CTE基础概念
  3. 递归CTE原理
  4. 迭代终止机制
  5. 树形查询应用
  6. 图状查询应用
  7. 物化CTE优化
  8. AI 应用场景
  9. Python 演示
  10. 常见误区
  11. 面试题

可视化演示

展示系统处理流水线、实时性能指标曲线及多方案对比分析，包含数据流转、吞吐延迟监控与方案评估。

本节配有交互动效（进入视口后自动加载，约 3KB）。

系统可视化 处理流程：输入→处理→模型→输出 性能监控：实时指标 P=L×T

CTE基础概念

想象你在整理书房：杂乱的书桌上堆着三十本书，你要反复从里面查某个作者的所有著作。你会选择什么？大概率是先拿一张便签纸，把这位作者的书名抄下来贴在桌角，之后每次查都只看便签纸。CTE 正是这样一张“SQL 便签纸”——用 WITH 子句把一段子查询的结果存成一个临时结果集，并给它起个名字，供后续主查询反复引用。

技术上看，CTE（Common Table Expression，通用表表达式）是 SQL 中一种命名子查询的机制。它的核心语法是在外部查询之前用 WITH 定义临时结果集，外部查询可以像使用普通表一样使用这个名字。与内联子查询相比，CTE 的最大优势是可读性：你不再需要在 FROM 里堆砌一长串子查询，而是把复杂逻辑拆解为多个命名清晰的“便签纸”，逐个阅读即可。

从执行层面讲，PostgreSQL 会把 CTE 优化为临时结果集甚至内联展开。CTE 的分析树会被缓存，同一个 CTE 名称在一条语句中被多次引用时，数据库可以复用计算结果，避免重复扫描基表。这让 CTE 不仅是语法糖，更是性能优化的重要工具。

回到书房的比喻：有了便签纸，你不需要在每次查找时重新翻开三十本书。CTE 也一样——复杂逻辑被预先计算、命名，主查询变得干净可读，而且多次引用时数据库有机会复用结果。

WITH cte AS (子查询S)SELECT ⋯ FROM cte\texttt{WITH } cte \texttt{ AS (} \text{子查询} S \texttt{)} \quad \texttt{SELECT } \cdots \texttt{ FROM } cte
这条式子描述的是 CTE 的基本骨架：先用 WITH 定义一个临时结果集，再在主查询中把它当表用。
  1. WITH 关键字声明一个 CTE 定义开始，相当于“我拿一张便签纸开始记”。
    WITH\texttt{WITH}
  2. cte 名称标识给临时结果集起的名字，后续查询用这个名字引用它。
    ctecte
  3. AS 子查询体定义这张便签纸上要抄什么，即实际的计算逻辑。
    AS (S)\texttt{AS (} S \texttt{)}
  4. 外层主查询引用将 CTE 作为数据源，像查普通表一样查询便签纸上的内容。
    SELECT ⋯ FROM cte\texttt{SELECT } \cdots \texttt{ FROM } cte

递归CTE原理

族谱的妙处在于：你不需要一次性把所有后代都列出来，只要记住两条规则——“祖先是谁”和“如何找孩子”。递归 CTE 正是把这两条规则翻译成 SQL。锚点成员是那个“最早的祖先”，递归成员是“找孩子”的规则，两者用 UNION ALL 连接，数据库便自动开始逐代迭代。

语法上，递归 CTE 与普通 CTE 的唯一区别是多了 RECURSIVE 关键字。一个递归 CTE 由两部分组成：

  • 锚点成员（Anchor Member）：递归的起点，一般是一个非递归的 SELECT，为结果集提供第一代数据。
  • 递归成员（Recursive Member）：引用了 CTE 自身的 SELECT，描述如何从“上一轮结果”生成“下一轮新行”。

PostgreSQL 在执行递归 CTE 时，会维护一个“工作表”（working table）：每轮迭代先用递归成员基于当前工作表计算新行，把新行追加到结果集中，同时替换工作表的内容，进入下一轮。直到某轮产生的新行为空，迭代停止。UNION ALL 保证所有行原样保留，不做去重——因为树形数据中不同路径可能指向同一个节点，去重会破坏递归语义。

回到族谱场景：你不需要把五千个人的名字一次背下来，只要知道“开山祖是谁”和“孩子该怎么找”，数据库替你代代相传地跑下去。这就是递归 CTE 的优雅之处——把无限复杂的层级问题，压缩成两个 SELECT 加一个关键字。

WITH RECURSIVE cte AS (A UNION ALL T) SELECT ⋯\texttt{WITH RECURSIVE } cte \texttt{ AS (} A \ \texttt{UNION ALL} \ T \texttt{)} \texttt{ SELECT } \cdots
锚点成员 A 给出初始数据，递归成员 T 引用自身逐轮产生新行，UNION ALL 把所有轮次的数据合并进最终结果。
  1. RECURSIVE 关键字让 PostgreSQL 知道这个 CTE 允许自引用，开启递归模式。
    RECURSIVE\texttt{RECURSIVE}
  2. 锚点成员 A第一轮返回的起始行，相当于族谱中的始祖。
    A:初始结果集A: \text{初始结果集}
  3. 递归成员 T核心迭代规则——从 cte 当前的“新行”出发，算出它们的子代。
    T:从 cte 上一轮新行计算子行T: \text{从 } cte \text{ 上一轮新行计算子行}
  4. UNION ALL 连接把锚点结果与每一轮新行全部合并，保留重复行以避免破坏层级语义。
    UNION ALL\texttt{UNION ALL}

迭代终止机制

家谱有断代的时候——某个分支没有后代，这条线就到底了。递归 CTE 同样如此：当某一轮递归成员返回 0 行时，就再也找不到新名字，数据库自动停笔。终止不是人为设置的“最多翻三遍”，而是数据本身的自然边界。

形式上，递归 CTE 的迭代过程可以记为一个单调递增的集合并：

当某一轮 $T(R^{(n)}) = \varnothing$ 时，迭代停止，最终结果为 $R^{(n)}$。

因此，保证递归能终止的第一个条件就是数据中不存在“循环引用”——即不存在这样的行：A 是 B 的上级，B 又是 A 的上级。对于树形数据（如组织架构），天然满足无环条件；但对于图状数据（如 BOM 中零件互相引用），则需要主动防护。常见做法是给递归成员添加深度上限条件，或用数组记录已访问路径来检测环。

另一个需要注意的性能陷阱是：每轮迭代 PostgreSQL 会对基表执行一次新的扫描，因此递归 CTE 的总代价 ≈ 迭代轮数 × 每轮扫描代价。如果递归深度过大、基表没有合适的索引，查询可能非常慢。这也是为什么“空结果终止”虽然是正确性保障，但实际工程中往往还要加上显式深度限制作为“安全阀”。

这就像族谱管理员翻到第 7 页时发现“这页没人可记了”——不是他累了，而是血脉真的到头了。数据分析师在设计递归查询时，也要像管理员一样心里有数：数据有没有可能成环？最深的层级到底有多深？给递归加上“深度护栏”永远是稳妥的做法。

R(0)=A,R(i)=R(i−1)∪T(R(i−1))R^{(0)} = A, \qquad R^{(i)} = R^{(i-1)} \cup T(R^{(i-1)})
T(R(n))=∅⇒迭代停止T(R^{(n)}) = \varnothing \Rightarrow \text{迭代停止}
递归终止的唯一判定标准：某轮递归成员没有产生任何新行。数据自然边界 + 深度保护 = 安全的递归。
  1. 初始结果第一轮结果集等于锚点成员给出的起始集合。
    R(0)=AR^{(0)} = A
  2. 递推关系每轮把递归成员基于上一轮新行产生的结果并入总集。
    R(i)=R(i−1)∪T(R(i−1))R^{(i)} = R^{(i-1)} \cup T(R^{(i-1)})
  3. 终止判定当某轮递归成员返回空集，说明所有路径走到尽头。
    T(R(n))=∅T(R^{(n)}) = \varnothing
  4. 最终结果所有轮次结果的并集，就是完整的层级数据。
    Rfinal=R(n)R_{\text{final}} = R^{(n)}

树形查询应用

组织架构是递归 CTE 最典型的用武之地。假设你有一张员工表，每行包含 emp_id、name、manager_id（上级编号），CEO 的 manager_id 是空。要找出“张三下属的所有层级员工”——不用递归 CTE，你得写十几行自连接或者写存储过程循环；有了递归 CTE，一条 SQL 就搞定：

这里锚点成员选出根节点（CEO），递归成员通过 JOIN 把每个员工的直接下属逐层“拽”进来，depth 字段在每一代 +1，标记当前层级。这条查询的结果就是整棵组织树的有序展开。进一步地，你还可以在递归成员中拼接路径字段（如 t.path || ' -> ' || e.name），输出从 CEO 到每个人的完整汇报链路。

组织架构查询让“找下属遍数”从 N 次手动查询变成 1 条递归 SQL。族谱管理员翻完整个家族只花了一杯茶的时间——数据库帮你把茶喝着，把活干完了。

σmanager_id=cte.emp_id(employees)\sigma_{\text{manager\_id} = \text{cte}.\text{emp\_id}}(\text{employees})
树形查询的本质：每一轮递归成员做一次等值连接——员工的 manager_id 等于当前层员工的 emp_id。
  1. 锚点根节点选出没有上级的 CEO，作为深度 1 的根。
    WHERE manager_id IS NULL\text{WHERE } \text{manager\_id} \text{ IS NULL}
  2. 递归连接条件当前层员工与基表按“上级=当前层”连接，找到所有直属下级。
    JOIN org_tree t ON e.manager_id=t.emp_id\text{JOIN } \text{org\_tree } t \text{ ON } e.\text{manager\_id} = t.\text{emp\_id}
  3. 深度累加每进入下一层，depth 在前一层基础上加一。
    depth=t.depth+1\text{depth} = t.\text{depth} + 1
sql9 行
WITH RECURSIVE org_tree AS (  SELECT emp_id, name, manager_id, 1 AS depth  FROM employees WHERE manager_id IS NULL  UNION ALL  SELECT e.emp_id, e.name, e.manager_id, t.depth + 1  FROM employees e  JOIN org_tree t ON e.manager_id = t.emp_id)SELECT * FROM org_tree ORDER BY depth, emp_id;

图状查询应用

BOM（Bill of Materials，物料清单）是制造业中递归 CTE 的经典场景：一辆整车由发动机、底盘等总成组成，发动机又由活塞、曲轴等零件组成。要计算“生产一辆整车需要多少个活塞”，必须沿着物料树从根到叶子逐层展开，每层乘以装配数量。

假设物料表结构为 product_tree(part_id, parent_id, qty)，其中 qty 表示“组装 1 个父件需要几个本件”。递归 CTE 可以这样拆解：

注意这里的 total_qty 是沿路径逐层累乘的结果：车→发动机→活塞，每一级乘上对应的装配数量，最终得到“整车需要 4 个活塞”。与树形查询不同，BOM 数据可能存在多父节点（一个零件被多个总成复用）甚至环（物料互相引用），因此工程中必须在递归成员里加上环检测——通常用数组记录访问过的 part_id：

在制造业 ERP 系统中，这个查询替代了原来几十行的存储过程递归代码。族谱管理员如果发现有人“过继+回嗣”导致同一祖先出现了两条血脉线，也会在笔记本上标记“此人已记录”——BOM 的环检测正是同一个思路。

total_qty叶子=∏i=1深度qtyi\text{total\_qty}_{\text{叶子}} = \prod_{i=1}^{\text{深度}} \text{qty}_i
BOM 拆解的数学本质：沿每条“父→子”路径做数量连乘，所有路径的结果再汇总。
  1. 路径数量乘积每层数量是上一层累计量与当前层装配量的乘积。
    total_qty=b.total_qty×p.qty\text{total\_qty} = b.\text{total\_qty} \times p.\text{qty}
  2. 层级深度BOM 每展开一层，层级计数加一。
    lvl=b.lvl+1\text{lvl} = b.\text{lvl} + 1
  3. 汇总求和所有路径的叶子累计用量相加，得到最终需求总量。
    SUM(total_qty) GROUP BY part_id\text{SUM}(\text{total\_qty}) \text{ GROUP BY } \text{part\_id}
sql9 行
WITH RECURSIVE bom AS (  SELECT part_id, parent_id, qty, 1 AS lvl, qty AS total_qty  FROM product_tree WHERE parent_id IS NULL  UNION ALL  SELECT p.part_id, p.parent_id, p.qty, b.lvl + 1, b.total_qty * p.qty  FROM product_tree p  JOIN bom b ON p.parent_id = b.part_id)SELECT part_id, total_qty FROM bom;

物化CTE优化

你在整理族谱时发现：同一个家族分支被五个人同时问到，你每次都得翻同几页家谱。聪明人会把这一支的谱系复印十份，谁来就给一份——复印一次十份的成本，远低于每次现场翻查。PostgreSQL 的物化 CTE（MATERIALIZED）正是这个“复印”动作。

默认情况下，同一语句中 CTE 被多次引用时，PostgreSQL 的优化器会评估是内联展开还是物化。但你可以在定义时显式指定：

物化 CTE 会先计算一次结果并写入临时存储，后续每次引用都从临时存储读取，而不是重新执行子查询。对于计算密集、被多次引用的 CTE，这一招能显著降低总执行代价。反之，如果 CTE 只被引用一次，物化反而可能引入额外开销，此时可以用 NOT MATERIALIZED 强制内联。

回到族谱管理员：如果“同一支人被问五次”的概率极高，复印就是最优解。数据库的物化 CTE 也是同样的权衡——用一次计算换取多次快速读取，把宝贵的查询时间还给真正的业务逻辑。

C物化=Cexec+k⋅Cscan≪C不物化=k⋅CexecC_{\text{物化}} = C_{\text{exec}} + k \cdot C_{\text{scan}} \quad \ll \quad C_{\text{不物化}} = k \cdot C_{\text{exec}}
不物化时 CTE 被执行 k 次（k 为引用次数）；物化后只执行一次，后续 k 次都变成便宜的临时表扫描。
  1. 执行代价不物化时，每次引用都重新跑完整查询。
    C不物化=k⋅CexecC_{\text{不物化}} = k \cdot C_{\text{exec}}
  2. 一次计算多次读取物化后仅算一次，后续引用只是扫描缓存。
    C物化=Cexec+k⋅CscanC_{\text{物化}} = C_{\text{exec}} + k \cdot C_{\text{scan}}
  3. 收益条件当计算代价远大于扫描代价且引用次数多时，物化收益显著。
    Cexec≫Cscan,k≥2C_{\text{exec}} \gg C_{\text{scan}}, \quad k \geq 2
sql5 行
WITH RECURSIVE materialized_cte AS MATERIALIZED (  SELECT ...  -- 复杂且耗时的查询)SELECT * FROM materialized_cte aJOIN materialized_cte b ON a.id = b.parent_id;

AI 应用场景

特征工程中的层级特征提取

阶段：数据预处理 / 特征工程

解决的问题：在 AI 模型训练中，经常需要把树形结构（如用户所属组织、商品分类层级、权限等级）转换为特征。传统做法是写多层 Python 循环递归查询数据库，代码冗长且性能低下。递归 CTE 能在数据库端一次完成层级展开，将“用户ID → 其所有上级链路的深度和路径”直接输出为表格特征。

具体案例：一家金融公司的反欺诈模型需要“用户所在部门到 CEO 的路径长度”作为特征。数据团队利用递归 CTE 从组织架构表一次性算出每名用户的 depth 和 path 字段，加载到训练集进行模型训练，特征工程耗时从 6 小时降至 20 分钟。

知识图谱推理中的可达性查询

阶段：知识图谱构建 / 图谱推理

解决的问题：知识图谱中判断两个实体是否连通、找出所有可达路径，本质是图上的传递闭包计算。递归 CTE 每个递归成员对应图谱中的一跳关系，迭代 k 轮即得到 k 跳以内所有可达实体，是图谱推理引擎之外极轻量级的补充方案。

具体案例：某智能客服系统的知识图谱记录了业务概念的上下位关系（如“苹果手机”→“智能手机”→“数码产品”）。利用递归 CTE 查询“某商品的所有上位类目”，用于长尾商品的类目补全与推荐召回，查询平均耗时小于 50ms。

RAG 系统的文档树遍历

阶段：检索增强生成（RAG）索引构建

解决的问题：RAG 系统中，文档库往往按“书 → 章节 → 小节 → 段落”组织成树形。构建向量索引时，需要遍历整棵文档树并为每个节点生成 chunk 及父子路径信息。递归 CTE 能在 PostgreSQL 中直接输出带路径前缀的行，配合向量检索使用。

具体案例：某法律 AI 助手用递归 CTE 从法规数据库中提取“法律名称 → 章节 → 条款”的完整路径，每个条款向量化时附加路径上下文。用户提问“劳动合同解除条件”，系统返回条款的同时展示其层级归属，回答准确率提升 15%。

Python 演示

python79 行
"""使用 psycopg2 在 PostgreSQL 中演示 CTE 递归查询组织架构"""import psycopg2  def create_connection():    """建立 PostgreSQL 连接，请按实际环境修改参数"""    return psycopg2.connect(        dbname='company',        user='postgres',        password='password',        host='localhost',        port='5432'    )  def setup(conn):    """创建员工表并插入测试数据"""    cur = conn.cursor()    cur.execute("DROP TABLE IF EXISTS employees CASCADE;")    cur.execute("""        CREATE TABLE employees (            emp_id INT PRIMARY KEY,            name TEXT NOT NULL,            manager_id INT REFERENCES employees(emp_id)        );    """)    rows = [        (1, '张三', None),        (2, '李四', 1),        (3, '王五', 1),        (4, '赵六', 2),        (5, '孙七', 2),        (6, '周八', 3),    ]    cur.executemany(        "INSERT INTO employees VALUES (%s, %s, %s);",        rows    )    conn.commit()    cur.close()  def recursive_query(conn):    """执行递归 CTE 查询，输出全部层级的员工"""    cur = conn.cursor()    cur.execute("""        WITH RECURSIVE org_tree AS (            SELECT emp_id, name, manager_id, 1 AS depth            FROM employees            WHERE manager_id IS NULL            UNION ALL            SELECT e.emp_id, e.name, e.manager_id, t.depth + 1            FROM employees e            JOIN org_tree t ON e.manager_id = t.emp_id        )        SELECT name, depth, manager_id        FROM org_tree        ORDER BY depth, emp_id;    """)    rows = cur.fetchall()    cur.close()    return rows  def main():    conn = create_connection()    try:        setup(conn)        print("组织架构递归遍历结果：")        print("姓名 | 层级 | 上级ID")        print("---- | ---- | ------")        for name, depth, manager_id in recursive_query(conn):            print(f"{name} | {depth} | {manager_id or '无'}")    finally:        conn.close()  if __name__ == '__main__':    main()
目的：连接 PostgreSQL 数据库，创建一张简单的员工表，用递归 CTE 从 CEO 出发逐层展开整棵组织树，并打印每个员工的层级深度。
  1. CREATE TABLE employees (...)定义员工表，manager_id 自引用 emp_id，构成树形结构。
  2. rows = [(1, '张三', None), ...]测试数据：张三为 CEO，李四、王五归张三管等。
  3. WITH RECURSIVE org_tree AS (...)声明递归 CTE，org_tree 在 UNION ALL 右侧被自引用。
  4. WHERE manager_id IS NULL锚点成员：选出根节点（CEO）作为迭代起点。
  5. JOIN org_tree t ON e.manager_id = t.emp_id递归成员：把“上级属于当前层结果”的员工纳入下一轮。
  6. t.depth + 1每轮迭代深度加一，标记员工在组织树中的层级。
  7. conn.commit()提交事务，确保建表和插入数据的变更生效。

常见误区

误区递归 CTE 就是在 SQL 里写一个 for 循环，每轮迭代按顺序处理一行数据，跟 Python 里的 while 循环差不多。

正解递归 CTE 是集合操作，不是过程式循环。每一轮迭代处理的是“上一轮产生的新行集合”，用的是基于集合的连接、过滤操作，而非逐行游标遍历。PostgreSQL 的优化器可能以 hash join 或 merge join 的方式一次性完成一轮全部行的展开。把递归 CTE 理解为“逐代翻族谱”，翻的时候是整页整页地抄写，而不是一个一个名字地念。

为何常见：因为“递归”这个词在编程语言中通常指函数调用自身，而 SQL 的递归 CTE 虽然也自引用，但底层语义是集合的迭代扩展，和命令式语言的递归有本质区别。

误区锚点成员和递归成员之间用 UNION 还是 UNION ALL 都一样，反正都是把结果拼起来，UNION 还能顺便去重，不是更好吗？

正解递归 CTE 必须使用 UNION ALL。UNION 会做去重，而去重可能破坏递归的层级语义——如果两个不同路径产生完全相同的行，UNION 会将其合并为一行，导致后续迭代轮次无法基于“这两条路径各自扩展”，最终结果缺失部分分支。族谱中两个分支的成员同名同姓，不能因为重名就认为他们是同一个人而不继续往下查。

为何常见：刚接触 SQL 的开发者习惯性地认为去重是“安全无害的优化”，而忽略了在递归场景中每一行都承载着路径信息，去重即丢失路径。

误区只要数据是从数据库里查出来的，应该都是干净的吧？递归 CTE 总会在某一轮自然终止，不需要额外处理。

正解实际业务数据中环非常常见——员工表中 A 的 manager 是 B、B 的 manager 又是 A（数据录入错误），BOM 表中零件互相引用。一旦存在环，递归成员每一轮都会产生新行，永远不会返回空集，查询会一直跑到数据库超时。工程上必须在递归成员中加防护：用 depth < 50 截断深度，或维护 path [] 数组检测已访问节点。

为何常见：教科书里的树形数据都是无环的，让学习者天然低估了真实数据的“脏”程度——现实数据里缺引用、错引用、循环引用无处不在。

误区在 CTE 前加了 MATERIALIZED 就一定是性能优化，物化缓存只需要计算一次，肯定比反复执行快。

正解物化 CTE 会先把结果写入临时存储，再从临时存储读取。如果 CTE 只被引用一次，物化的“先写后读”相比“直接执行后使用”反而多了一次临时表读写开销。只有 CTE 被多次引用且子查询本身计算代价较高时，物化才划算。引用一次的场景用 NOT MATERIALIZED 强制内联，让优化器把子查询合并进主查询。

为何常见：“缓存=优化”是直觉上的一个强大偏见，但它忽略了缓存本身的写入和存储开销——就像复印族谱要花时间和纸，只问一个人还不如直接翻原书。

面试题

Q1. CTE 的中文全称是什么？

单选题 · easy

  • A.通用表表达式← 正确答案
  • B.公共表连接
  • C.复杂事务查询
  • D.连接表表达式

答案：A。CTE 全称 Common Table Expression，即通用表表达式。它通过 WITH 子句定义命名临时结果集，注意不是“公共表连接”或“复杂事务查询”。

Q2. 递归 CTE 中“锚点成员”的作用是什么？

单选题 · easy

  • A.确定递归终止条件
  • B.提供迭代的起始数据集← 正确答案
  • C.合并每轮查询结果
  • D.提升查询的并行度

答案：B。锚点成员为递归提供第一代数据，相当于族谱中的始祖。终止条件由递归成员返回空行触发，合并结果由 UNION ALL 完成，都与锚点成员无关。

Q3. 递归 CTE 的终止条件是什么？

单选题 · mid

  • A.递归成员返回 0 行数据← 正确答案
  • B.锚点成员结果集为空
  • C.迭代次数达到固定上限
  • D.UNION ALL 去重完成

答案：A。PostgreSQL 递归 CTE 的终止机制是自然的：当某轮递归成员基于上一轮新行计算后返回 0 行，迭代停止。锚点为空只会让结果直接为空，UNION ALL 不去重，也不存在默认的固定次数上限（需人工设置）。

Q4. 递归 CTE 中锚点成员和递归成员之间用什么关键字连接？

单选题 · mid

  • A.UNION
  • B.JOIN
  • C.UNION ALL← 正确答案
  • D.INTERSECT

答案：C。标准写法要求使用 UNION ALL。UNION 会去重，可能破坏树的层级语义（不同路径产生的相同行被合并）；JOIN 和 INTERSECT 不适用于合并递归各轮结果。

Q5. 在组织架构递归查询中，depth 字段的递增方式是什么？

单选题 · mid

  • A.每轮迭代对所有已返回行整体加一
  • B.递归成员中写成 depth + 1← 正确答案
  • C.由数据库自动计算层级深度
  • D.锚点成员中完成深度自增

答案：B。depth 的递增需要开发者在递归成员中显式写出 t.depth + 1。锚点成员把根节点深度设为 1，递归成员对每一轮新行在前一层基础上 +1，数据库不会自动推导层级。

Q6. 物化 CTE（MATERIALIZED）的主要作用是什么？

单选题 · mid

  • A.将结果集永久写入数据库磁盘
  • B.让 CTE 结果只计算一次并缓存复用← 正确答案
  • C.自动为 CTE 结果创建索引
  • D.强制 CTE 每轮迭代重新计算

答案：B。物化 CTE 在语句执行期间把子查询结果缓存到临时存储，后续引用直接读取，避免重复执行。它不是永久表、不创建索引，也不是强制重算。“物化”只是一次执行期内的一次性计算+复用。

Q7. BOM 物料拆解中，子件总用量的正确计算方式是什么？

单选题 · hard

  • A.所有层级的数量直接求和
  • B.沿每条路径逐层累乘再汇总← 正确答案
  • C.只取最深一层的数量
  • D.取所有层级数量的最大值

答案：B。BOM 的用量沿装配路径累积：整车需要 1 台发动机，发动机需要 4 个活塞，则整车需要 1×4=4 个活塞。直接求和会忽略层级间的乘法关系，只看最深层或最大值明显错误。

Q8. 下列哪些场景适合使用递归 CTE？

多选题 · mid

  • A.查询所有层级的组织下属← 正确答案
  • B.统计单张订单的订单总额
  • C.BOM 物料树的完整拆解← 正确答案
  • D.计算两个日期之间的天数差

答案：AC。递归 CTE 解决的是层级或图状数据的遍历问题。组织下属和 BOM 拆解都需要逐层展开，适合用递归 CTE。订单金额是简单聚合，日期差是日期函数，都不涉及层级递归。

Q9. 在 PostgreSQL 中预防递归 CTE 死循环的常用手段有哪些？

多选题 · hard

  • A.配置全局递归次数上限参数
  • B.递归成员中加深度限制条件← 正确答案
  • C.用路径数组检测已访问节点← 正确答案
  • D.修改数据库事务超时时间

答案：BC。PostgreSQL 没有 MySQL 那样的 cte_max_recursion_depth 全局参数，事务超时只是临时兜底。工程上最常见的做法是在递归成员用 WHERE depth < N 限制深度，或维护 path 数组检测环——相当于在族谱里标记“这个人我已经查过了”。

Q10. 关于递归 CTE 执行过程，下列说法正确的是？

多选题 · mid

  • A.先执行锚点成员获得起始集合← 正确答案
  • B.每轮递归成员基于上一轮新行计算← 正确答案
  • C.递归结果在每轮迭代前自动去重
  • D.最终结果是锚点与所有轮次的并集← 正确答案

答案：ABD。递归 CTE 先执行锚点（A 正确），之后每轮只基于上一轮新行做连接计算（B 正确），最终结果由 UNION ALL 合并锚点及所有轮次的新行（D 正确）。UNION ALL 不去重（C 错误），这正是区别于 UNION 的语义。