使用SQL递归查询：用`WITH AS`轻松遍历树形结构

作为开发者，我们经常需要处理具有层级关系的数据：组织结构、菜单树、分类目录、评论的嵌套回复等等。在SQL的世界里，这类数据通常表现为树形结构（每个节点有一个父节点，根节点无父节点）。传统的SQL查询在处理这种“无限层级”或“深度未知”的关系时往往力不从心，需要编写复杂的自连接或依赖应用程序层进行多次查询。而SQL标准中的递归公用表表达式（Recursive Common Table Expression, Recursive CTE），通过 WITH RECURSIVE 语法，为我们提供了一种在单条SQL语句内优雅遍历整个树形结构的强大方法。

WITH AS 基础回顾

WITH AS 子句（也称为CTE）的主要作用是为一个复杂的查询定义临时的命名结果集，这个结果集可以在后续的 SELECT, INSERT, UPDATE, DELETE 或 CREATE VIEW 语句中被多次引用。它提高了查询的可读性和可维护性。

sql

体验AI代码助手

代码解读

复制代码

WITH cte_name (column1, column2, …) AS ( -- 定义子查询 SELECT … FROM … WHERE … ) SELECT * FROM cte_name WHERE …;

• WITH：引入 CTE 的关键字。
• cte_name：自定义的临时结果集名称，可在后续主查询或其他 CTE 中引用。
• 可选列名列表：当子查询列不便或需要重命名时使用。

WITH AS 递归 CTE 语法结构

递归 CTE 是 WITH AS 的一种特殊形式，它允许CTE在其定义中引用自身。这使得我们能够基于初始结果集（锚点成员）逐步迭代（递归成员），构建出层级关系或序列。其基本结构如下：

sql

体验AI代码助手

代码解读

复制代码

WITH RECURSIVE cte_name (column_list) AS ( -- 锚点成员 (Anchor Member): 初始查询，通常是根节点 SELECT ... FROM ... WHERE ... -- 初始化条件 UNION [ALL] -- 使用 UNION ALL 保留所有行（包括重复行，递归通常需要） -- 递归成员 (Recursive Member): 引用 cte_name 自身，连接到下一层级 SELECT ... FROM some_table JOIN cte_name ON ... -- 关键！连接条件定义父子关系 WHERE ... -- 可选的递归终止条件 ) -- 主查询：使用最终构建好的递归CTE结果 SELECT * FROM cte_name [OPTIONS];

1. 锚点（Anchor Member） ：提供初始行，相当于递归的“种子”。
2. 递归成员（Recursive Member） ：基于上一次结果进行自我引用查询，直至满足终止条件。

示例：组织机构数据