CTE与递归CTE：SQL高级查询

CH-03-05-007·约 21 分钟·编程语言与开发工具·更新 2026-08-10

想象一下，你是一位建筑工程师，面前摆着一张复杂的建筑图纸。图纸上有几十个独立的小构件——楼梯、承重墙、门窗框架——它们各自有精确的尺寸和位置。如果你把这些构件全部用一行话写在一张纸上，恐怕没有人能看懂。但你选择把它们分解成一张张“蓝图小卡”，每张卡只描述一个构件，最后再拼成完整的设计图。 SQL里的CTE（Common Table Expression，公用表表达式）就是这样的“蓝图小卡”。它让你把复杂的查询拆解成一个个命名清晰、逐层依赖的临时片段，最后组合成一个可读性极强的主查询。而递归CTE，则是这套体系中最奇妙的部分——就像一台自动浇筑塔吊，它能反复执行相同的动作，一层一层向上盖楼，直到建筑物完工。对AI从业者而言，复杂数据清洗、特征工程中的多层嵌套查询、层级关系遍历（如组织架构、知识图谱路径）都是家常便饭。掌握CTE与递归CTE，意味着你能把原本“天书般”的SQL写出工程美感，让队友和未来的自己都心存感激。

岗位关联

一句话价值：CTE是SQL代码的“工程化重构工具”，递归CTE则是处理树状层级数据的“终极武器”——数据工程师的日常硬技能。

可视化演示
初识CTE：把“天书”拆成“蓝图小卡”
WITH语法：如何把“蓝图小卡”钉在图纸上
递归CTE原理：自动浇筑塔吊的秘密
层级查询实战：从老板到实习生，一次遍历全公司
与子查询对比：同样是“临时工”，差距怎么这么大
性能优化技巧：让塔吊既快又省油
数据工程应用：从ETL到特征工程的全能选手
AI 应用场景
Python 演示
常见误区
面试题

可视化演示

展示递归CTE逐层展开层级树、CTE链式引用与子查询嵌套的可读性对比，以及多CTE管道式数据处理流程。

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

递归CTE：逐层展开层级树锚点查询提供根节点(CEO) 递归成员自引用逐层展开子节点 UNION ALL连接直到无新行 WITH RECURSIVE cte AS (anchor UNION ALL recursive)

初识CTE：把“天书”拆成“蓝图小卡”

你第一次接手别人的SQL，发现一个 FROM 从句里嵌套了三层子查询，最里层的别名和外层混在一起，读完第一行就忘了第二行的含义。这种感觉就像拿到一张密密麻麻的建筑总图，所有尺寸标在一起，线条交叉到无法辨认。CTE 的诞生就是为了终结这种“嵌套地狱”。

CTE 的中文是“公用表表达式”，它允许你在一个查询的最前面，用 WITH 关键字定义一个或多个命名的临时结果集。这个临时结果集的生命周期只属于当前这条查询语句——就像建筑工地上的一张蓝图小卡，只服务于当前这栋楼，楼盖完就回收。

这里的关键洞察是：销售汇总 不是一个真实存在的表，它是查询执行到一半时“凭空搭出来的架子”，但它有名字、有结构、可以被主查询引用。对 SQL 引擎来说，CTE 像是一个内联视图；对写代码的你来说，它像是一个函数局部变量——让思路逐层推进。

CTE 的作用域仅限于它所在的这条语句——你不能在另一条 SQL 里引用它，这就像是蓝图小卡不能拿到别人工地上用。但这种约束反而让查询变得自包含、可移植性极强，你把整条语句复制到任何环境，它都能独立运行。

回到比喻：每张“蓝图小卡”都只画一个构件，但它们组合起来就是完整建筑。CTE 的核心理念正是分解与组合——把复杂问题拆成简单片段，再像搭积木一样组合出最终答案。

\text{CTE 定义形式：} \quad \text{WITH cte\_name (col1, col2) AS (SELECT ...) }

CTE 的本质是“可命名 + 可引用”的临时结果集，它在查询语句执行完毕后自动消失。

定义形式WITH 关键字表明即将定义一个临时结果集，cte_name 是这个结果集的名字。
$\text{WITH cte\_name AS (subquery)}$
可选列名你可以显式指定列名，相当于给结果集的列“贴标签”。
$\text{WITH cte\_name (col\_a, col\_b) AS (SELECT ...)}$
主查询引用定义完 CTE 后，后面的主 SELECT 可以把 CTE 当作普通表来查。
$\text{SELECT * FROM cte\_name}$

sql6 行

WITH 销售汇总 AS (    SELECT 部门, SUM(金额) AS 总金额    FROM 订单    GROUP BY 部门)SELECT * FROM 销售汇总 WHERE 总金额 > 10000;

