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

liuchang0820
16 阅读7分钟

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

WITH AS 基础回顾

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

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在其定义中引用自身。这使得我们能够基于初始结果集（锚点成员）逐步迭代（递归成员），构建出层级关系或序列。其基本结构如下：

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） ：基于上一次结果进行自我引用查询，直至满足终止条件。

示例：组织机构数据模型

假设我们有一个 organization 表，结构如下：

字段名类型描述
idINT组织/部门唯一ID
nameVARCHAR(50)组织/部门名称
p_idINT父级组织/部门ID (根节点的 p_id 通常为 NULL 或 0)

示例数据 (organization):

INSERT INTO organization (id, name, p_id) VALUES
(1, '总公司', NULL),
(2, '技术部', 1),
(3, '市场部', 1),
(4, '人力资源部', 1),
(5, '后端组', 2),
(6, '前端组', 2),
(7, '运维组', 2),
(8, '华东大区', 3),
(9, '华南大区', 3),
(10, '招聘组', 4),
(11, '培训组', 4),
(12, 'Java小组', 5),
(13, 'Go小组', 5),
(14, '上海分公司', 8),
(15, '杭州分公司', 8);

结构示意：

总公司 (id:1)
├── 技术部 (id:2)
│   ├── 后端组 (id:5)
│   │   ├── Java小组 (id:12)
│   │   └── Go小组 (id:13)
│   ├── 前端组 (id:6)
│   └── 运维组 (id:7)
├── 市场部 (id:3)
│   ├── 华东大区 (id:8)
│   │   ├── 上海分公司 (id:14)
│   │   └── 杭州分公司 (id:15)
│   └── 华南大区 (id:9)
└── 人力资源部 (id:4)
    ├── 招聘组 (id:10)
    └── 培训组 (id:11)

递归查询实战：获取技术部及其所有子孙部门

技术部id=2，获取该部门自身以及它下面所有层级的子部门。

递归CTE查询：

WITH RECURSIVE TechTree AS (
    -- 锚点成员：选取起始点（技术部自身）
    SELECT id, name, p_id, 1 AS level -- level 表示层级深度
    FROM organization
    WHERE id = 2 -- 指定技术部为起点

    UNION ALL

    -- 递归成员：查找当前结果集中所有节点的直接子节点
    SELECT child.id, child.name, child.p_id, parent.level + 1
    FROM organization AS child
    INNER JOIN TechTree AS parent -- 关键！连接递归CTE自身
        ON child.p_id = parent.id -- 连接条件：子节点的 p_id = 父节点的 id
)
SELECT id, name, p_id, level
FROM TechTree
ORDER BY level, id; -- 按层级和ID排序，便于查看

关键点：

  1. WITH RECURSIVE TechTree ...: 声明一个名为 TechTree 的递归CTE。
  2. 锚点成员 (SELECT ... WHERE id = 2):
    • 查询起始点（技术部，id=2）。
    • 同时初始化一个 level 字段，值为1，表示这是树的第一层（根节点通常是0层，这里批总公司）。
  3. UNION ALL: 将递归成员的结果追加到锚点成员的结果之后。ALL 很重要，因为不同层级上的子节点是不同的行。
  4. 递归成员 (SELECT child.id ... JOIN TechTree AS parent ON child.p_id = parent.id):
    • FROM organization AS child: 从原始表中选择潜在的子节点。
    • INNER JOIN TechTree AS parent: 这是递归的核心。TechTree 此时包含了之前（锚点或上一次递归）查询到的节点（作为父节点）。
    • ON child.p_id = parent.id: 连接条件。找到所有 p_id 等于 TechTree 中某个父节点 id 的记录。这些记录就是父节点的直接子节点。
    • parent.level + 1: 子节点的层级是父节点层级加1。
  5. 递归终止:
    • 隐式终止：当递归成员 (SELECT child.id ...) 再也找不到满足 child.p_id = parent.id 的新行时，递归过程自然停止。在我们的例子中，当查询到叶子节点（如 Java小组, Go小组 等）后，它们没有子节点，递归即结束。
  6. 主查询 (SELECT * FROM TechTree ORDER BY level, id):
    • 最终从构建好的 TechTree CTE 中选择所有列。
    • 按 level（层级）和 id 排序，使结果呈现清晰的树形结构。

查询结果：

idnamep_idlevel
2技术部11
5后端组22
6前端组22
7运维组22
12Java小组53
13Go小组53

如何生成完整树路径

如果需要知道从根节点到当前节点的完整路径。利用递归CTE中的字符串拼接也可以轻松实现：

WITH RECURSIVE FullOrgPath AS (
    -- 锚点成员：根节点（p_id IS NULL）
    SELECT id, name, p_id, 1 AS level, CAST(name AS VARCHAR(255)) AS path
    FROM organization
    WHERE p_id IS NULL -- 通常根节点的父ID为空

    UNION ALL

    -- 递归成员
    SELECT child.id, child.name, child.p_id, parent.level + 1,
           CONCAT(parent.path, ' -> ', child.name) -- 拼接路径
    FROM organization AS child
    INNER JOIN FullOrgPath AS parent
        ON child.p_id = parent.id
)
SELECT id, name, p_id, level, path
FROM FullOrgPath
ORDER BY path; -- 按路径排序，树形结构一目了然

结果示例:

idnamep_idlevelpath
1总公司NULL1总公司
4人力资源部12总公司 -> 人力资源部
10招聘组43总公司 -> 人力资源部 -> 招聘组
11培训组43总公司 -> 人力资源部 -> 培训组
3市场部12总公司 -> 市场部
8华东大区33总公司 -> 市场部 -> 华东大区
14上海分公司84总公司 -> 市场部 -> 华东大区 -> 上海分公司
...............

注意事项

  1. RECURSIVE 关键字：在支持递归CTE的数据库（如 PostgreSQL, MySQL 8.0+, SQL Server, Oracle, SQLite 3.8.3+）中，定义递归CTE 必须 使用 WITH RECURSIVE（部分数据库如 SQL Server 允许省略 RECURSIVE，但显式写出是良好实践）。
  2. UNION ALL vs UNION：递归成员几乎总是使用 UNION ALL，因为我们希望保留所有层级的节点（即使名字相同）。使用 UNION 会去重，可能导致层级信息丢失或递归提前终止。
  3. 递归终止条件：
    • 显式终止：可以在递归成员的 WHERE 子句中添加额外的终止条件（例如，限制最大深度 AND parent.level < 5）。
    • 隐式终止：当递归成员不再产生新行时，递归自动终止（这是最常见的情况）。
  4. 避免无限循环：如果数据中存在循环引用（例如 A 的父是 B，B 的父又是 A），递归将陷入死循环。大多数数据库有递归深度限制（如 MAXRECURSION 提示，默认通常是 100）来防止这种情况。设计数据模型时应避免循环。

掌握递归CTE，可以轻松应对各种层级关系挑战，如组织架构、产品分类、权限继承关系等场景。下次遇到“无限级分类”或“树形数据查询”的需求时，不妨试试 WITH RECURSIVE 这把瑞士军刀！

avatar
文章
阅读
粉丝