原生递归CTE是SQL Server、MySQL 8.0+和PostgreSQL处理树形结构的标准方案,可直接生成带层级、路径及排序能力的扁平结果集,避免存储过程中手写循环或临时表;需严格注意类型对齐、长度预留、空值处理及外层聚合。

别在存储过程中手写循环或临时表来拼树路径——SQL Server、MySQL 8.0+、PostgreSQL 都支持原生递归 CTE,它能直接生成带层级、带路径、可排序的扁平结果集,后续再聚合或格式化更稳。
SQL Server 里用 CTE 构造 path 字段必须显式 CAST + 拼接
SQL Server 不支持 CONCAT 在旧版本(如 2012),且递归 CTE 中字符串拼接若类型不一致会隐式截断或报错。锚成员和递归成员的 path 列必须同类型、同长度,否则 UNION ALL 直接失败。
- 锚成员用
CAST(id AS VARCHAR(200))而不是CONVERT(VARCHAR, id),避免默认长度为 30 导致深层路径被截断 - 递归部分用
CAST(t.path + '.' + CAST(o.id AS VARCHAR(10)) AS VARCHAR(200)),两次 CAST 缺一不可 - 如果表里
id是BIGINT,CAST(o.id AS VARCHAR(10))会溢出,得改用VARCHAR(20) - 别用
+连接时漏空值判断:ISNULL(t.path + '.', '') + CAST(...),否则某节点parent_id为 NULL 会导致整条 path 变 NULL
MySQL 8.0+ 的 WITH RECURSIVE 要靠 path 排序才能保证父子紧邻
只查出 level 和 id 不够——ORDER BY level, id 会把所有 level=2 的节点堆在一起,破坏树形视觉顺序。真正起作用的是递归中构造的 path 字符串,它天然携带拓扑信息。
- 锚成员定义
path为CAST(id AS CHAR(200)),不是id数字本身 - 递归部分用
CONCAT(t.path, '/', c.id),斜杠分隔比点号更利于后期按层级切分 -
ORDER BY path必须写在最终SELECT后,不能放在 CTE 内;否则优化器可能忽略 - 如果同级要按 name 排序,得在递归中加
ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY c.name)并拼进 path,但会显著拖慢性能
路径汇总统计必须在外层 GROUP BY,CTE 内不能聚合
递归 CTE 展开的是逻辑视图,不是物理表。你在 CTE 里写 COUNT(*) OVER (PARTITION BY level) 或 SUM(amount) 会报错或结果错乱,因为窗口函数无法跨递归层级正确累积。
- 想算每级节点数:外层包一层
SELECT level, COUNT(*) FROM Tree GROUP BY level - 想算每个根节点下的子节点总数:先在 CTE 中带上
root_id(锚查询传入),再外层GROUP BY root_id - 想汇总金额并防重复计算(比如父节点金额含子节点):必须明确业务语义——是“仅本级”还是“包含后代”,后者需在递归中累加:
t.total_amount + ISNULL(o.amount, 0) - 别在 CTE 里
SELECT *后直接GROUP BY——列名未显式声明易触发类型推导失败
PostgreSQL 用 ltree 类型省事但要注意索引和长度限制
如果你的路径是固定格式(如 'org.chn.beijing.hr'),ltree 比手拼字符串更可靠,但它是扩展类型,不是所有环境默认启用,且有硬性约束。
- 建表前先
CREATE EXTENSION IF NOT EXISTS ltree;,否则path ltree字段建不起来 -
ltree值总长不能超 256 字节,路径段太多(如 20 级部门)容易溢出,得提前规划分段粒度 - 查子树用
path @> 'org.chn',查祖先用'org.chn.beijing' ~ 'org.chn.*',符号别记混 - 必须配
GIST索引才高效:CREATE INDEX idx_path_gist ON orgs USING GIST (path);,B-tree 索引对 ltree 查询无效
最常被跳过的一步是路径字段的类型对齐和长度预估——少 cast 一次、少预留 10 个字符,到第 15 层就截断,而错误往往只在生产大数据量时暴露。











