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

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

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

一、树形结构表设计示例

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

id name parent_id 1 总公司 null 2 技术部 1 3 市场部 1 4 开发组 2 5 测试组 2 6 推广小组 3

二、递归查询基础语法

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;

结果：

id name parent_id 2 技术部 1 4 开发组 2 5 测试组 2

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

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

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

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;

结果类似：

id name parent_id level 1 总公司 null 1 2 技术部 1 2 3 市场部 1 2 4 开发组 2 3 5 测试组 2 3 6 推广小组 3 3

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

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

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;

结果举例：

id name parent_id path 1 总公司 null 总公司 2 技术部 1 总公司->技术部 4 开发组 2 总公司->技术部->开发组

六、SQL递归常见问题解答

1. 数据库版本要求？

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

2. 如何防止死循环？

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

3. 查询性能如何？

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

七、总结

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

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