递归 CTE 的工作原理
基础情况加递归步骤
递归 CTE 的工作原理 是 CoddyKit 上的免费 SQL Academy 课时。 这是第 1 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。
什么是递归 CTE
递归 CTE 是一种会引用自身的公用表表达式。它允许您编写重复执行某个步骤、直到满足条件为止的查询——类似于循环，但完全使用 SQL 表达。
递归 CTE 使用 WITH RECURSIVE 关键字定义，非常适合遍历层级数据或类似图的数据，例如组织结构图、文件夹树和物料清单结构。
两部分结构
每个递归 CTE 都恰好包含由 UNION ALL 分隔的两部分：
1. 基础情况——返回起始行的非递归 SELECT。
2. 递归步骤——将 CTE 再次连接到自身并生成下一层行的 SELECT。
引擎会持续执行递归步骤并累积结果，直到该步骤不再生成任何新行。
WITH RECURSIVE cte_name AS (
-- Base case
SELECT ...
UNION ALL
-- Recursive step (references cte_name)
SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;从 1 数到 5
最简单的递归 CTE 是进行数字计数。基础情况将值 1 作为初始值。递归步骤在每次迭代中加上 1。递归步骤中的 WHERE 子句充当终止条件——没有它，查询就会永远运行。
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;逐步执行
下面介绍引擎如何逐次迭代地处理计数器 CTE：
第 0 次迭代（基础情况）：返回 {1}。
第 1 次迭代：对 {1} 应用递归步骤，返回 {2}。
第 2 次迭代：对 {2} 应用递归步骤，返回 {3}。
第 3、4 次迭代：依次返回 {4} 和 {5}。
第 5 次迭代：对于 n=5，WHERE n < 5 为假，因此返回零行。查询结束。
所有累积的行——1、2、3、4、5——就是最终结果。
建立层级表
递归 CTE 在自引用表上尤其有用。让我们创建一个 employees 表，其中每名员工都有一个可选的 manager_id，指向同一张表中的另一行。
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name VARCHAR(50),
manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Carol', 1),
(4, 'Dave', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);遍历层级结构
现在，我们可以从 CEO（Alice，id=1）开始遍历完整的汇报链。基础情况选择 Alice；递归步骤查找所有 manager_id 与 CTE 中已有 id 匹配的员工。
无论树有多深，结果都会包含从 Alice 出发能够到达的每名员工。
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;跟踪路径
一种常见的改进是构建一个路径字符串，显示从根节点到每个节点的完整链路。随着递归深入，我们使用 ' -> ' 分隔并拼接各个名称。
这样便于显示面包屑式导航，或调试较深的层级结构。
WITH RECURSIVE org_tree AS (
SELECT id, name, name AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, ot.path || ' -> ' || e.name
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;限制递归深度
深层或循环的数据可能导致递归 CTE 长时间运行。以下是两项安全做法：
1. 跟踪深度并添加 WHERE 子句——WHERE depth < 10 可确保遍历不会超过 10 层。
2. 使用循环检测列——某些数据库（PostgreSQL 14+）提供 CYCLE 语法，可自动检测对同一节点的重复访问。
WITH RECURSIVE org_tree AS (
SELECT id, name, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;递归 CTE 中的 UNION 与 UNION ALL
递归步骤几乎总是使用 UNION ALL，而不是 UNION。原因如下：
UNION 会在每次迭代后，通过比较整个结果集来删除重复行——这非常耗费性能，而且对于同一节点确实可能通过多条路径到达的图结构，还可能改变查询语义。
UNION ALL 会保留所有行而不去重，这对于树的遍历来说既更快也更正确。只有在确实需要删除重复项并且了解其性能代价时，才使用 UNION。
生成日期序列
递归 CTE 也很适合生成日期序列。本例会生成指定一周中的每一天，这种模式常用于构建日历报表或填补时间序列数据中的空缺。
WITH RECURSIVE date_series AS (
SELECT DATE '2024-01-01' AS day
UNION ALL
SELECT day + INTERVAL '1 day'
FROM date_series
WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;查找某位经理的所有下属
您可以使用任意特定节点作为基础情况的起点，而不必从根节点开始。这里我们从 Bob（id=2）开始，查找所有直接或间接向他汇报的人。
这种模式适用于权限检查、子树聚合，或将仪表板限定在单个部门的范围内。
WITH RECURSIVE subordinates AS (
SELECT id, name
FROM employees
WHERE id = 2
UNION ALL
SELECT e.id, e.name
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;快速检查
检验您对递归 CTE 工作方式的理解。
课程回顾
在本课中，您学习了递归 CTE 的工作方式：
结构：每个递归 CTE 都包含一个基础情况（起始行），通过 UNION ALL 与一个递归步骤（引用自身的 SELECT）连接。
终止：引擎会重复执行递归步骤并累积结果，直到该步骤返回零行。
常见用途：遍历组织结构图和文件夹树、生成数字或日期序列、计算路径，以及查找子树中的所有节点。
安全提示：始终包含终止条件（深度限制或循环保护条件），并且出于性能考虑，优先使用 UNION ALL 而不是 UNION。
常见问题解答
「递归 CTE 的工作原理」课时是免费的吗？
是的 — 「递归 CTE 的工作原理」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。
「递归 CTE 的工作原理」这节课中我会学到什么？
基础情况加递归步骤 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 SQL Academy 需要有经验吗？
无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 1 节课，共 4 节。
「递归 CTE 的工作原理」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 SQL Academy 课中编写并运行代码吗？
能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。