|
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对您有帮助。 (责任编辑：IT) |