SQL 2005 — общее табличное выражение — найти последнее в иерархии

Допустим, у меня есть следующая таблица:

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, я хочу найти последнего человека в цепочке, который в этом примере будет «Company President 1 - VP - Secretary - Sandwich Delivery» и «Company President 2 - VP - Secretary».

У меня есть следующий CTE, который дает мне все, включая уровень вложенности, но я не уверен, куда идти дальше. Я хотел бы избежать курсоров, если это возможно.

Кроме того, и это очень важно, у меня есть логика в другом месте, которая гарантирует, что у сотрудника может быть только 1 прямой подчиненный. Таким образом, хотя схема технически это допускает, 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

tsql common-table-expression
person Chris Haas    schedule 17.11.2010    source источник
comment
Если кого-то когда-нибудь заинтересует, вот версия mySQL, специально предназначенная для MariaDB 10.2: cjhaas.com/2019/10/25/mysql-recursive-cte   -  person Chris Haas    schedule 25.10.2019

Ответы (2)


arrow_upward
6
arrow_downward

Отслеживание основного идентификатора сотрудника позволяет объединить результаты с последним уровнем, чтобы сохранить нужные записи.

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
person Lieven Keersmaekers    schedule 17.11.2010
comment
Исправлен ваш синтаксис и +1, потому что я думаю, что ваше самосоединение EmployeeRec более элегантно, чем мой промежуточный набор результатов :) - person AakashM; 17.11.2010
comment
@AakashM, спасибо, я пропустил это. В свою защиту я пишу это без доступа к экземпляру SQL Server. - person Lieven Keersmaekers; 17.11.2010
comment
... и я вернул должок :) - person Lieven Keersmaekers; 17.11.2010

arrow_upward
3
arrow_downward

Ключевым моментом здесь является отслеживание родительского элемента верхнего уровня в рекурсивном 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, чтобы получить имена ultiamte grandboss или отследить имена в CTE, как это имеет смысл в вашей реальной ситуации.

person AakashM    schedule 17.11.2010