无论位置如何，都使用CTE获得EMP的层次结构

Question

我正在考虑使用CTE创建层次结构查询。但是，我在Where子句上缺少一些内容，查询应返回员工的完整层次结构

这是我创建的SQL小提琴。

预期结果：

对于id = 3，我应该得到这些结果：

ParentEmpId Id          Name 
----------- ----------- -----
NULL        1           A    
1           3           C    
3           6           F     

对于id = 2，我应该得到这些结果：

ParentEmpId Id          Name 
----------- ----------- -----
NULL        1           A    
1           2           B    
2           4           D     
2           5           E     

Answer 1

从您的预期输出中，似乎您需要id的孩子和父母。因此，您需要一个针对孩子的等级制度，而父母则需要一个：

WITH EmpCTE(ParentEmpId, Id, [Name], [Level]) AS
(
    SELECT ParentEmpId, Id, [Name], 0 AS [Level]
    FROM emp
    WHERE id=3
         UNION ALL
    SELECT E.ParentEmpId, E.Id, E.[Name], [Level] + 1
    FROM emp E
        INNER JOIN EmpCTE empCTE
        ON E.Id = EmpCTE.ParentEmpId 
),
 EmpCTE2(ParentEmpId, Id, [Name], [Level]) AS
(
    SELECT ParentEmpId, Id, [Name], 0 AS [Level]
    FROM emp
    WHERE id=3
         UNION ALL
    SELECT E.ParentEmpId, E.Id, E.[Name], [Level] + 1
    FROM emp E
        INNER JOIN EmpCTE2 empCTE2
        ON E.ParentEmpId = EmpCTE2.Id 
)
Select * from (
select * from EmpCTE 
Union 
select * from EmpCTE2 ) a
order by name

Answer 2

您使用的当前CTE生成了给定id的 descing 层次结构，而不是 parent ersarchy。

一种解决方案是创建第二个递归cte来生成父层次结构，然后 UNION两者都如下：

WITH EmpCTE(ParentEmpId, Id, [Name], [Level]) AS
(
    SELECT ParentEmpId, Id, [Name], 0 AS [Level]
    FROM emp
    WHERE id = 2
    UNION ALL
    SELECT E.ParentEmpId, E.Id, E.[Name], [Level] + 1
    FROM emp E
        INNER JOIN EmpCTE empCTE
        ON E.ParentEmpId = EmpCTE.id
),
EmpCTE2(ParentEmpId, Id, [Name], [Level]) AS
(
    SELECT ParentEmpId, Id, [Name], 0 AS [Level]
    FROM emp
    WHERE id = 2
    UNION ALL
    SELECT E.ParentEmpId, E.Id, E.[Name], [Level] + 1
    FROM emp E
        INNER JOIN EmpCTE2 empCTE2
        ON E.id = EmpCTE2.ParentEmpId
)
SELECT * FROM EmpCTE
UNION 
SELECT * FROM EmpCTE2
ORDER BY [name]

在此中，更新了DB小提琴，当给出id = 2时，查询返回：

ParentEmpId  Id  Name  Level
(null)       1   A     1
1            2   B     0
2            4   D     1
2            5   E     1

Answer 3

为什么不这样？

select data.*
from emp e
  cross apply (
    select * 
    from emp e1
    where e1.Id=e.Id
    union all
    select *
    from emp e2
    where e2.ParentEmpId=e.Id
    union all
    select e4.*
    from emp e3
      join emp e4
        on e3.ParentEmpId=e4.Id
    where e3.Id=e.Id
  ) data
where e.Id=3