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
复制代码 代码如下:
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