SQL递归:获得父母与后代之间的所有可能关系

Question

我想运行递归cte，以便使用表1中提供的信息获得表2。

表1：

Employee  | ReportsTo
a           NULL
b           a
c           b
d           c

表2：

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

给出几个示例来说明我的问题，请注意，我错过了员工" D"间接向员工报告" B"之间的关系。或雇员" D"间接向员工报告" A"。

Answer 1

您很接近。您需要从CTE中删除where ReportsTo is null，才能在分支上获取所有节点。（我还添加了一个contat，以便您可以在顶级节点和员工之间看到完整的树路径和所有节点。）

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

Answer 2

尝试一下。

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;