sql递归查询所有字节点cte

1、数据表：

 

2、sql语句：

;with cte as
(
    select id,pid,groupName,0 as nLevel,IsNull(cast(pid as varchar),'')+'/'+cast(id as varchar) as paths from tb_RemindUserGroup where IsNull(pid,0)=0 
    union all
    select a.id,a.pid,a.groupName,cte.nLevel+1,IsNull(cast(cte.paths as varchar),'')+'/'+cast(a.id as varchar)
    from tb_RemindUserGroup a join cte on a.pid = cte.id
)         
select * from cte order by paths

 

3、执行结果：