SQL CTE将在层级中找到高级管理人员(从顶层到第二层)

Question

我有一个Employee IDs表和相应的Manager IDs，如下所示：

理想结果：高级经理栏，其中有直接向John (EmpID: 1)或Anna (EmpID: 2)报告的滚动经理。

汤姆向杰克汇报，杰克向提姆汇报，蒂姆向安娜汇报。

因此，杰克布莱克的高级经理是蒂姆史密斯，4。

汤姆->杰克-> Tim --> Anna

​

​

我曾经处理过一个CTE，但是查询会返回很多重复的行。否则，CTE输出正确的数据。

   WITH cte AS 
    (
        SELECT EmpID, FirstName, LastName, EmpID as SeniorManager
        FROM Employees
        WHERE ManagerID < 3 
            AND EmpActive = 1
        UNION ALL
        SELECT emp.EmpID, emp.FirstName, emp.LastName, c.SeniorManager 
        FROM Employees emp
        JOIN cte c ON c.EmpID = emp.ManagerID
        WHERE emp.EmpID <> emp.ManagerID
            AND emp.EmpActive = 1
        )

    SELECT * FROM cte

示例数据是实际数据的过度简化版本--它存在于公司数据库中。我的主要问题是:为什么CTE会返回重复的行？

下面是示例数据：

 CREATE TABLE Employees (
  EmpID INT NOT NULL PRIMARY KEY,
  FirstName VARCHAR(35) NOT NULL,
  LastName VARCHAR(35) NOT NULL,
  ManagerID INT NOT NULL);

 INSERT INTO Employees
  (EmpID, FirstName, LastName, ManagerID)
 VALUES
  (1, 'John', 'Smith', 2),
  (2, 'Anna', 'White', 1),
  (3, 'Jack', 'Black', 4),
  (4, 'Tim', 'Smith', 2),
  (5, 'Jason', 'Black', 3),
  (6, 'Tom', 'Black', 3);

Answer 1

您可以尝试添加一个level列来表示person层次结构。

那就只有level = 1人了。

WITH cte AS 
(
    SELECT EmpID,FirstName,LastName,ManagerID,1 level
    FROM Employees 
    UNION ALL
    SELECT t2.EmpID,t2.FirstName,t2.LastName,t2.ManagerID,level+ 1
    FROM cte t1 JOIN Employees t2
    on t1.EmpId = t2.ManagerID
    WHERE t1.ManagerID <> t2.EmpId
)
SELECT EmpID,FirstName,LastName  
FROM cte t1
where not exists (
    select 1 
    from cte tt
    WHERE tt.level = 2 and t1.EmpID = tt.EmpID
) and level = 1

木琴

结果

mpID    FirstName   LastName    
1       John        Smith       
2       Anna        White       

Answer 2

WITH recursive cte
AS (
SELECT emp.*, 0 AS LEVEL, managerid AS supervisor
FROM employees emp
WHERE empid IN (1, 2)

UNION ALL

SELECT emp.*, cte.LEVEL + 1, CASE 
        WHEN cte.LEVEL IN (0, 1)
            THEN emp.managerid
        ELSE cte.supervisor
        END
FROM employees emp, cte
WHERE cte.empid = emp.managerid
    AND emp.empid NOT IN (1, 2)
)
SELECT * FROM cte

Answer 3

经过大量的故障排除后，我已经找到了解决方案。希望这能帮助需要帮助的人:)

WITH cte AS 
(
    SELECT EmpID, FirstName, LastName, EmpID as SeniorManager
    FROM Employees
    WHERE ManagerID <= 2
        AND Active = 1
    UNION ALL
    SELECT e.EmpID, e.FirstName, e.LastName, c.SeniorManager 
    FROM tblEmployee e
    JOIN cte c ON c.EmpID = e.ManagerID
    WHERE e.EmpID <> e.ManagerID
        AND e.EmpID >= 3
        AND e.Active = 1
    )

SELECT EmpID, FirstName, LastName, MAX(SeniorManager) SeniorManager FROM cte
GROUP BY EmpID, FirstName, LastName

解释：

• 将e.EmpID >3添加到联接的第二部分。重复的行是由无限循环造成的，因为有两个人作为经理，所以我不得不排除它们。
• 为每个重复的EmpID选择最后一行。出于某种原因，cte对于每个EmpID有2条记录，第一条记录带有top-level manager，第二行记录有second-level manager。因此，我需要选择第二行。

编辑：

我的OP声明代码已经给出了我想要的输出，只是它会运行到无限循环中。这是我的主要问题，在将第一批雇员排除在外后得到解决。

我正在研究公司数据，这比示例数据要复杂得多，我不能在这里复制它，也不能修复数据。如果有人遇到类似的问题，我不知道是否能为您提供确切的解决方案，但希望我提供的代码足够开始。

为什么我回答了我自己的问题，但接受了别人的回答:我的答案对数据库非常具体。它已经解决了这一具体问题，但我不认为这是最好的办法。我接受的答案更好。