好的，现在来看如何生成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