WITH语法：如何把“蓝图小卡”钉在图纸上

上一节我们认识了 CTE 的样子，现在来仔细研究这张“蓝图小卡”的规格说明。想像你在工地上有一块大白板，每张卡片用一颗钉子钉在白板上，卡片之间可以互相引用，但卡片本身不能反向引用后面的卡片，因为 WITH 语句是按书写顺序逐条定义的。

当你想定义多个 CTE 时，用逗号分隔即可，不需要重复写 WITH：

这个语法细节极其重要：每个 CTE 之间用逗号连接，且只有一个 WITH。新手最常见的错误是在第二个 CTE 前又写一个 WITH，导致语法错误，就像在白板上钉卡片时又加了一块新白板。

使用 WITH RECURSIVE 前缀可以让 CTE 支持递归，这是处理层级数据的关键开关。有些数据库（如 PostgreSQL）要求递归 CTE 明确声明 RECURSIVE 关键字，就像你需要在塔吊上挂出“高空作业”的警示牌，提醒引擎和读者：这里会自我引用。

CTE 的命名要遵循“名词性、表意清晰”的原则——sales_summary 优于 t1，employee_org 优于 emp。这就像每张蓝图小卡上都用红字标清了构件名称，而不是写“东西1”“东西2”。

钉好一张张蓝图小卡后，主查询就像总工程师拿着图纸指挥施工。你会发现，即使查询逻辑长达几百行，只要 CTE 划分合理，每一步的目的都一目了然。这就是工程化的力量。

\text{多 CTE 结构：} \quad \text{WITH a AS (...), b AS (...), c AS (...) SELECT ...}

多个 CTE 以逗号分隔，前面的 CTE 可以被后面的 CTE 引用，顺序决定了依赖关系。

顺序依赖CTE 按书写顺序定义，第 n 个 CTE 可以引用前 n-1 个，但不能引用后面的。
$\text{WITH a AS (...), b AS (SELECT ... FROM a)}$
逗号分隔每个 CTE 定义块之间用英文逗号连接。
$\text{WITH a AS (...), b AS (...)}$
RECURSIVE 修饰在 WITH 后加 RECURSIVE 关键字启用递归模式。
$\text{WITH RECURSIVE cte AS (...)}$

sql6 行

WITH   华东订单 AS (SELECT * FROM 订单 WHERE 区域 = '华东'),  大客户 AS (SELECT * FROM 客户 WHERE 等级 = 'VIP')SELECT a.*FROM 华东订单 aJOIN 大客户 b ON a.客户ID = b.客户ID;

递归CTE原理：自动浇筑塔吊的秘密

现在进入最激动人心的部分——递归 CTE。把 CTE 比作蓝图小卡，递归 CTE 就是一台自动浇筑塔吊。它有一个基础斗（锚点成员）和一条不断向上伸展的机械臂（递归成员）。机械臂每完成一次动作，就自动向上升一层，直到触及设定的最高点（终止条件）为止。

递归 CTE 的标准结构包含两个部分，用 UNION ALL 连接：

这个查询返回 1 到 10 的整数序列。执行过程如下：锚点成员先跑一遍，得到第一行 n=1；然后递归成员被反复执行——每一次都读取上一次产生的行，在 n 上加 1，直到不再满足 n < 10。这正是塔吊“浇完一层、升一层”的过程。

执行顺序之所以微妙，是因为每次递归只处理“上一次刚刚生成的行”，而不是整个 CTE 的全部结果。这意味着递归 CTE 天然适合表达“逐层扩散”的问题——组织架构（上级找下级）、BOM 展开（父件找子件）、图遍历（节点找邻居）。

理解了这四个要素，你就掌握了递归 CTE 的全部精髓。如果忘记写终止条件，塔吊会一直向上浇筑、永不停止——在 SQL 里这会导致无限循环。大多数数据库会设置递归深度上限（如 PostgreSQL 默认 1000），就像塔吊有高度限位器一样，防止你的查询把数据库资源耗尽。

\text{递归执行序列：} \quad \text{锚点} \rightarrow \text{递归}_1 \rightarrow \text{递归}_2 \rightarrow \cdots \rightarrow \text{终止}

递归 CTE 用 UNION ALL 连接锚点成员与递归成员，递归成员反复引用自身输出，直到终止条件不满足。

