Level 20: Rekursive Abfragen: WITH RECURSIVE

Lektion

Eine rekursive CTE ist eine CTE, die sich selbst liest. Sie dient zwei Dingen, die gewöhnliche Abfragen nicht können: eine Wertereihe erzeugen, die in keiner Tabelle existiert, und eine Hierarchie durchlaufen, deren Tiefe man nicht kennt.

Sie hat zwei Teile, verbunden durch UNION ALL. Der Startfall ist ein gewöhnliches SELECT, das die ersten Zeilen erzeugt. Der rekursive Schritt ist ein SELECT, das die CTE selbst liest und aus den in der vorherigen Runde erzeugten Zeilen neue Zeilen bildet.

SQL führt die Berechnung Runde für Runde aus. Im Beispiel erzeugt der Start x = 1. In Runde 1 liest der Schritt 1 und erzeugt 2; in Runde 2 liest er 2 und erzeugt 3, und so weiter bis 5. In der nächsten Runde ist die Bedingung x < 5 für 5 falsch: Der Schritt erzeugt nichts mehr, und die Berechnung endet. Das Ergebnis vereint alle erzeugten Zeilen: 1, 2, 3, 4, 5. Das WHERE des rekursiven Schritts ist also die Abbruchbedingung.

Anwendung 1: Reihen. Mit date(day, '+1 day') oder strftime('%Y-%m', month || '-01', '+1 month') aus Level 6 erzeugt man alle Tage oder alle Monate eines Zeitraums. Ein LEFT JOIN hängt dann die Daten an: Monate ohne Aktivität erscheinen mit 0.

Anwendung 2: Hierarchien. Der Start wählt die Wurzel aus, zum Beispiel die Angestellte ohne Führungskraft (Alice, Tiefe 0). Jede Runde verbindet staff mit der CTE, um die Untergebenen der in der vorherigen Runde gefundenen Personen zu finden: Bob, Claire und David in Runde 1, ihre Teams in Runde 2. Die Berechnung endet, wenn niemand mehr Untergebene hat.

Enthalten die Daten einen Zyklus (A ist Führungskraft von B, B von A), läuft die Abfrage endlos. Es gibt zwei Schutzmaßnahmen. Die sicherste: eine Tiefenbegrenzung im rekursiven Schritt, zum Beispiel WHERE depth < 20. Die andere: Start und Schritt mit UNION statt UNION ALL verbinden; eine bereits erzeugte Zeile wird dann nicht erneut erzeugt, und die Schleife endet, wenn der Zyklus auf eine identische Zeile zurückkommt. Achtung: Enthält die Zeile einen Zähler wie depth, ist sie immer neu, und UNION reicht nicht mehr.

Wie jede Abfrage garantiert eine rekursive CTE keine Zeilenreihenfolge: Füge in der abschließenden Abfrage ein ORDER BY hinzu, sobald die Reihenfolge wichtig ist.

Syntax

WITH RECURSIVE t(x) AS (
  SELECT start
  UNION ALL
  SELECT x + 1
  FROM t
  WHERE x < ziel
)
SELECT x
FROM t;

Kommentiertes Beispiel

WITH RECURSIVE n(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1
  FROM n
  WHERE x < 5
)
SELECT x
FROM n;

Start: 1. Jede Runde fügt x + 1 hinzu, solange x < 5. Ergebnis: 1, 2, 3, 4, 5.

Ergebnis des Beispiels

Das Wichtigste

• Start UNION ALL rekursiver Schritt.
• Der rekursive Schritt liest die CTE selbst.
• Das WHERE des rekursiven Schritts beendet die Schleife.

Häufige Fallen

• Die Abbruchbedingung vergessen: Die Abfrage läuft endlos.
• Startfall und rekursiver Schritt müssen gleich viele Spalten haben.

Die 5 Übungen des Levels

1. Geführt · Zeige die Zahlen von 1 bis 10 in einer Spalte x. (Tabelle: )
2. Training · Zeige alle Tage vom 1. bis 7. Juli 2025 in einer Spalte day. (Tabelle: )
3. Training · Zeige den Namen aller Personen, die direkt oder indirekt Bob (id 2) unterstellt sind. (Tabelle: staff)
4. Training · Zeige jede Person mit ihrer Tiefe im Organigramm (depth): 0 für die Person ohne Führungskraft, 1 für ihre direkt Unterstellten und so weiter. (Tabelle: staff)
5. Herausforderung · Zeige die Zahl der Ausleihen in jedem Monat von Januar bis Juni 2025 (Format JJJJ-MM), auch für Monate ohne Ausleihe, in zeitlicher Reihenfolge. (Tabelle: loans)

In SpeedQL wird jede Abfrage sofort geprüft: Ihr Ergebnis wird mit dem erwarteten verglichen, dann läuft die Abfrage noch einmal auf einer versteckten Kontrolldatenbank. Zu jeder Übung gibt es schriftliche Hinweise, die du nur bei Bedarf aufrufst.