代码之家 › 专栏 › 技术社区 › Paramus

SQL递归:获取父代和子代之间所有可能的关系

recursive-query sql

Paramus · 技术社区 · 7 年前

我想运行一个递归的CTE,以便使用表1中提供的信息获得表2。

表1:

Employee  | ReportsTo
a           NULL
b           a
c           b
d           c

Employee  | ReportsTo  | depth
a           NULL         0
b           a            1
c           a            2
d           a            3
c           b            1
d           b            2
d           c            1

到目前为止,我已经:

with cte
    (Employee
    ,ReportsTo
    ,depth)
as
    (
    select  Employee
            ,ReportsTo
            ,0 as depth
    from    [Table 1]
    where   ReportsTo is null

    UNION ALL

    select  a.Employee
            ,a.ReportsTo
            ,b.depth + 1
    from    cte b
    join [Table 1] a    
            on a.ReportsTo = b.Employee
    )

select  *
from    cte
order by depth

Employee  | ReportsTo | Depth
a           NULL        0
b           a           1
c           b           2
d           c           3

2 回复 | 直到 7 年前

abraxascarab 7 年前

你很接近。您需要删除 where ReportsTo is null

WITH cte
    (TopNode
    ,Employee
    ,ReportsTo
    ,Depth
    ,TreePath)
AS
    (
    SELECT ReportsTo AS TopNode
           ,Employee
           ,ReportsTo
           ,0 AS Depth
           ,CAST(ReportsTo AS VARCHAR(max)) AS TreePath
    FROM   #tblTable1

    UNION ALL

    SELECT cte.TopNode
           ,a.Employee
           ,a.ReportsTo
           ,cte.Depth + 1 AS depth
           ,CAST(cte.TreePath + ' -> ' +
            CAST(a.ReportsTo AS VARCHAR(max)) 
            AS VARCHAR(max)) AS TreePath
    FROM #tblTable1 AS a
    inner join cte
       ON cte.Employee = a.ReportsTo
    )

现在您已经拥有了所有节点/分支,您可以选择那些反映您想要的适当深度的节点/分支。(concat还将最终员工添加到树路径中。) 此选择还为顶部节点提供了正确的零深度。

SELECT
    cte.Employee
    ,cte.TopNode AS ReportsTo
    ,case when cte.ReportsTo is null
             then cte.Depth 
          else cte.Depth + 1
     end AS Depth
    ,case when cte.ReportsTo is null
             then cte.Employee
          else CAST(cte.TreePath + ' -> ' +
               CAST(cte.Employee AS VARCHAR(max)) 
               AS VARCHAR(max)) 
     end AS TreePath
FROM cte
WHERE
   cte.TopNode is not null
   or cte.ReportsTo is null
ORDER BY
   cte.TopNode
   ,cte.Depth;

结果集为:

Employee  ReportsTo   Depth   TreePath
a         NULL        0       a
b         a           1       a -> b
c         a           2       a -> b -> c
d         a           3       a -> b -> c -> d
c         b           1       b -> c
d         b           2       b -> c -> d
d         c           1       c -> d

Serg 7 年前

试试这个。

with cte
    (root
    ,Employee
    ,ReportsTo
    ,depth)
as
    (
    select  ReportsTo root
            ,Employee
            ,ReportsTo
            ,0 as depth
    from    [Table 1] t1

    UNION ALL

    select  b.root
            ,a.Employee
            ,a.ReportsTo
            ,b.depth + 1
    from    cte b
    join [Table 1] a    
            on a.ReportsTo = b.Employee
    )

select  distinct Employee , root ReportsTo,  depth+1 depth
from    cte
where depth = 0 or root is not null --  needn't null as a boss but on 0 level
order by depth;