t-sql递归查询

数据如下

id    name     parentid
--------------------------
1     project   0
2     structure 1
3     path_1    2
4     path_2    2
5     path_3    2
6     path_4    3
7     path_5    4
8     path_6    5

我想以递归方式从上面的数据中形成完整路径.表示递归将提供以下输出.

FullPaths
-------------
Project
ProjectStructure
ProjectStructurePath_1
ProjectStructurePath_2
ProjectStructurePath_3
ProjectStructurePath_1path_4
ProjectStructurePath_2path_5
ProjectStructurePath_3path_6

谢谢

解决方法

declare @t table (id int,name varchar(max),parentid int)

insert into @t select 1,'project',0
union all select 2,'structure',1
union all select 3,'path_1',2
union all select 4,'path_2',2
union all select 5,'path_3',2
union all select 6,'path_4',3
union all select 7,'path_5',4
union all select 8,'path_6',5

; with CteAlias as (
    select id,name,parentid
    from @t t
    where t.parentid = 0
    union all
    select t.id,parent.name + '' + t.name,t.parentid
    from @t t
    inner join CteAlias parent on t.parentid = parent.id
)
select * 
from CteAlias

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!