锚点成员递归的起点，只执行一次，通常是一个简单 SELECT。
$\text{SELECT 1 AS n}$
UNION ALL 连接负责把锚点结果与每次递归产生的新行合并，保留重复行。
$R_0 \cup R_1 \cup R_2 \cup \cdots$
递归成员引用 CTE 自身，在每次迭代中基于“上一轮的输出”计算新行。
$\text{SELECT n + 1 FROM 序列 WHERE n < 10}$
终止条件隐藏在 WHERE 子句中，一旦没有新行产生，迭代终止。
$\text{当 } n \ge 10 \text{ 时，} \sigma_{n<10}(R_{k}) = \varnothing$

sql7 行

WITH RECURSIVE 数字序列 AS (    SELECT 1 AS n                    -- 锚点成员：起点    UNION ALL    SELECT n + 1 FROM 数字序列        -- 递归成员：引用自身    WHERE n < 10                     -- 终止条件)SELECT * FROM 数字序列;

层级查询实战：从老板到实习生，一次遍历全公司

假设你在一家 500 人规模的公司做数据分析，老板丢给你一个任务：把整个组织架构树导出成一张扁平表——每个员工一行，包含他的所有上级链路。如果是手工在 Excel 里一级一级查，你得打开几百次 vlookup，那画面太美不敢看。

递归 CTE 正是为此而生。看这个经典例子：

在这个查询中，锚点成员选出没有经理的 CEO（层级=1），递归成员通过 JOIN 组织树 不断找出每个员工的下属，层级逐层+1，路径则不断拼接。最终，你得到了一张包含全员层级关系和完整汇报链路的表。

类似的需求遍布制造业与电商：物料清单（BOM）展开——一台手机由屏幕、主板、电池组成，主板又有芯片、电容……递归 CTE 可以一层层炸开所有子件；内容分类——一个商品类目下有子类目，子类目下还有孙类目；权限树——一个角色继承自另一个角色。

当递归 CTE 跑完的那一刻，你手里拿到的是一张完整的企业“族谱”，每一行的路径字段都讲述着这个员工从 CEO 到自己的完整故事。没有递归 CTE，这项任务将是一项多轮自连接 + 集合合并的噩梦；有了它，它只是一段 15 行的声明式代码。

\text{层级递推公式：} \quad \text{level}(x) = \text{level}(\text{parent}(x)) + 1

组织树通过锚点选出根节点，通过递归 JOIN 父节点的子节点，逐层计算层级并拼接路径。

锚点定义根节点以 CEO 为起点，层级设为 1。
$\text{level}(\text{CEO}) = 1$
递归展开子树每次将当前层级的员工与其直接下属相连。
$\text{下属}(e) = \sigma_{\text{经理ID} = e.\text{员工ID}}(\text{员工})$
路径拼接把上级路径与该员工姓名连接，形成完整链路。
$\text{路径} \leftarrow \text{上级路径} \| \text{'>'} \| \text{姓名}$
层级递增并终止没有下属时递归自然停止。
$\text{level}(x) = \text{level}(\text{parent}(x)) + 1$

sql10 行

WITH RECURSIVE 组织树 AS (    SELECT 员工ID, 姓名, 经理ID, 1 AS 层级, 姓名 AS 路径    FROM 员工    WHERE 经理ID IS NULL           -- 锚点：CEO    UNION ALL    SELECT e.员工ID, e.姓名, e.经理ID, t.层级 + 1, t.路径 || '>' || e.姓名    FROM 员工 e    JOIN 组织树 t ON e.经理ID = t.员工ID   -- 递归：找到下属)SELECT * FROM 组织树 ORDER BY 路径;

与子查询对比：同样是“临时工”，差距怎么这么大

在 CTE 诞生之前，SQL 开发者用子查询来处理“先算中间结果”的需求。子查询就像是临时雇来的搬运工——他确实能干完活，但你不知道他叫什么名字，没法让他反复跑腿，更没法把几个搬运工的工作成果组合成流水线。而 CTE 是固定编制的施工队成员，每个都有名牌、有工位、可以互相协作。

来看对比。假如你要找出“下单金额超过平均值的用户”，用子查询是这样：

这段 SQL 读起来像绕口令，第三个 SELECT 嵌在第二个 SELECT 里，第二个又躲进了 HAVING。如果用 CTE：

两段 SQL 在绝大多数数据库上执行计划完全相同——CTE 不是魔法，优化器会把它展开成等价的子查询。但第二段读起来是“自顶向下”的叙事结构：先算用户消费，再算平均消费，最后做筛选。这就是可读性的胜利。

CTE 还有一个子查询难以企及的优势：自引用能力。子查询不可能 SELECT 自身，而递归 CTE 就是建立在这种自我引用之上的。这就像临时搬运工不可能自己给自己派活，而施工队成员之间可以形成“你昨天干的活，我今天接着干”的流水线。更别提一些数据库（如 PostgreSQL）允许对 CTE 添加物化提示，让你手动干预执行计划——子查询没有这个待遇。

