代码之家 › 专栏 › 技术社区 › Chris Haas

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

common-table-expression tsql

Chris Haas · 技术社区 · 15 年前

假设我有下表:

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

我要做的是为所有有空值的员工 ParentEmployeeId 我想找到链中的最后一个人,在本例中是“ Company President 1 - VP - Secretary - Sandwich Delivery “和” Company President 2 - VP - Secretary ".

我有以下的CTE,它给了我一切,包括嵌套水平,但我不知道从这里去哪里。如果可能的话,我想避免使用光标。

还有,这是 非常重要 ,我在其他地方有逻辑,保证一个员工只能有一个直接下属。所以尽管模式在技术上允许它, Company President 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

2 回复 | 直到 15 年前

AakashM 15 年前

跟踪您的主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

AakashM 15 年前

关键是 跟踪递归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
)

... 对于每一个最终的祖父,选择他们最深的孩子:

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 这又回到了 Employee 在CTE中获取ultiamte grandboss的名字或跟踪名字,这在您的实际情况中是有意义的。