SQL 2005 - 公用表表达式 - 查找层次结构中的最后一个

Question

假设我有下表：

CREATE TABLE Employees
(
EmployeeId int PRIMARY KEY NOT NULL,
ParentEmployeId int REFERENCES Employees(EmployeeId) NULL,
Name varChar(255)
)

所有记录都有一个主标识符，并且记录能够将另一个记录识别为父记录。 （我的实际架构与员工无关，这只是用于说明的简化版本，因此如果您有更好的方法来处理员工信息，则与此对话无关。）

以下记录是插入：

INSERT INTO Employees VALUES (1, NULL, 'Company President 1')
INSERT INTO Employees VALUES (2, NULL, 'Company President 2')

INSERT INTO Employees VALUES (3, 1, 'Company President 1 - VP')
INSERT INTO Employees VALUES (4, 2, 'Company President 2 - VP')

INSERT INTO Employees VALUES (5, 3, 'Company President 1 - VP - Secretary')
INSERT INTO Employees VALUES (6, 4, 'Company President 2 - VP - Secretary')

INSERT INTO Employees VALUES (7, 5, 'Company President 1 - VP - Secretary - Sandwich Delivery')

这些插入代表：

Company President 1
    Company President 1 - VP
        Company President 1 - VP - Secretary
            Company President 1 - VP - Secretary - Sandwich Delivery
Company President 2
    Company President 2 - VP
        Company President 2 - VP - Secretary

我想要做的是对于所有具有 NULL ParentEmployeeId 的员工，我想找到链中的最后一个人，在本例中为“公司总裁 1 - 副总裁 - 秘书 - 三明治外卖”和“公司总裁 2 - 副总裁 - 秘书”。

我有以下 CTE，它为我提供了包括嵌套级别在内的所有内容，但我不确定从这里到哪里去。如果可能的话，我想避免使用光标。

另外，这非常重要，我在其他地方有逻辑保证一名员工只能有 1 名直接下属。因此，尽管架构在技术上允许，但公司总裁 1 永远不会列出两名副总裁。

WITH EmployeeRec(EmployeeId, ParentEmployeeId, Name, Level) AS
(
    SELECT
        EmployeeId,
        ParentEmployeId,
        Name,
        1 as [Level]
    FROM
        Employees
    WHERE
        ParentEmployeId IS NULL

    UNION ALL

    SELECT
        E.EmployeeId,
        E.ParentEmployeId,
        E.Name,
        R.[Level] + 1
    FROM
        Employees E
    INNER JOIN
        EmployeeRec R
    ON
        E.ParentEmployeId = R.EmployeeId
)
SELECT * FROM EmployeeRec

Answer 1

这里的关键是跟踪递归 CTE 中的顶级父级：

;WITH EmployeeRec(
   EmployeeId, ParentEmployeeId, UltimateGrandbossEmployeeId, Name, Level)
 AS
(
    SELECT
        EmployeeId,
        ParentEmployeeId,
        EmployeeId UltimateGrandbossEmployeeId,
        Name,
        1 as [Level]
    FROM
        Employees
    WHERE
        ParentEmployeeId IS NULL

    UNION ALL

    SELECT
        E.EmployeeId,
        E.ParentEmployeeId,
        R.UltimateGrandbossEmployeeId,
        E.Name,
        R.[Level] + 1
    FROM
        Employees E
    INNER JOIN
        EmployeeRec R
    ON
        E.ParentEmployeeId = R.EmployeeId
)

... 形成一个中间 CTE 来捕获“自下而上”的级别 ...

SELECT 
    UltimateGrandbossEmployeeId,
    Name,
    ROW_NUMBER() OVER (PARTITION BY UltimateGrandbossEmployeeId 
                       ORDER BY Level Desc ) BottomUp
 FROM EmployeeRec
)

... 对于每个最终的 grandboss ，选择它们最深的子级：（

SELECT
    UltimateGrandbossEmployeeId, DeepestChildName
FROM
    Inter
WHERE
    BottomUp = 1

将所有这些代码片段连接在一起，形成具有两个 CTE 和一个 SELECT 的单个查询）

结果：

1   Company President 1 - VP - Secretary - Sandwich Delivery
2   Company President 2 - VP - Secretary

您可以将其 JOIN 返回到 员工 获取最终大老板的姓名或跟踪 CTE 中的姓名，这在您的实际情况中是有意义的。

Answer 2

跟踪您的主 EmployeeID 允许您将结果与最后一个级别连接起来，以保留您需要的记录。

WITH EmployeeRec(Master, EmployeeId, ParentEmployeeId, Name, Level) AS
(
    SELECT
        [Master] = EmployeeId,
        EmployeeId,
        ParentEmployeId,
        Name,
        1 as [Level]
    FROM
        Employees
    WHERE
        ParentEmployeId IS NULL

    UNION ALL

    SELECT
        R.Master,
        E.EmployeeId,
        E.ParentEmployeId,
        E.Name,
        R.[Level] + 1
    FROM
        Employees E
    INNER JOIN
        EmployeeRec R
    ON
        E.ParentEmployeId = R.EmployeeId
)
SELECT  *
FROM    EmployeeRec er
        INNER JOIN (
          SELECT  Master, Level = MAX(Level)
          FROM    EmployeeRec
          GROUP BY Master
        ) m ON m.Master = er.Master
               AND m.Level = er.Level