SQL 2005 - Gemeinsamer Tabellenausdruck - Suche nach dem letzten in der Hierarchie

Question

Nehmen wir an, ich habe die folgende Tabelle:

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

Alle Datensätze haben einen primären Bezeichner, und die Datensätze können einen anderen Datensatz als Elternteil identifizieren. (Mein tatsächliches Schema ist nicht über Mitarbeiter, dies ist nur eine vereinfachte Version zur Veranschaulichung, so dass, wenn Sie einen besseren Weg der Handhabung von Mitarbeiterinformationen seine nicht germane zu diesem Gespräch haben).

Die folgenden Datensätze werden eingefügt:

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')

Diese Einsätze repräsentieren:

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

Was ich versuche, ist für alle Mitarbeiter, die eine NULL haben ParentEmployeeId Ich möchte die letzte Person in der Kette finden, in diesem Beispiel wäre das " Company President 1 - VP - Secretary - Sandwich Delivery " und " Company President 2 - VP - Secretary ".

Ich habe die folgende CTE, die mir alles, einschließlich der Verschachtelung Ebene gibt, aber ich bin nicht sicher, wo von hier aus zu gehen. Ich würde gerne Cursor vermeiden, wenn möglich.

Außerdem, und das ist sehr wichtig Ich habe an anderer Stelle eine Logik, die garantiert, dass ein Mitarbeiter nur einen direkten Untergebenen haben kann. Obwohl das Schema dies also technisch zulässt, Company President 1 werden niemals zwei Vizepräsidenten aufgeführt sein.

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

Wenn Sie Ihre Master-EmployeeID im Auge behalten, können Sie die Ergebnisse mit der letzten Ebene verbinden, um die benötigten Datensätze zu erhalten.

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

Answer 2

Der Schlüssel dazu ist die oberste übergeordnete Ebene im rekursiven CTE zu verfolgen :

;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
)

... einen Zwischen-CTE bilden, um die "Bottom-up"-Ebene zu erfassen ...

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

... für jeden ultimativen Großboss sein tiefstes Kind auswählen:

SELECT
    UltimateGrandbossEmployeeId, DeepestChildName
FROM
    Inter
WHERE
    BottomUp = 1

(Verketten Sie alle diese Codefragmente zu einer einzigen Abfrage mit zwei CTEs und einer SELECT )

Ergebnisse:

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

Sie können JOIN dies zurück zu Employee um ultiamte Grandboss-Namen zu erhalten oder die Namen im CTE zu verfolgen, wie es in Ihrer aktuellen Situation sinnvoll ist.