SQL SERVER 2008 CTE生成结点的FullPath

203 阅读 0 评论 134 点赞

我是靠谱客的博主干净电脑，最近开发中收集的这篇文章主要介绍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