您现在的位置是：网站首页> 编程资料编程资料

SQL SERVER 2008 CTE生成结点的FullPath_mssql2008_

2023-05-27 433人已围观

简介 SQL SERVER 2008 CTE生成结点的FullPath_mssql2008_

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

您可能感兴趣的文章:

上一篇：SQLSERVER2008中CTE的Split与CLR的性能比较_mssql2008_

下一篇：Sql server 2008 express远程登录实例设置图文教程_mssql2008_

您现在的位置是：网站首页> 编程资料编程资料

SQL SERVER 2008 CTE生成结点的FullPath_mssql2008_

相关内容

点击排行

本栏推荐

猜你喜欢