sql server语言 - 如何在sql中生成通向给定节点的层次结构路径?

Question

在我的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

Answer 1

这是我的解决方案，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，要获取id，你可以试试这个

SQL小提琴

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

Answer 2

可能的解决方案是使用@a_horse_with_no_name:

所提到的递归CTE

CREATE TABLE [Hierarchy](
[ParentCategoryId] CHAR(1) NULL,
[ChildCategoryId] CHAR(1) NOT NULL
);

INSERT INTO Hierarchy
SELECT NULL, 'A' UNION ALL
SELECT 'A', 'B' UNION ALL
SELECT 'A', 'D' UNION ALL
SELECT 'B', 'C' UNION ALL
SELECT 'D', 'C';

WITH CTE AS (
    SELECT 
        ParentCategoryId, ChildCategoryId, 
        CAST(ISNULL(ParentCategoryId,'') + ChildCategoryId AS VARCHAR(255)) [Path] 
    FROM Hierarchy
    WHERE ParentCategoryId IS NULL
    UNION ALL
    SELECT 
        H.ParentCategoryId, H.ChildCategoryId, 
        CAST(C.[Path] + ' > ' + H.ChildCategoryId AS VARCHAR(255)) [Path] 
    FROM Hierarchy H
    INNER JOIN CTE C ON C.ChildCategoryId = H.ParentCategoryId
) SELECT * FROM CTE;

Answer 3

这是个有趣的层次结构。似乎允许父母成为他们孩子的孩子。如果发生这种情况，代码逻辑就会中断，但只要不发生这种情况，就应该正常工作。

Create  Function dbo.IdentifyHierarchyPaths (@DeepestChildNode UniqueIdentifier)
Returns @hierarchy Table
(
        Hierarchy Varchar(Max)
)
As
Begin
        ;With   BuildHier As
        (
                Select  Convert(Varchar(Max),h2.ChildCategoryId) As child, Convert(Varchar(Max),h1.ChildCategoryId) + ' > ' +  Convert(Varchar(Max),h2.ChildCategoryId) As hier
                From    Hierarchy h1
                Left    Join Hierarchy h2
                        On  h1.ChildCategoryId = h2.ParentCategoryId
                Where   h1.ParentCategoryId Is Null
                Union   All
                Select  Convert(Varchar(Max),h1.ChildCategoryId) As child, bh.hier + ' > ' +  Convert(Varchar(Max),h1.ChildCategoryId) As hier
                From    BuildHier bh
                Join    Hierarchy h1
                        On  bh.child = h1.ParentCategoryId
        ),      HierWithTopLevel As
        (
                Select  Convert(Varchar(Max),ChildCategoryId) As hierarchy
                From    Hierarchy
                Where   ParentCategoryId Is Null
                Union   
                Select  hier
                From    BuildHier
        )
        Insert  @hierarchy
        Select  hierarchy
        From    HierWithTopLevel
        Where   Right(hierarchy,36) = Convert(Varchar(36),@DeepestChildNode);
        Return;
End;