Lernen Sie die rekursive PostgreSQL-Abfrage anhand eines Beispiels kennen

Zusammenfassung: In diesem Lernprogramm erfahren Sie mehr über die rekursive PostgreSQL-Abfrage mithilfe von rekursiven allgemeinen Tabellenausdrücken oder CTEs.

Einführung in die rekursive PostgreSQL-Abfrage

PostgreSQL bietet die WITH -Anweisung, mit der Sie Hilfsanweisungen für die Verwendung in einer Abfrage erstellen können.

Diese Anweisungen werden oft als Common table expressions oder CTEs bezeichnet. Die CTEs sind wie temporäre Tabellen, die nur während der Ausführung der Abfrage vorhanden sind.

Eine rekursive Abfrage ist eine Abfrage, die auf einen rekursiven CTE verweist. Die rekursiven Abfragen sind in vielen Situationen nützlich, z. B. beim Abfragen hierarchischer Daten wie Organisationsstruktur, Stückliste usw.

Im Folgenden wird die Syntax eines rekursiven CTE veranschaulicht:

Code language: SQL (Structured Query Language) (sql)

Ein rekursiver CTE hat drei Elemente:

• Nicht rekursiver Term: Der nicht rekursive Term ist eine CTE-Abfragedefinition, die die Basisergebnismenge der CTE-Struktur bildet.
• Rekursiver Term: der rekursive Term besteht aus einer oder mehreren CTE-Abfragedefinitionen, die mit dem nicht rekursiven Term mithilfe des UNION- oder UNION ALL -Operators verknüpft sind. Der rekursive Begriff verweist auf den CTE-Namen selbst.
• Terminierungsprüfung: Die Rekursion stoppt, wenn keine Zeilen aus der vorherigen Iteration zurückgegeben werden.

PostgreSQL führt einen rekursiven CTE in der folgenden Reihenfolge aus:

1. Führen Sie den nicht rekursiven Term aus, um die Basisergebnismenge (R0) zu erstellen.
2. Führen Sie den rekursiven Term mit Ri als Eingabe aus, um die Ergebnismenge Ri+1 als Ausgabe zurückzugeben.
3. Wiederholen Sie Schritt 2, bis ein leerer Satz zurückgegeben wird. (Terminierungsprüfung)
4. Geben Sie die endgültige Ergebnismenge zurück, die eine VEREINIGUNG oder UNION ALL der Ergebnismenge ist R0, R1, … Rn

PostgreSQL rekursive Abfrage Beispiel

Wir werden eine neue Tabelle erstellen, um die rekursive PostgreSQL-Abfrage zu demonstrieren.

Code language: SQL (Structured Query Language) (sql)

Die Tabelle employees hat drei Spalten: employee_id, manager_id und full_name. Die Spalte manager_id gibt die Manager-ID eines Mitarbeiters an.

Die folgende Anweisung fügt Beispieldaten in die Tabelle employees ein.

Code language: PHP (php)

Die folgende Abfrage gibt alle Untergebenen des Managers mit der ID 2 zurück.

Code language: SQL (Structured Query Language) (sql)

Wie es funktioniert:

• Der rekursive CTE, subordinates , definiert einen nicht-rekursiven Term und einen rekursiven Term.
• Der nicht rekursive Term gibt die Basisergebnismenge R0 zurück, die der Mitarbeiter mit der ID 2 ist.

Code language: SQL (Structured Query Language) (sql)

Der rekursive Term gibt die direkten Untergebenen der Mitarbeiter-ID 2 zurück. Dies ist das Ergebnis der Verbindung zwischen der Tabelle employees und dem CTE subordinates. Die erste Iteration des rekursiven Terms gibt die folgende Ergebnismenge zurück:

Code language: SQL (Structured Query Language) (sql)

PostgreSQL führt den rekursiven Term wiederholt aus. Die zweite Iteration des rekursiven Elements verwendet die Ergebnismenge über dem Schritt als Eingabewert und gibt diese Ergebnismenge zurück:

Code language: SQL (Structured Query Language) (sql)

Die dritte Iteration gibt eine leere Ergebnismenge zurück, da kein Mitarbeiter an den Mitarbeiter mit den ID 16, 17, 18, 19 und 20 berichtet.

PostgreSQL gibt die endgültige Ergebnismenge zurück, die die Vereinigung aller Ergebnismengen in der ersten und zweiten Iteration ist, die durch die nicht rekursiven und rekursiven Terme generiert werden.

In diesem Tutorial haben Sie gelernt, wie Sie die rekursiven CTEs zum Erstellen der rekursiven PostgreSQL-Abfragen verwenden.