SQL Server父子关系（父级查看所有内容）？

Question

这是我的表格：

EmployeeID  Employee    ManagerID
---------------------------------
    1       Anna           5
    2       John           4
    3       Steve          4
    4       Lisa           1
    5       Adam          NULL
    6       Per            1

使用自连接可以轻松获取父子关系，例如：

SELECT 
    E.EmployeeID,
    E.Employee AS Employee,
    E.ManagerID,
    M.Employee AS Manager
FROM 
    Employee AS E
LEFT JOIN 
    Employee AS M ON E.ManagerID = M.EmployeeID

EmployeeID  Employee    ManagerID   Manager
1   Anna    5   Adam
2   John    4   Lisa
3   Steve   4   Lisa
4   Lisa    1   Anna
5   Adam    NULL NULL
6   Per     1    Anna

然而，我该如何确保父级能够看到整个层次结构？

我希望表格呈现以下形式：

EmployeeID  Manager Employee    EmployeeID  
5   Adam    Anna    1
5   Adam    Per     6
5   Adam    Lisa    4
5   Adam    John    2
5   Adam    Steve   3
1   Anna    Per     6
1   Anna    Lisa    4
1   Anna    John    2
1   Anna    Steve   3
4   Lisa    John    2
4   Lisa    Steve   3

注意：在这个例子中，我只有三个管理层级别，但实际上可能还有更多。

- Nils

---

SQL Server有一个hierarchyId类型来实现此功能。 - Panagiotis Kanavos

另一种方法是使用递归CTE。根据记录数量和级别数量，您可能会发现这种方法有点慢。 - David Rushton

嗨，是的，我一直在研究递归CTE，但并没有完全理解如何在不了解级别的情况下使用它。您有这个问题的示例可以提供给我吗？ - Nils

Answer 1

你可以尝试这个：

DECLARE @DataSource TABLE
(
    [EmployeeID] TINYINT
   ,[Employee] VARCHAR(12)
   ,[ManagerID] TINYINT 
);

INSERT INTO @DataSource ([EmployeeID], [Employee], [ManagerID])
VALUES (1, 'Anna', 5)
      ,(2, 'John', 4)
      ,(3, 'Steve', 4)
      ,(4, 'Lisa', 1)
      ,(5, 'Adam', NULL)
      ,(6, 'Per',  1);

WITH DataSource AS 
(
    SELECT DISTINCT DS1.*
                   ,0 AS [Level]
                   ,DS1.[EmployeeID] AS Parent
    FROM @DataSource DS1
    INNER JOIN @DataSource DS2
        ON DS1.[EmployeeID] = DS2.[ManagerID]
    UNION ALL
    SELECT DS2.*
          ,DS1.[Level] + 1
          ,DS1.Parent
    FROM DataSource DS1
    INNER JOIN @DataSource DS2
        ON DS1.[EmployeeID] = DS2.[ManagerID]
)
SELECT DS1.[EmployeeID] 
      ,DS1.[Employee] AS [Manager]
      ,DS.[EmployeeID]
      ,DS.[Employee]
FROM DataSource DS
INNER JOIN @DataSource DS1
    ON DS.[Parent] = DS1.[EmployeeID]
WHERE DS.[Level] <> 0
ORDER BY DS.[Parent] DESC;

我们正在使用递归CTE，如果你第一次看到这个语法可能会感觉有些混乱和复杂，但其实没什么特别的。

当使用递归CTE时，请进行一些性能测试，以确保它是解决您问题的正确技术。

- gotqn

Answer 2

你应该使用递归CTE语法。在第一次迭代（UNION ALL之前），你会得到所有的父子对。在递归部分（UNION ALL之后），你会得到每个对应的下一级子节点，并将其替换为原本的子节点，同时保留父节点不变。

WITH CTE AS 
(
   SELECT TP.EmployeeID as ManagerId, 
          TP.Employee as Manager,
          TC.EmployeeID as EmployeeID, 
          TC.Employee as Employee

          FROM TEmployee as TP
          JOIN TEmployee as TC on (TP.EmployeeID = TC.ManagerID)

          UNION ALL

   SELECT TP.ManagerId as ManagerId, 
          TP.Manager as Manager,
          TC.EmployeeID as EmployeeID, 
          TC.Employee as Employee

          FROM CTE as TP
          JOIN TEmployee as TC on (TP.EmployeeID = TC.ManagerID)
)
SELECT * FROM CTE Order By ManagerID

结果：

+-----------+---------+------------+----------+
| ManagerId | Manager | EmployeeID | Employee |
+-----------+---------+------------+----------+
|         1 | Anna    |          4 | Lisa     |
|         1 | Anna    |          6 | Per      |
|         1 | Anna    |          2 | John     |
|         1 | Anna    |          3 | Steve    |
|         4 | Lisa    |          2 | John     |
|         4 | Lisa    |          3 | Steve    |
|         5 | Adam    |          1 | Anna     |
|         5 | Adam    |          4 | Lisa     |
|         5 | Adam    |          6 | Per      |
|         5 | Adam    |          2 | John     |
|         5 | Adam    |          3 | Steve    |
+-----------+---------+------------+----------+

- valex

---

这太完美了，递归CTE在这里真的非常有用，谢谢你。我会将你和@gotqn的答案标记为正确，因为他先回答了。然而，这个答案是一个很好的解释。 - Nils