Eine Hierarchie durchlaufen

Teil des Abschnitts Über die Grundlagen hinaus der SQL-Journey von Coddy — Lektion 21 von 27.

Der eigentliche Grund für die Existenz rekursiver CTEs: das Durchlaufen einer in einer einzelnen Tabelle gespeicherten Eltern-Kind-Beziehung. Angenommen, employees hat eine manager_id-Spalte, die auf dieselbe Tabelle zurückverweist:

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7              -- Anker: der startende Mitarbeiter
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id      -- rekursiv: Sprung zum Manager
)
SELECT * FROM chain

Jeder Schritt verbindet die CTE wieder mit employees, wobei eine Ebene im Baum nach oben gesprungen wird. Die Rekursion stoppt automatisch, wenn eine Zeile keinen Manager hat (JOIN liefert keine Treffer).

Aufgabe

Mittel

Verfügbare Tabellen und Spalten:
categories: id, name, parent_id

Jede Kategorie hat eine optionale parent_id, die auf eine andere Kategorie verweist. Geben Sie alle Kategorien zurück, die Nachkommen der Kategorie id = 1 sind (deren Kinder, Enkelkinder, …), aber nicht die Kategorie 1 selbst. Geben Sie id und name zurück, sortiert nach id.

Spickzettel

Verwenden Sie rekursive CTEs, um Eltern-Kind-Beziehungen zu durchlaufen, die in einer einzelnen Tabelle gespeichert sind (z. B. eine manager_id- oder parent_id-Spalte, die auf dieselbe Tabelle zurückverweist):

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7          -- Anker: Startzeile
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id  -- rekursiv: Sprung zum Vorgesetzten
)
SELECT * FROM chain

Die Rekursion stoppt automatisch, wenn der JOIN keine passenden Zeilen findet (z. B. eine Zeile ohne Vorgesetzten).

Probier es selbst

WITH RECURSIVE descendants AS (
    -- Anker: direkte Kinder von id 1
    -- rekursiv: deren Kinder, etc.
)
SELECT id, name FROM descendants ORDER BY id