所以，下次遇到“需要复用同一个中间结果”的查询，别再让子查询嵌套成“千层饼”，把它拆成 CTE 吧。你的同事会在 code review 时感谢你。

\text{语义等价：} \quad \sigma_{\text{条件}}(\text{CTE}) \equiv \text{内联子查询}

从语义上讲，CTE 等价于在 FROM 子句中放置一个子查询视图，但可读性与复用性远超后者。

语义等价性数据库会把 CTE 内联展开为子查询形式，执行计划通常无异。
$\text{WITH t AS (SELECT ...) SELECT * FROM t} \equiv \text{SELECT * FROM (SELECT ...) t}$
可读性差异子查询的嵌套深度增加，大脑需要维护多层上下文；CTE 的平铺结构降低了认知负担。
$\text{嵌套深度} d_{\text{subquery}} \gg d_{\text{CTE}}$
复用能力CTE 可以在主查询中被多次引用；子查询若复用，只能整段复制粘贴。
$\text{CTE 引用次数：} k \ge 1 \text{，子查询复制次数：} k \text{（代码冗余）}$

sql8 行

SELECT * FROM 用户WHERE 用户ID IN (    SELECT 用户ID FROM 订单    GROUP BY 用户ID    HAVING SUM(金额) > (SELECT AVG(总金额) FROM (        SELECT 用户ID, SUM(金额) AS 总金额 FROM 订单 GROUP BY 用户ID    ) t));

sql9 行

WITH 用户消费 AS (    SELECT 用户ID, SUM(金额) AS 总金额    FROM 订单 GROUP BY 用户ID),平均消费 AS (    SELECT AVG(总金额) AS 均值 FROM 用户消费)SELECT * FROM 用户WHERE 用户ID IN (SELECT 用户ID FROM 用户消费 WHERE 总金额 > (SELECT 均值 FROM 平均消费));

性能优化技巧：让塔吊既快又省油

递归 CTE 虽好，但如果不加节制，塔吊会变成油老虎。想像一下：每浇一层楼，塔吊都要把机械臂从地面重新升一次，如果楼层之间有大量不必要的往返，时间与油耗都在白白浪费。SQL 中的“油耗”就是扫描的行数、排序的代价和内存的占用。

第一条优化策略是尽早裁剪递归范围。递归 CTE 的每一轮迭代都会产生一批新行，如果这些行最终根本不会被用到，那它们就在白白消耗资源。所以在递归成员的 WHERE 条件中尽可能滤除多余的行——比如只递归深度小于 5 的节点，比如排除已经失效的订单。

第二条是合理使用物化（MATERIALIZED）提示。在 PostgreSQL 中，CTE 默认会被内联，像宏一样嵌入主查询；但加上 MATERIALIZED 后，CTE 会先独立计算并缓存结果，适合“CTE 被主查询引用多次且结果集较小”的场景。相反，如果 CTE 结果集巨大且只被引用一次，强制物化会让内存压力骤增。

第三条是索引策略。递归 CTE 的每一次迭代本质是一次 JOIN——比如 JOIN 员工 e ON e.经理ID = t.员工ID。如果 员工.经理ID 上没有索引，每一次迭代都是一次全表扫描，10 层递归就是 10 回全表扫描。建立 (经理ID) 索引后，每次迭代走索引扫描，性能提升可能是两个数量级。

最后一条锦囊是“周期监控”。在数据量大的表上跑递归前，先用 EXPLAIN ANALYZE 跑一遍小数据，观察每一步迭代的行数变化。就像试运行塔吊一样，先空转一次看看机械臂有没有异响，再正式开工。你可以在 #viz-info 区域记录每次迭代的行数，画成折线——如果行数爆炸式增长，说明递归逻辑里产生了笛卡尔积，得马上叫停。

\text{递归总代价：} \quad \sum_{k=1}^{n} \text{Cost}(\text{iter}_k) \approx n \times \log(\text{行数})

递归 CTE 性能 = 迭代次数 × 单次迭代代价；索引与裁剪同时压缩这两个因子。

迭代次数控制树深度直接反映为迭代次数，深度越大代价越高。
$n_{\text{iter}} = \text{depth}(T)$
单次迭代代价主要由 JOIN 基数决定，索引可将其降为对数级。
$\text{Cost} \propto \lvert R_k \rvert \times \lvert S \rvert \rightarrow \lvert R_k \rvert \log \lvert S \rvert$
物化权衡物化缓存结果集，以内存换重复计算。
$\text{物化后代价} = \text{构建}(T) + k \times \text{读取}(T)$

sql4 行

