如何查询SQL中层级结构的所有子节点_利用子查询或CTE实现树形遍历-SQL-PHP中文网

mysql 8.0+ 必须用 with recursive 实现多层子节点递归查询，其结构由锚点查询（起始节点）和递归查询（inner join 自引用 + union all）组成，需确保列数类型一致、连接条件正确（o.parent_id = t.id）、where 隐式终止，否则报错或死循环。

如何查询sql中层级结构的所有子节点_利用子查询或cte实现树形遍历

MySQL 8.0+ 怎么用 `WITH RECURSIVE` 查出某个节点的所有子节点

直接上手：必须用 WITH RECURSIVE，普通子查询做不到多层递归。CTE 的递归部分由 anchor（初始查询）和 recursive member（自连接）组成，缺一不可。

常见错误是漏写 UNION ALL、递归查询里没加终止条件（比如 WHERE 过滤父 ID）、或递归引用了错误的别名。

示例（假设表 org 有 id 和 parent_id）：

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name
  FROM org
  WHERE id = 1  -- 起始节点
  UNION ALL
  SELECT o.id, o.parent_id, o.name
  FROM org o
  INNER JOIN tree t ON o.parent_id = t.id
)
SELECT * FROM tree;

anchor 部分必须能单独执行，不能依赖递归结果
递归分支中 INNER JOIN 的连接条件必须指向上一层的 id（不是 parent_id），否则会跳层或死循环
MySQL 默认递归深度限制为 1000，超深树要临时调大：SET SESSION cte_max_recursion_depth = 5000;

PostgreSQL 中 `WITH RECURSIVE` 的关键差异点

语法结构一致，但行为更严格：不支持在递归成员中使用聚合、窗口函数或 GROUP BY；另外，UNION 和 UNION ALL 在递归 CTE 中效果不同——必须用 UNION ALL，否则可能意外去重导致漏节点。

容易踩的坑是列顺序和数据类型不一致：anchor 和 recursive 查询的对应列必须类型兼容，否则报错 recursive query "xxx" column "yyy" has type xxx but expression has type yyy。

如果想查路径或层级深度，可加计算字段：

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS level
  FROM org
  WHERE id = 1
  UNION ALL
  SELECT o.id, o.parent_id, o.name, t.level + 1
  FROM org o
  JOIN tree t ON o.parent_id = t.id
)
SELECT * FROM tree ORDER BY level;

SQL Server 怎么用 `WITH` 实现相同效果（不带 RECURSIVE 关键字）

SQL Server 的递归 CTE 不需要显式写 RECURSIVE，但规则一样严格：anchor 必须在前，UNION ALL 后接递归部分，且递归查询只能引用 CTE 自身一次。

典型错误是把 WHERE 条件写在递归分支外层（比如放在最终 SELECT 后），这会导致全树扫描后再过滤，性能极差；正确做法是在递归 JOIN 后立即用 WHERE 限定父子关系。

另外注意：SQL Server 默认最大递归数是 100，超限报错 Msg 530，需加 OPTION (MAXRECURSION n)，设为 0 表示无限制（慎用）：

WITH tree AS (
  SELECT id, parent_id, name
  FROM org
  WHERE id = 1
  UNION ALL
  SELECT o.id, o.parent_id, o.name
  FROM org o
  INNER JOIN tree t ON o.parent_id = t.id
)
SELECT * FROM tree OPTION (MAXRECURSION 0);