在特殊情况下使用CTE

Question

我在SQL Server 2005中有雇员经理层次结构的下表。

以下是我想要达到的目标

​

​

布辛斯逻辑：

-有效的经理应有一个部门

-有效经理是层级中最近的有部门的经理

-有效部门是有效经理的部门

我已经为类似的递归场景推荐了CTE。示例SQL服务器-递归CTE的简单示例，但是我的场景有更多的条件，而不是简单地找到最近的管理器。

如何修改这个CTE以给出预期的结果？

查询

DECLARE @Emploee TABLE (EmpID INT, EmpName VARCHAR(50), Dept VARCHAR(5), Manager INT)

INSERT INTO @Emploee VALUES (1, 'A', NULL,NULL)
INSERT INTO @Emploee VALUES (2, 'B', 'D100',NULL)
INSERT INTO @Emploee VALUES (3, 'C', 'D101',2)
INSERT INTO @Emploee VALUES (4, 'D', 'D102',1)
INSERT INTO @Emploee VALUES (5, 'E', NULL,2)
INSERT INTO @Emploee VALUES (6, 'F', 'D103',5)
INSERT INTO @Emploee VALUES (7, 'G', NULL,6)
INSERT INTO @Emploee VALUES (8, 'H', 'D104',7)

SELECT * 
FROM @Emploee
WHERE Manager IS NOT NULL

Answer 1

可以将adrianm答案的CTEs从3减少到2，将所有有效经理的数据合并在一起。

WITH EmployeeManagers AS (
  SELECT e.EmpId, e.EmpName, 1 AS Level
       , e.Manager, e.EmpID CurrLevelEmpID, Cast(Null as VarChar(5)) Dept
  FROM   Employee AS e
  WHERE  e.Manager IS NOT NULL
  UNION ALL
  SELECT em.EmpId, em.EmpName, Level + 1 AS Level
       , e.Manager, e.EmpID CurrLevelEmpID, e.Dept Dept
  FROM   EmployeeManagers em
         INNER JOIN Employee e ON e.EmpId = em.Manager
), EffectiveManagers AS (
  SELECT EmpID
       , CurrLevelEmpID Manager
       , Dept
       , Effective = ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Level)
  FROM   EmployeeManagers e
  WHERE  DEPT is not null
)
SELECT e.EmpID
     , e.EmpName
     , e.Manager
     , eem.Manager EffectiveManager
     , eem.Dept EffectiveDepartment
FROM   Employee e
       LEFT JOIN EffectiveManagers eem ON e.EmpID = eem.EmpID 
             AND eem.Effective = 1
WHERE  e.Manager IS NOT NULL

EmployeeManagers将获得更多的数据，特别是CurrLevelEmpID是当前级别的员工的EmpID，更改e.Manager的定义将阻碍经常性的e.Manager，如果有当前级别的话，Dept是当前级别的员工的部门。

EffectiveManagers CTE获取每个员工的有效管理器行，Effective计算员工行的有效经理级别。

主查询JOIN基表，EffectiveManagers只获取每个层次结构的第一个层次结构(一个有效= 1)

SQLFiddle演示

Answer 2

WITH EmployeeManagers AS (
    SELECT Employee.EmpId
          ,1 AS Level
          ,Employee.Manager
    FROM @Employee AS Employee
    WHERE Employee.Manager IS NOT NULL
  UNION ALL
    SELECT EmployeeManagers.EmpId
          ,Level + 1 AS Level
          ,Manager.Manager
    FROM EmployeeManagers
         INNER JOIN @Employee AS Manager
             ON Manager.EmpId = EmployeeManagers.Manager
)
,EmployeeManagerDepartment AS (
    SELECT EmployeeManagers.EmpId
          ,EmployeeManagers.Level
          ,EmployeeManagers.Manager
          ,Manager.Dept
   FROM EmployeeManagers
        LEFT JOIN @Employee AS Manager
            ON Manager.EmpID = EmployeeManagers.Manager
)
,EffectiveManagerLevel AS (
    SELECT EmpId
          ,MIN(Level) EffectiveLevel
    FROM EmployeeManagerDepartment
    WHERE Dept IS NOT NULL
    GROUP BY EmpId
)
SELECT Employee.EmpID AS [Emp ID]
      ,Employee.EmpName AS [EmpName]
      ,Employee.Manager AS [Direct Manager]
      ,EmployeeManagerDepartment.Manager AS [EffectiveManager]
      ,EmployeeManagerDepartment.Dept AS [Effective Department]
FROM @Employee AS Employee
     LEFT JOIN EffectiveManagerLevel
         ON EffectiveManagerLevel.EmpId = Employee.EmpId
     LEFT JOIN EmployeeManagerDepartment
         ON EmployeeManagerDepartment.EmpId = Employee.EmpId
            AND EmployeeManagerDepartment.Level = EffectiveManagerLevel.EffectiveLevel
WHERE Employee.Manager IS NOT NULL