在我的MS sql 2008 R2数据库中,我有这个表：
TABLE [Hierarchy] [ParentCategoryId] [uniqueidentifier] NULL,[ChildCategoryId] [uniqueidentifier] NOT NULL
让我们说我有以下树：
A -B --C -D --C
哪个将存储为：
NULL | A A | B A | D B | C D | C
在询问C的路径时,我想回到两条路径(写得更多或更少这样)：
A > B > C,A > D > C
解决方法
这是我的解决方案,Sql Fiddle
DECLARE @child VARCHAR(10) = 'C'
;WITH children AS
(
SELECT
ParentCategoryId,CAST(ISNULL(ParentCategoryId + '->','') + ChildCategoryId AS VARCHAR(4000)) AS Path
FROM Hierarchy
WHERE ChildCategoryId = @child
UNION ALL
SELECT
t.ParentCategoryId,list= CAST(ISNULL(t.ParentCategoryId + '->','') + d.Path AS VARCHAR(4000))
FROM Hierarchy t
INNER JOIN children AS d
ON t.ChildCategoryId = d.ParentCategoryId
)
SELECT Path
from children c
WHERE ParentCategoryId IS NULL
输出：
A->D->C A->B->C
更新：
@AlexeiMalashkevich,为了获得身份证,你可以试试这个
DECLARE @child VARCHAR(10) = 'C'
;WITH children AS
(
SELECT
ParentCategoryId,ChildCategoryId AS Path
FROM Hierarchy
WHERE ChildCategoryId = @child
UNION ALL
SELECT
t.ParentCategoryId,d.ParentCategoryId
FROM Hierarchy t
INNER JOIN children AS d
ON t.ChildCategoryId = d.ParentCategoryId
)
SELECT DISTINCT PATH
from children c
