时间戳

首页 > 解决方案 > 如何使用 CTE 递归来构建组织层次结构？

sql-server - 如何使用 CTE 递归来构建组织层次结构？

问题描述

我正在尝试编写一个为给定经理构建层次结构的函数。它不仅应该返回向指定经理汇报的人员，还应该返回向这些人员汇报的人员等等。

我倾向于使用递归 CTE，但我无法让它工作。

我有一张ManagerAssignments映射ManagerUserIDs到EmployeeIDs他们管理的员工的表格。ManagerUserIDs是VARCHARs，EmployeeIDs是INT。所以经理可以有UserID'Bob'和EmployeeID1。

我还有UserIDEmployeeIDMapping一张将给定UserID与EmployeeID. 并非所有员工都有UserID.

该ManagerAssignments表如下所示：

|ManagerUserID|EmployeeID|
|   Bob       | 1        |
|   Bob       | 2        |
|   Bob       | 3        |
|   Steve     | 4        |
|   Jim       | 5        |
|   Jim       | 6        |
|   Steve     | 7        |

该UserIDEmployeeIDMapping表如下所示：

|UserID|EmployeeID|
|Bob   |    10    |
|Steve |    1     |
|Jim   |    7     |

因此，如果我选择 Bob，我应该得到员工 1、2、3 和 4，因为 Steve 向 Bob 汇报。如果我选择 Steve，我应该得到 4、5、6 和 7，因为 Jim 向 Steve 汇报。如果我选择 Jim，我应该只得到 5 和 6。

如果给定的员工没有条目UserIDEmployeeIDMapping或没有ManagerUserID条目，则他们是叶节点ManagerAssignments

我正在努力解决的是额外的间接级别，必须查看给定员工是否具有UserID. 我尝试使用表变量将两个表合并为一个带有ManagerUserID、EmployeeID和EmployeeUserID字段的表，并在 CTE 中使用它。但这样我只能得到一个层次结构。

DECLARE @UserID VARCHAR(150)
SET @UserID = 'Bob'

DECLARE @Temp TABLE
(
    ManagerUserID VARCHAR(150),
    EmployeeID INT,
    EmployeeUserID VARCHAR(150)
)

INSERT INTO @Temp
SELECT MA.ManagerUserID, MA.EmployeeID, UE.UserID AS EmployeeUserID
FROM ManagerAssignments MA LEFT JOIN UserIDEmployeeIDMapping UE ON MA.EmployeeID = UE.EmployeeID

;WITH OrgChart (ManagerUserID, EmployeeID, EmployeeUserID) AS
(
        SELECT ManagerUserID, EmployeeID, EmployeeUserID
        FROM @Temp
        WHERE ManagerUserID = @UserID
        UNION ALL 
        SELECT T.ManagerUserID, T.EmployeeID, T.EmployeeUserID
        FROM @Temp AS T INNER JOIN OrgChart OC ON T.ManagerUserID = OC.ManagerUserID
        WHERE T.ManagerUserID <> @UserID
)

UserID“鲍勃”的预期输出：

|ManagerUserID|EmployeeID|EmployeeUserID|
|  Bob        |  1       | Steve        |
|  Bob        |  2       | NULL         |
|  Bob        |  3       | NULL         |
|  Steve      |  4       | NULL         |

标签： sql-serversql-server-2012

解决方案

尝试这个：

;WITH cteOrgChart(ManagerUserID,ManagerEmployeeID,EmployeeID, EmployeeUserID)
AS(
  SELECT MA.ManagerUserID, UE.EmployeeID AS ManagerEmployeeID, MA.EmployeeID, EU.UserID AS EmployeeUserID
  FROM ManagerAssignments MA
  INNER JOIN UserIDEmployeeIDMapping UE 
  ON MA.ManagerUserID = UE.UserID
  INNER JOIN UserIDEmployeeIDMapping EU
  ON MA.EmployeeID=EU.EmployeeID
  WHERE MA.ManagerUserID='Bob'
  UNION ALL
  SELECT O.EmployeeUserID, O.EmployeeID, A.EmployeeID, M.UserID
  FROM cteOrgChart O
  LEFT JOIN ManagerAssignments A
  ON O.EmployeeUserID=A.ManagerUserID
  LEFT JOIN UserIDEmployeeIDMapping M
  ON M.EmployeeID=A.EmployeeID
  WHERE O.EmployeeID IS NOT NULL
)
SELECT * FROM cteOrgChart;

推荐阅读