SQL SERVER 2008 CTE生成结点的FullPath

好的，现在来看如何生成FullPath:
<div class=”codetitle”><a style=”CURSOR: pointer” data=”9630″ class=”copybut” id=”copybut9630″ onclick=”doCopy(‘code9630’)”> 代码如下:<div class=”codebody” id=”code9630″>
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,19
就这么简单，实际上有Sql server 2008中HierarchyType 也能很好的解决这个问题。我将在后面写一些关于HierarchyType的Post. 希望这篇POST对您有帮助。 Author Peter Liu