欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL SERVER 2008 CTE生成结点的FullPath

程序员文章站 2023-09-06 19:49:18
好的,现在来看如何生成fullpath: 复制代码 代码如下: declare @tbl table ( id int ,parentid int ) insert int...
好的,现在来看如何生成fullpath:
复制代码 代码如下:

declare @tbl table
(
id int
,parentid int
)
insert into @tbl
( id, parentid )
values ( 0, null )
, ( 8, 0 )
, ( 12, 8 )
, ( 16, 12 )
, ( 17, 16 )
, ( 18, 17 )
, ( 19, 17 )

with abcd
as (
-- anchor
select id
,parentid
,cast(id as varchar(100)) as [path]
from @tbl
where parentid is null
union all
--recursive member
select t.id
,t.parentid
,cast(a.[path] + ',' + cast( t.id as varchar(100)) as varchar(100)) as [path]
from @tbl as t
join abcd as a on t.parentid = a.id
)
select id ,parentid ,[path]
from abcd
where id not in ( select parentid
from @tbl
where parentid is not null )

返回:
id parentid path
----------- ----------- ----------------------
18 17 0,8,12,16,17,18
19 17 0,8,12,16,17,19
就这么简单,实际上有sql server 2008中hierarchytype 也能很好的解决这个问题。我将在后面写一些关于hierarchytype的post.

希望这篇post对您有帮助。

author peter liu