WITH RECURSIVE 组织树 AS MATERIALIZED (    SELECT ...  -- 组织架构递归)SELECT ... FROM 组织树 t1 JOIN 组织树 t2 ON ...;

数据工程应用：从ETL到特征工程的全能选手

假如你是一名数据工程师，每天早上要跑一套 ETL 流程：从业务库抽取原始订单，清洗、去重、加工成特征宽表，最后写入数仓供算法团队训练模型。在没有 CTE 的时代，这个任务通常是一长串 INSERT……SELECT 嵌套，或者用 Python 脚本调多次 SQL 接口，在内存里做数据转换——又慢又难维护。

CTE 为 ETL 提供了一条“声明式流水线”。每一段清洗逻辑变成一个 CTE 块，就像工厂里的流水线工位：① 原始订单去重 → ② 过滤异常值 → ③ 用户维度聚合 → ④ 特征拼接。每个工位只对上一工位的输出负责，逻辑清爽、调试方便：

在 AI 特有的需求——特征工程中，递归 CTE 出场更频繁。比如构建“用户行为路径特征”：从埋点日志中还原每个用户的访问序列，session 划分后再用递归 CTE（或窗口函数）拼接成路径字符串。又比如做知识图谱中的“多跳邻居”特征——“用户 A 关注的用户关注的用户中，有多少人买了商品 X”，这本质是图上的 2-hop 遍历，正是递归 CTE 的拿手好戏。

回到 AI 场景的深水区：训练样本里的“用户序列”、推荐系统里的“物品知识图谱”、NLP 里的“句法分析树”，本质上都是树或图结构。能熟练地用递归 CTE 将这些结构展开为关系表的特征，你在数据工程师团队中的话语权会大不一样。当然，若数据量达到数十亿级，你可能需要转向 GraphX 或 Neo4j 这类专业图引擎，但 CTE 在“中等规模 + 强关系型生态”的场景下，仍是性价比之王。

\text{多跳特征：} \quad N_{hop}(u) = \{ (hop_1, hop_2, \ldots, hop_k) \}

数据工程中的 ETL 分步清洗、用户路径还原、多跳邻居计算，均可借助 CTE 与递归 CTE 实现声明式、可维护的处理流。

分步清洗每个 CTE 只做一件转换，形成可读的流水线。
$\text{raw} \xrightarrow{\text{去重}} \text{dedup} \xrightarrow{\text{过滤}} \text{valid} \xrightarrow{\text{聚合}} \text{feature}$
递归展开树状维度一级级展开商品类目、部门架构，形成扁平维表。
$\text{维表} = \bigcup_{k=1}^{n} \text{level}_k$
多跳邻居递归 CTE 在关系图上逐层扩展，计算不同跳数的邻居集。
$\Gamma_k(u) = \Gamma(\Gamma_{k-1}(u))$

sql5 行

WITH   去重订单 AS (SELECT ..., ROW_NUMBER() OVER (PARTITION BY 订单ID ORDER BY 更新时间 DESC) rn FROM 原始订单),  有效订单 AS (SELECT * FROM 去重订单 WHERE rn = 1 AND 金额 > 0),  用户汇总 AS (SELECT 用户ID, COUNT(*) AS 订单数, SUM(金额) AS 消费总额 FROM 有效订单 GROUP BY 用户ID)SELECT * FROM 用户汇总;

AI 应用场景

场景一：特征工程中的“多跳邻居”生成

阶段：数据准备与特征工程

解决的问题：在社交推荐场景中，我们需要为每个用户构造“二跳邻居”特征（好友的好友中，有多少人点击了某商品）。传统做法是写多轮 Python 循环 + 多次 SQL 查询，在千万级用户表上循环几十轮，耗时数小时且代码难以维护。用递归 CTE 一次遍历图上所有路径，几分钟完成。

具体案例：某内容平台的推荐团队，在用户-关注关系表上使用递归 CTE 计算用户的两跳、三跳可达集合，并聚合出“关注的关注中消费过某内容的人数”作为特征，直接输入 LR 模型，离线 AUC 提升 1.8%。

场景二：ETL过程中的层级维度表拉平

阶段：数据管道构建

解决的问题：数据仓库中的组织架构、商品类目、地理区域都是树状结构，而算法模型需要的是“每个叶子节点携带完整祖先路径”的扁平表。传统拉平方式需要预知树的深度并写无数个 LEFT JOIN，一旦层级变化代码就报废。递归 CTE 自适应任意深度。

具体案例：一家零售商的数仓团队，用递归 CTE 将7层商品类目树展开成 120 万行扁平维表，每日 ETL 从 2.5 小时缩短到 18 分钟，且类目层级增减时无需改代码。

