如何在SQL中通过JOIN实现树形结构数据的全路径展开-SQL-PHP中文网

直接用join展开树形路径行不通，因其仅支持固定层级、无法处理动态深度，且不能生成可排序的结构化路径字符串；正确解法是使用with recursive递归cte，分锚点和递归体两部分逐层展开并累积路径。

如何在sql中通过join实现树形结构数据的全路径展开

为什么直接用 JOIN 展开树形路径行不通

SQL 的 JOIN 本身是扁平的二元关系操作，不带递归能力。想靠几层 LEFT JOIN 自关联把无限深度的树（比如组织架构、分类目录）全展开，会立刻遇到两个硬伤：一是深度不确定，你没法预设连多少次；二是即使硬写 5 层 JOIN，第 6 层节点就彻底丢失，且结果里会出现大量 NULL 填充列，难过滤、难读取。

常见错误现象：SELECT t1.name, t2.name, t3.name FROM categories t1 LEFT JOIN categories t2 ON t2.parent_id = t1.id LEFT JOIN categories t3 ON t3.parent_id = t2.id —— 看似能拿到三级路径，但实际只覆盖固定深度，且无法拼出“/一级/二级/三级”这样的完整路径字符串。

标准解法：用 WITH RECURSIVE 构建递归 CTE

所有主流 SQL 引擎（PostgreSQL、SQL Server、SQLite 3.8.3+、MySQL 8.0+）都支持递归 CTE，这才是展开树形路径的正确入口。核心思路是分两步：锚点（根节点） + 递归成员（逐层找子节点），并在过程中累积路径字符串。

锚点查询必须明确指定起始条件，例如 WHERE parent_id IS NULL 或 WHERE id = 1
递归部分必须引用自身 CTE 名，并通过 ON 关联上一层的 id 和当前层的 parent_id
路径拼接推荐用 ||（PostgreSQL/SQLite）或 CONCAT()（MySQL），避免用 +（SQL Server 中可能触发隐式类型转换错误）
必须设置递归深度限制（如 MAXRECURSION 100 in SQL Server，或 PostgreSQL 的 SET search_path TO ... 配合 max_recursive_depth 参数），否则环形引用会死循环

示例（PostgreSQL）：

WITH RECURSIVE tree_path AS (
  SELECT id, name, parent_id, CAST(name AS TEXT) AS path, 1 AS level
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, tp.path || '/' || c.name, tp.level + 1
  FROM categories c
  INNER JOIN tree_path tp ON c.parent_id = tp.id
)
SELECT * FROM tree_path ORDER BY path;

MySQL 8.0+ 特别注意字段别名与 CONCAT 的坑

MySQL 对递归 CTE 的字段类型推导较严格，如果锚点中 path 是 VARCHAR(50)，而递归分支拼接后超长，会导致截断无声失败。另外 CONCAT() 遇到任一参数为 NULL 时整结果为 NULL，必须显式用 COALESCE() 处理。

锚点里的 path 字段类型要预留足够长度，例如 CAST(name AS CHAR(512))
递归部分路径拼接务必写成 CONCAT(tp.path, '/', COALESCE(c.name, ''))
MySQL 不支持 MAXRECURSION 提示，需靠 SET cte_max_recursion_depth = 100 全局控制，且该值不能超过 @@cte_max_recursion_depth 系统变量上限

不想用递归？临时表 + 循环是唯一退路

在不支持递归 CTE 的旧版数据库（如 MySQL 5.7、SQL Server 2005 以下）中，没有优雅方案。只能用存储过程模拟迭代：先插入所有根节点到临时表，再用 WHILE 循环反复 INSERT INTO #tmp SELECT ... FROM source JOIN #tmp，每次扩展一层，并用额外字段记录当前路径和层级。