MsSql教程网-SQL SERVER 2008 CTE生成结点的FullPath

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

共享资源网提供最优质的资源集合。
共享资源网 » MsSql教程网-SQL SERVER 2008 CTE生成结点的FullPath

共享资源网提供最优质的资源集合

充值中心 开启会员