场景三：用户行为序列还原

阶段：数据分析与洞察

解决的问题：产品经理希望看到“用户在本次 Session 中的完整操作链路”，如：首页 → 搜索 → 商品详情 → 下单。原始埋点日志是一行行事件，缺少会话分组与顺序拼接。递归 CTE 可以按时间窗口和会话标识逐条串联事件，生成路径特征。

具体案例：某电商数据团队利用递归 CTE 将 3 亿条埋点日志还原为 2000 万条用户路径序列，并转化为“路径最短距离 + 关键节点命中”的特征，供推荐排序模型使用。

Python 演示

python60 行

import sqlite3 # 构造一个内存数据库，员工表 (id, name, manager_id)conn = sqlite3.connect(":memory:")conn.execute("""    CREATE TABLE employee (        id INTEGER PRIMARY KEY,        name TEXT NOT NULL,        manager_id INTEGER    )""") employees = [    (1, "CEO", None),    (2, "CTO", 1),    (3, "CFO", 1),    (4, "架构师", 2),    (5, "工程师", 4),    (6, "会计", 3),]conn.executemany("INSERT INTO employee VALUES (?, ?, ?)", employees)conn.commit() # 使用递归 CTE 遍历组织架构树sql = """WITH RECURSIVE org_tree AS (    SELECT id, name, manager_id, 1 AS level, name AS path    FROM employee    WHERE manager_id IS NULL    UNION ALL    SELECT e.id, e.name, e.manager_id, t.level + 1, t.path || '->' || e.name    FROM employee e    JOIN org_tree t ON e.manager_id = t.id)SELECT * FROM org_tree ORDER BY path""" cur = conn.execute(sql)print("=== 组织架构树递归遍历 ===")print(f"{'ID':<4}{'姓名':<10}{'上级ID':<8}{'层级':<6}汇报链条")print("-" * 60)for row in cur.fetchall():    emp_id, name, mgr_id, level, path = row    print(f"{emp_id:<5}{name:<12}{str(mgr_id):<10}{level:<7}{path}") # 统计每层人数cur2 = conn.execute("""    WITH RECURSIVE org_tree AS (        SELECT id, manager_id, 1 AS level        FROM employee WHERE manager_id IS NULL        UNION ALL        SELECT e.id, e.manager_id, t.level + 1        FROM employee e JOIN org_tree t ON e.manager_id = t.id    )    SELECT level, COUNT(*) FROM org_tree GROUP BY level ORDER BY level""")print("\n=== 各层级人数统计 ===")for level, cnt in cur2.fetchall():    print(f"层级 {level}: {cnt} 人")conn.close()

目的：在纯 Python 环境中模拟数据库递归 CTE 的执行，演示锚点成员、递归成员与终止条件的三段式逻辑。

