获取包含SQL Server中所有子记录的记录 [英] Get records containing all the child records in sql server

问题描述

我在sql server中有分层数据。
Follwing是我的数据继承树。

I have heirarchical data in sql server. Follwing are my data heirarcy tree.

Id      Name         ParentID

1124    ABC          2
1125    BCD          1124
1126    EFG          1124   
1127    HIJ          1126   
1128    KLM          1126
1129    OPQ          1124   
1130    RST          1124
1131    UVW          1130
1132    XYZ          1131
1133    ZYA1         1124

现在我要从上表中得出以下结果。在这种情况下，我希望从所有孩子的根（父母）到最后一个孩子的路径。

Now i want following result from above table. In that i want path from root (parent) with all child till last child.

Id      Name    ParentID    PATH

1124    ABC     2           ABC
1125    BCD     1124        ABC/BCD
1126    EFG     1124        ABC/EFG
1127    HIJ     1126        ABC/EFG/HIJ
1128    KLM     1126        ABC/EFG/KLM
1129    OPQ     1124        ABC/OPQ
1130    RST     1124        ABC/RST
1131    UVW     1130        ABC/RST/UVW
1132    XYZ     1131        ABC/RST/UVW/XYZ
1133    ZYA1    1124        ABC/ZYA1

所以有人可以帮助我编写sql查询以找出包含从父项到所有子项的所有记录的路径。

So can anyone help me to write the sql query to find out the path which containing all the records from parent to its all containing child.

预先感谢。

推荐答案

您需要使用递归公用表表达式，然后仅针对每条记录的基本路径过滤结果（例如，对于ID 1131，获取 ABC / RST / UVW 而不仅仅是 ABC / RST

You need to use a recursive common table expression, then filter the results for only the base path for each record, (i.e. for ID 1131 get ABC/RST/UVW and not just ABC/RST

WITH CTE AS
(   SELECT  ID, 
            Name, 
            ParentID, 
            NextParentID = ParentID, 
            Path = CAST(Name AS VARCHAR(MAX)),
            Recursion = 1
    FROM    T
    UNION ALL
    SELECT  CTE.ID, 
            CTE.Name, 
            CTE.ParentID, 
            T.ParentID, 
            CAST(T.Name + '/' + CTE.Path AS VARCHAR(MAX)),
            Recursion + 1
    FROM    CTE
            INNER JOIN T
                ON CTE.NextParentID = T.ID
), CTE2 AS
(   SELECT  CTE.ID,
            CTE.Name,
            CTE.ParentID,
            CTE.Path,
            RowNumber = ROW_NUMBER() OVER(PARTITION BY CTE.ID ORDER BY Recursion DESC)
    FROM    CTE
)
SELECT  ID, Name, ParentID, Path
FROM    CTE2
WHERE   RowNumber = 1;

SQL小提琴上的示例

Example on SQL Fiddle

这篇关于获取包含SQL Server中所有子记录的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！