如何在SQL中生成通往给定节点的层次结构路径? [英] How can I generate a hierarchy path in SQL that leads to a given node?

问题描述

在我的MS SQL 2008 R2数据库中，有以下表格:

In my MS SQL 2008 R2 database I have this table:

TABLE [Hierarchy]
[ParentCategoryId] [uniqueidentifier] NULL,
[ChildCategoryId] [uniqueidentifier] NOT NULL

我需要编写一个查询，该查询将生成通向给定节点的所有路径.

I need to write a query that will generate all paths that lead to a given Node.

可以说，我有以下树:

A
-B
--C
-D
--C

存储为:

NULL | A
A    | B
A    | D
B    | C
D    | C

在询问C的路径时，我想找回两条路径(这样写或多或少):

When asking for the Paths for C, I would like to get back two paths (written more or less like this):

A > B > C,
A > D > C

推荐答案

这是我的解决方案， Sql小提琴

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，您可以尝试

@AlexeiMalashkevich, to just get id, you may try this

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

这篇关于如何在SQL中生成通往给定节点的层次结构路径?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！