如何用SQL递归CTE实现树形结构分支的完整根到叶遍历输出

原有的递归CTE查询代码如下：

declare @table as table
 (
  Id int,
  ParentId int
 )


 insert into @table
 values (1 , null),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3)


  ;with cte 
    as (select Id,ParentId,1 as [level]
        from @table as t 
        where ParentId is null
        UNION ALL
        select t.Id,t.ParentId , cte.level + 1
        from @table as t 
        join cte 
        on t.ParentId = cte.Id)
    select *
    from cte

该查询得到的结果为：

现在需要实现树形结构的分支化完整遍历：先完整展示根节点（Id=1）→子节点2→孙节点4、5的分支，再完整展示根节点1→子节点3→孙节点6、7的分支，期望输出如下：

修改后的查询代码 #

要实现这种分支优先的完整遍历，需要为每个分支生成独立的路径标识，再按路径分组展开完整分支：

declare @table as table
 (
  Id int,
  ParentId int
 )

insert into @table
values (1 , null),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3);

-- 生成所有节点的完整路径，用于区分不同分支
with node_paths as (
    select 
        Id, 
        ParentId, 
        1 as [level],
        cast(Id as varchar(max)) as path
    from @table
    where ParentId is null
    union all
    select 
        t.Id, 
        t.ParentId, 
        np.level + 1,
        np.path + '>' + cast(t.Id as varchar(max))
    from @table t
    join node_paths np on t.ParentId = np.Id
),
-- 获取所有叶子节点的路径，每个叶子节点对应一条从根到叶的完整分支
leaf_paths as (
    select path
    from node_paths np
    where not exists (select 1 from @table t where t.ParentId = np.Id)
)
-- 根据每个叶子节点的路径，递归展开对应的完整分支
select 
    cast(split.value as int) as Id,
    case 
        when charindex('>', p.path) = 0 then null
        else cast(substring(p.path, 1, charindex('>', p.path)-1) as int)
    end as ParentId,
    row_number() over(partition by p.path order by (select 0)) as [level]
from leaf_paths p
cross apply string_split(p.path, '>') split
order by p.path, [level];

逻辑说明 #

1. node_paths：递归生成每个节点的完整路径（如1>2>4），路径用于唯一标识每个分支。
2. leaf_paths：筛选出所有叶子节点的路径，每个叶子节点对应一条从根到叶的完整分支。
3. 最终查询：通过string_split拆分每个叶子节点的路径，生成对应的ParentId和Level，最后按路径排序，实现分支的完整展开。

内容的提问来源于stack exchange，提问作者Zoyeb Shaikh