WITH RECURSIVE org_tree AS (宣告一个递归 CTE，org_tree 是临时结果集的名字。
WHERE manager_id IS NULL锚点成员：选出没有上级的根节点（CEO）。
UNION ALL连接锚点与递归成员，保留重复行，确保每次迭代的结果都叠加。
JOIN org_tree t ON e.manager_id = t.id递归成员的核心：把员工表中的经理 ID 与上一轮的节点 ID 连接。
t.level + 1每往下一层，层级加一，形成逐层递增的编号。
t.path || '->' || e.name路径拼接，生成从 CEO 到当前员工的完整汇报链路。
sqlite3.connect(":memory:")在内存中建立数据库，无需外部服务，方便演示。
conn.executemany(...)批量插入员工数据，构造测试环境。
cur.fetchall()取出递归 CTE 的完整结果，逐行打印。
GROUP BY level ORDER BY level对结果按层级聚合，验证递归的正确性。

常见误区

误区“我写了 WITH 然后再 UNION ALL 引用自己，为什么报语法错误？是不是数据库不支持 CTE？”

正解在绝大多数数据库（PostgreSQL、MySQL 8.0+ 等）中，只要 CTE 是递归的，就必须在 WITH 后显式加 RECURSIVE 关键字，写成 WITH RECURSIVE cte AS (...)。这就像塔吊需要挂出“高空作业”标牌——不是摆设，而是安全协议。不加它，数据库解析器根本不认为你在写递归，自然报错。

为何常见：因为非递归的 CTE 不需要 RECURSIVE，用户写惯了普通 CTE 后，容易忽略递归场景下的额外要求。

误区“递归 CTE 不就是 CTE 加了个 UNION ALL 嘛，性能问题用普通 CTE 优化思路处理就行。”

正解递归 CTE 的执行模型完全不同：普通 CTE 是“一次性物化或内联”，递归 CTE 是“迭代式工作队列”——每一轮只处理上一轮新生成的行。这意味着递归 CTE 的计算复杂度与树深度线性相关，优化应聚焦“减少迭代轮数 + 单轮加速”，而非普通 CTE 的“只算一次就好”。

为何常见：两者在语法上只差一个关键字和 UNION ALL，容易让人误判复杂度来源。

误区“既然递归 CTE 能遍历树，那所有层级/树状结构查询都应该用它。”

正解递归 CTE 适合“树深度未知、需要逐层生成”的场景。但如果树的层数固定且较少（比如不超过 3 层），用多个普通 JOIN 往往更快更简单。数据库优化器对普通 JOIN 的优化非常成熟，而递归 CTE 是迭代执行，很难做全局优化。

为何常见：看到递归 CTE 的“魔法”效果后容易过度使用，忽略了场景匹配。

误区“反正数据库有最大递归深度保护（比如 1000），递归条件写松一点也没关系。”

正解数据库的深度上限是“安全网”，不是“优化建议”。如果递归 CTE 需要扫描大量节点，每次迭代都会产生临时表读写，即使没爆栈，也会让查询慢到让同事怀疑数据库挂了。终止条件宁紧勿松：能用 WHERE 控制深度就绝不靠 LIMIT。

为何常见：小数据集上测试时，递归很快跑完，让人低估了大迭代次数在真实数据量下的代价。

误区“CTE 是 '新技术'，肯定比子查询快，把子查询全改成 CTE 准没错。”

正解在大多数数据库上，普通 CTE 与等价子查询的执行计划完全一样——优化器会先内联 CTE 再做优化。CTE 的优势是可读性和复用性，不是速度。真正影响性能的是底层表结构、索引和数据分布，而不是你写没写 WITH。盲目改写反而可能因物化策略改变执行计划。

为何常见：把“代码更清晰”和“运行更快”两件事混为一谈，是新手最常见的归因错误。

面试题

Q1. 在 PostgreSQL 中，递归 CTE 的声明关键字是什么？

单选题 · easy

A.WITH AS
B.WITH RECURSIVE← 正确答案
C.WITH LOOP
D.CTE RECURSIVE

答案：B。递归 CTE 必须以 WITH RECURSIVE 开头，B 正确。A 缺少 RECURSIVE，普通 CTE 可以但递归不行；C、D 都是杜撰的语法。这就像塔吊必须挂“高空作业”标牌——数据库靠这个关键字知道你在进行自我引用的递归操作。

Q2. 关于递归 CTE 的锚点成员，下列说法正确的是？

单选题 · easy

A.锚点成员会被执行多次
B.锚点成员负责终止递归
C.锚点成员只执行一次，是递归的起点← 正确答案
D.锚点成员必须包含聚合函数

答案：C。锚点成员是递归的初始结果集，只执行一次，相当于塔吊的基础斗——先浇好地基再开始往上建。A 错误，锚点不重复执行；B 错误，终止条件是递归成员中的 WHERE 或数据耗尽；D 错误，锚点只是普通 SELECT，不需要聚合。递归成员反复执行，锚点只执行一次。

Q3. 下列递归 CTE 的错误原因是？

单选题 · mid

A.缺少列名定义← 正确答案
B.UNION ALL 不能用
C.缺少深度上限参数
D.查询没有任何问题

答案：A。递归 CTE 要求锚点成员和递归成员返回的列数、列名一致。锚点写了 SELECT 1，但 CTE 声明了列名 (n)。PostgreSQL 允许这种写法，它会把 1 命名为 n。递归成员引用 n 也合法。因此 D 正确。A、B、C 都描述了不存在的错误。注意：如果不带列名列表且锚点 SELECT 后不带别名，则可能报错；但这里已定义 (n) 所以没问题。

Q4. CTE 相比子查询最主要的优势是什么？

单选题 · mid

A.一定会更快
B.可以自引用与复用，且可读性更高← 正确答案
C.不占用数据库连接
D.自动建立索引

答案：B。CTE 的核心优势是“可命名、可复用、可自引用”，这让复杂查询拥有了工程化的结构，B 正确。A 错误，CTE 性能通常等价于子查询；C、D 错误，CTE 不涉及连接管理和索引创建。

Q5. 在递归 CTE 中，递归成员执行时读取的是哪部分数据？

单选题 · mid

A.整个 CTE 的最终结果
B.上一次迭代新生成的行← 正确答案
C.锚点成员的初始数据
D.源表的全量数据

答案：B。递归 CTE 采用工作队列模型：每一轮迭代只读取“上一轮新产生的行”，而不是整个累积结果集，B 正确。A 是普通 CTE 的物化方式；C 只在一开始发生；D 是 JOIN 的底层行为，但递归的“驱动”数据是上一轮输出，而非全表驱动。

Q6. 下列哪个场景最适合使用递归 CTE？

单选题 · easy

A.单表等值过滤
B.树状组织架构遍历← 正确答案
C.两表简单 JOIN
D.数值四则运算

答案：B。递归 CTE 擅长处理“层级未知、逐层展开”的树状结构，B 正确。单表过滤和两表 JOIN 用普通 SQL 即可，A、C 错误；四则运算直接写在 SELECT 中，D 错误。组织架构、BOM、图遍历都是递归 CTE 的主场。

Q7. 下列哪些是优化递归 CTE 性能的有效手段？

多选题 · mid

A.在递归成员 JOIN 的列上建索引← 正确答案
B.在递归成员 WHERE 中尽量裁剪数据← 正确答案
C.尽可能增加最大递归深度上限
D.使用物化提示避免重复计算← 正确答案

答案：ABD。A 正确，索引能把单轮迭代从全表扫描降为索引查找；B 正确，早期裁剪减少无效迭代；D 正确，物化适合被多次引用的 CTE 结果。C 错误，提高深度上限只是防爆栈，不会变快，反而可能掩盖设计缺陷。就像塔吊省油靠优化机械臂动作，而不是换更高的限位器。

Q8. 递归 CTE 与普通子查询的执行计划关系是？

单选题 · hard

A.递归 CTE 一定使用物化，子查询一定内联
B.普通 CTE 通常被优化器内联为等价子查询← 正确答案
C.子查询永远比 CTE 更快
D.递归 CTE 无法被优化器改写

答案：B。在多数数据库中，普通 CTE 会被优化器先内联再统一优化，执行计划与等价子查询基本一致，B 正确。A 错误，CTE 默认是否物化取决于数据库版本和提示；C 错误，性能差异取决于数据分布；D 错误，优化器可对递归 CTE 做裁剪和改写。

Q9. 关于 `WITH RECURSIVE` 的终止机制，正确的有哪些？

多选题 · hard

A.由递归成员中的 WHERE 条件决定← 正确答案
B.当某轮迭代不产生新行时自动结束← 正确答案
C.一定需要手动指定迭代次数上限
D.数据库有默认保护上限，防止死循环← 正确答案

答案：ABD。递归 CTE 的终止条件靠 WHERE 子句（下次不满足则无新行）和“上一轮无新行”共同决定，A、B 正确。D 正确，如 PostgreSQL 默认 max_recursive_iterations 保护。C 错误，通常不需要手动指定次数，若需要可以加 WHERE 深度限制，但非必需。

Q10. 使用 CTE 的典型收益有哪些？

多选题 · easy

A.代码从“嵌套地狱”变为平铺结构← 正确答案
B.同一中间结果可多次引用← 正确答案
C.必然缩短查询执行时间
D.支持递归自引用，解决层级遍历← 正确答案

答案：ABD。CTE 让 SQL 平铺化、模块化，A 正确；同一条语句中可多次引用同名 CTE，B 正确；递归 CTE 可以自我引用，D 正确。C 错误——CTE 是“可读性优化”而非“性能优化”，执行时间由优化器和数据分布决定，不保证变快。

可视化演示

初识CTE：把“天书”拆成“蓝图小卡”

WITH语法：如何把“蓝图小卡”钉在图纸上

递归CTE原理：自动浇筑塔吊的秘密

层级查询实战：从老板到实习生，一次遍历全公司

与子查询对比：同样是“临时工”，差距怎么这么大

性能优化技巧：让塔吊既快又省油

数据工程应用：从ETL到特征工程的全能选手

AI 应用场景

场景一：特征工程中的“多跳邻居”生成

场景二：ETL过程中的层级维度表拉平

场景三：用户行为序列还原

Python 演示

常见误区

面试题

Q1. 在 PostgreSQL 中，递归 CTE 的声明关键字是什么？

Q2. 关于递归 CTE 的锚点成员，下列说法正确的是？

Q3. 下列递归 CTE 的错误原因是？

Q4. CTE 相比子查询最主要的优势是什么？

Q5. 在递归 CTE 中，递归成员执行时读取的是哪部分数据？

Q6. 下列哪个场景最适合使用递归 CTE？

Q7. 下列哪些是优化递归 CTE 性能的有效手段？

Q8. 递归 CTE 与普通子查询的执行计划关系是？

Q9. 关于 WITH RECURSIVE 的终止机制，正确的有哪些？

Q10. 使用 CTE 的典型收益有哪些？

Q9. 关于 `WITH RECURSIVE` 的终止机制，正确的有哪些？