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

在实际开发中，我们经常会遇到树形结构数据的处理需求，比如菜单、组织架构、评论回复等。这类结构的数据通常存储在数据库的同一张表中，通过id和parent_id字段来实现层级关系。如何高效地在SQL中遍历或查询这样的树形结构？答案就是递归查询。

本文将详细介绍如何利用SQL的WITH ... AS语法（公用表表达式，简称CTE），快速、优雅地进行树形结构的遍历和查询。

一、树形结构表设计示例

假设我们有一张组织架构表organization，结构如下：

二、递归查询基础语法

WITH ... AS用来定义临时的视图，在SQL标准中扩展为递归CTE支持。基本语法如下：

WITH RECURSIVE cte_name AS (
    -- 基础部分（锚点）
    SELECT ...
    FROM ...
    WHERE ...
    UNION ALL
    -- 递归部分
    SELECT ...
    FROM ...
    INNER JOIN cte_name ON ...
)
SELECT * FROM cte_name;

注意：不同数据库关键词略有差异，MySQL、PostgreSQL 需要WITH RECURSIVE，SQL Server 用WITH即可。

三、实战：查询某部门下的所有子部门

比如我要查“技术部”（id=2）下的所有子部门：

WITH RECURSIVE dept_tree AS (
    -- 基础查询：选择技术部本身
    SELECT id, name, parent_id
    FROM organization
    WHERE id = 2
    UNION ALL
    -- 递归查询：选择其下的子部门
    SELECT o.id, o.name, o.parent_id
    FROM organization o
    INNER JOIN dept_tree dt ON o.parent_id = dt.id
)
SELECT * FROM dept_tree;

结果：

可以看到，“技术部”及其所有下级部门都被查询了出来，不论子级有多少层。

四、查询整个树的所有数据及其层级

有时候我们想查出所有数据，并且知道每条记录在树里的层级（深度）。可以这样写：

WITH RECURSIVE org_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM organization
    WHERE parent_id IS NULL  -- 顶层部门
    UNION ALL
    SELECT o.id, o.name, o.parent_id, t.level + 1
    FROM organization o
    INNER JOIN org_tree t ON o.parent_id = t.id
)
SELECT * FROM org_tree ORDER BY level, id;

结果类似：

五、进一步：路径记录与排序

如果想记录每条数据在组织中的完整路径，可以使用字符串拼接的方式：

WITH RECURSIVE org_path AS (
    SELECT id, name, parent_id, name AS path
    FROM organization
    WHERE parent_id IS NULL
    UNION ALL
    SELECT o.id, o.name, o.parent_id, CONCAT(op.path, '->', o.name)
    FROM organization o
    INNER JOIN org_path op ON o.parent_id = op.id
)
SELECT * FROM org_path;

结果举例：

六、SQL递归常见问题解答

1. 数据库版本要求？

• MySQL 8.0+、PostgreSQL、SQL Server 2005+ 都支持递归CTE。
• 旧版MySQL（如5.7）不支持递归CTE，可以用存储过程或多次查询模拟。

2. 如何防止死循环？

• 一般表结构不会形成环形引用（parent_id指向自身或祖先），实际使用前要对数据做校验。

3. 查询性能如何？

• 采用递归CTE已非常高效，但数据量大时可以考虑生成冗余的路径字段，或采用闭包表等优化方式。

七、总结

WITH AS+递归查询让我们写SQL时能非常直观地处理树形结构。不仅能查询所有子节点、祖先节点，还能计算深度、拼接路径，让复杂的业务需求变得简单、优美。

建议后端开发者和DBA多了解和掌握这项技术，能够极大提升你在项目数据处理中的解决问题能力。