Arbeiten mit CTEs (allgemeinen Tabellenausdrücken)

Siehe auch:

CONNECT BY, WITH

Unter diesem Thema:

Was ist ein CTE?

Ein CTE (allgemeiner Tabellenausdruck) ist eine benannte Unterabfrage, die in einer WITH-Klausel definiert wird. Sie können sich den CTE als temporäre Ansicht vorstellen, die in der Anweisung verwendet wird, die den CTE definiert. Der CTE definiert den Namen der temporären Ansicht, eine optionale Liste mit Spaltennamen und einen Abfrageausdruck (d. h. eine SELECT-Anweisung). Das Ergebnis des Abfrageausdrucks ist im Grunde eine Tabelle. Jede Spalte dieser Tabelle entspricht einer Spalte in der (optionalen) Liste der Spaltennamen.

Hier ist ein Beispiel für eine Abfrage, die einen CTE verwendet:

WITH
    my_cte (cte_col_1, cte_col_2) AS (
        SELECT col_1, col_2
            FROM ...
    )
SELECT ... FROM my_cte;
Copy

Im obigen Beispiel beginnt der CTE in der Zeile, die my_cte (cte_col_1, cte_col_2) AS ( enthält, und endet in der Zeile, die ) enthält.

Vermeiden Sie CTE-Namen, die mit folgenden Namen übereinstimmen:

  • SQL-Funktionsnamen

  • Tabellen, Ansichten oder materialisierte Ansichten. Wenn eine Abfrage einen CTE mit einem bestimmten Namen definiert, hat der CTE Vorrang vor Tabellen usw.

Ein CTE kann rekursiv oder nicht rekursiv sein. Ein rekursiver CTE ist ein CTE, der auf sich selbst verweist. Ein rekursiver CTE kann eine Tabelle so oft wie nötig mit sich selbst verknüpfen, um hierarchische Daten in der Tabelle zu verarbeiten.

CTEs erhöhen die Modularität und vereinfachen die Wartung.

Rekursive CTEs und hierarchische Daten

Mit CTEs können Sie hierarchische Daten verarbeiten, z. B. eine Stückliste (Komponente, Unterkomponenten) oder eine Verwaltungshierarchie (Vorgesetzte, Mitarbeiter). Weitere Informationen zu hierarchischen Daten und anderen Möglichkeiten zur Abfrage hierarchischer Daten finden Sie unter Abfragen hierarchischer Daten.

Mit einem rekursiven CTE können Sie alle Ebenen einer Hierarchie verknüpfen, ohne vorher zu wissen, wie viele Ebenen es gibt.

Übersicht zu rekursiver CTE-Syntax

Dieser Abschnitt bietet nur einen Überblick über die Syntax und darüber, wie sich die Syntax auf die Funktionsweise der Rekursion bezieht.

WITH [ RECURSIVE ] <cte_name> AS
(
  <anchor_clause> UNION ALL <recursive_clause>
)
SELECT ... FROM ...;
Copy
Wobei:
anchor_clause

wählt die Anfangszeile oder eine Menge von Zeilen aus, die die Spitze der Hierarchie darstellen. Wenn Sie beispielsweise versuchen, alle Mitarbeiter eines Unternehmens anzuzeigen, wird in der Ankerklausel der Leiter des Unternehmens ausgewählt.

Die Ankerklausel ist eine SELECT-Anweisung und kann ein beliebiges SQL-Konstrukt enthalten. Die Ankerklausel kann nicht auf cte_name verweisen.

recursive_clause

wählt die nächste Ebene der Hierarchie basierend auf der vorherigen Ebene aus. In der ersten Iteration ist die vorherige Ebene das Resultset aus der Ankerklausel. In nachfolgenden Iterationen ist die vorherige Ebene die zuletzt abgeschlossene Iteration.

Die Klausel recursive_clause ist zwar eine SELECT-Anweisung, die jedoch auf Projektionen, Verknüpfungen und Filter beschränkt ist. Darüber hinaus ist Folgendes in der Anweisung nicht zulässig:

  • Aggregat- oder Fensterfunktionen.

  • GROUP BY, ORDER BY, LIMIT oder DISTINCT

Die rekursive Klausel kann wie eine reguläre Tabelle oder Ansicht auf cte_name verweisen.

Eine ausführlichere Beschreibung der Syntax finden Sie unter WITH.

Logisch wird der rekursive CTE wie folgt ausgewertet:

  1. Die Ankerklausel anchor_clause wird ausgewertet, und das Ergebnis wird sowohl in das endgültige Resultset als auch in eine Arbeitstabelle geschrieben. Der CTE-Name cte_name ist im Grunde ein Alias für diese Arbeitstabelle. Mit anderen Worten: Eine Abfrage, die auf cte_name verweist, liest aus dieser Arbeitstabelle.

  2. Wenn die Arbeitstabelle nicht leer ist:

    1. Die rekursive Klausel recursive_clause wird unter Verwendung des aktuellen Inhalts der Arbeitstabelle ausgewertet, wo immer auf cte_name verwiesen wird.

    2. Die rekursive Klausel recursive_clause wird ausgewertet, und das Ergebnis wird sowohl in das endgültige Resultset als auch in eine temporäre Tabelle geschrieben.

    3. Die Arbeitstabelle wird durch den Inhalt der temporären Tabelle überschrieben.

Im Endeffekt wird die Ausgabe der vorherigen Iteration in einer Arbeitstabelle mit dem Namen cte_name gespeichert, und diese Tabelle dient dann als eine der Eingaben für die nächste Iteration. Die Arbeitstabelle enthält nur das Ergebnis der letzten Iteration. Die akkumulierten Ergebnisse aller bisherigen Iterationen werden an einer anderen Stelle gespeichert.

Nach der letzten Iteration stehen die akkumulierten Ergebnisse der Haupt-SELECT-Anweisung zur Verfügung, indem auf cte_name verwiesen wird.

Hinweise zu rekursiven CTEs

Potenzial für Endlosschleifen

Das fehlerhafte Konstruieren eines rekursiven CTE kann eine Endlosschleife verursachen. In diesen Fällen wird die Abfrage so lange ausgeführt, bis eine der folgenden Situationen eintritt: die Abfrage war erfolgreich, die Abfrage weist eine Zeitüberschreitung auf (z. B. wird die durch den Parameter STATEMENT_TIMEOUT_IN_SECONDS angegebene Anzahl von Sekunden überschritten), oder der Benutzer führt einen Abbruch der Abfrage aus.

Weitere Informationen zum Entstehen einer Endlosschleife und zu Richtlinien, mit deren Hilfe dieses Problem verhindert wird, finden Sie unter Problembehandlung bei rekursiven CTEs.

Nicht zusammenhängende Hierarchien

Unter diesem Thema wurden Hierarchien und die Frage beschrieben, wie rekursive CTEs Beziehungen zwischen übergeordneten/untergeordneten Elementen nutzen können. In allen Beispielen dieses Themas sind die Hierarchien zusammenhängend.

Informationen zu nicht zusammenhängenden Hierarchien finden Sie unter Abfragen hierarchischer Daten.

Beispiele

Dieser Abschnitt enthält sowohl nicht rekursive als auch rekursive Beispiele für CTEs, um die beiden Typen gegenüberzustellen.

Nicht rekursiver, zweistufiger, selbstverknüpfender CTE

In diesem Beispiel wird eine Tabelle mit Mitarbeitern und Vorgesetzten verwendet:

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
Copy
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);
Copy

Eine zweistufige Selbstverknüpfung dieser Mitarbeitertabelle sieht folgendermaßen aus:

SELECT
     emps.title,
     emps.employee_ID,
     mgrs.employee_ID AS MANAGER_ID, 
     mgrs.title AS "MANAGER TITLE"
  FROM employees AS emps LEFT OUTER JOIN employees AS mgrs
    ON emps.manager_ID = mgrs.employee_ID
  ORDER BY mgrs.employee_ID NULLS FIRST, emps.employee_ID;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE              |
|----------------------------+-------------+------------+----------------------------|
| President                  |           1 |       NULL | NULL                       |
| Vice President Engineering |          10 |          1 | President                  |
| Vice President HR          |          20 |          1 | President                  |
| Programmer                 |         100 |         10 | Vice President Engineering |
| QA Engineer                |         101 |         10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | Vice President HR          |
+----------------------------+-------------+------------+----------------------------+
Copy

Die Abfrage oben zeigt alle Mitarbeiter. Die Mitarbeiter der einzelnen Vorgesetzten werden im Bericht neben dem Vorgesetzten angezeigt. Der Bericht zeigt die Hierarchie jedoch nicht visuell an. Ohne die Daten sorgfältig zu prüfen, wissen Sie nicht, wie viele Ebenen es in dem Unternehmen gibt. Sie müssen jede Zeile lesen, um festzustellen, welche Mitarbeiter einem bestimmten Vorgesetzten zugeordnet sind.

Ein rekursiver CTE kann diese hierarchischen Daten als seitlich liegende Baumstruktur anzeigen, wie im nächsten Abschnitt gezeigt.

Rekursiver CTE mit eingerückter Ausgabe

Im Folgenden finden Sie zwei Beispiele für die Verwendung eines rekursiven CTE:

  • Im ersten Beispiel werden die verschiedenen Ebenen der Hierarchie durch Einrücken dargestellt. Um dieses Beispiel zu vereinfachen, erstellt der Code die Zeilen nicht in einer bestimmten Reihenfolge.

  • Im zweiten Beispiel wird die Einrückung verwendet, und die Mitarbeiter werden unmittelbar unter ihrem jeweiligen Vorgesetzten angezeigt.

Ungeordnete Ausgabe

Hier ist das erste Beispiel.

 1)    WITH RECURSIVE managers
 2)          (indent, employee_ID, manager_ID, employee_title)
 3)        AS
 4)          (
 5)
 6)            SELECT '' AS indent, employee_ID, manager_ID, title AS employee_title
 7)              FROM employees
 8)              WHERE title = 'President'
 9)
10)            UNION ALL
11)
12)           SELECT indent || '--- ',
13)               employees.employee_ID, employees.manager_ID, employees.title
14)             FROM employees JOIN managers
15)               ON employees.manager_ID = managers.employee_ID
16)         )
17)
18)     SELECT indent || employee_title AS Title, employee_ID, manager_ID
19)       FROM managers
20)     ;
Copy

Die Abfrage umfasst die folgenden Abschnitte:

  • ZeileContains2 enthält die Spaltennamen für die „Ansicht“ (CTE).

  • Die Zeilen 4–16 enthalten den CTE.

  • Die Zeilen 6–8 enthalten die Ankerklausel des CTE.

  • Die Zeilen 12–15 enthalten die Rekursionsklausel des CTE.

  • Die Zeilen 18–19 enthalten den Haupt-SELECT-Anweisung, in der der CTE als Ansicht verwendet wird. Diese SELECT-Anweisung referenziert:

    • Name des CTE (managers), definiert in Zeile 1

    • Spalten des CTE (indent, employee_id usw.), definiert in Zeile 2

Der CTE enthält zwei SELECT-Anweisungen.

  • Die SELECT-Anweisung in der Ankerklausel wird einmal ausgeführt und liefert alle Zeilen der ersten (obersten) Ebene der Hierarchie.

  • Die SELECT-Anweisung in der Rekursionsklausel kann auf den CTE verweisen. Sie können sich die Abfrage als iterierend vorstellen, wobei jede Iteration auf den Abfrageergebnissen der vorherigen Iteration aufbaut.

Im Beispiel Vorgesetzte/Mitarbeiter gibt die Ankerklausel die erste Zeile aus, die den Vorstandsvorsitzenden des Unternehmens beschreibt.

In der nächsten Iteration der rekursiven Klausel findet die rekursive Klausel alle Zeilen, deren Vorgesetzter der Vorstandsvorsitzende ist (d. h. sie findet alle stellvertretenden Vorstandsvorsitzenden). Die dritte Iteration findet alle Mitarbeiter, deren Vorgesetzter einer der stellvertretenden Vorstandsvorsitzenden ist. Die Iteration wird fortgesetzt, bis es eine Iteration gibt, in der alle abgerufenen Zeilen Zeilen von Mitarbeitern auf Blattebene sind, die für niemanden als Vorgesetzter fungieren. Die Anweisung führt eine weitere Iteration durch und sucht (findet aber keine) Mitarbeiter, deren Vorgesetzte Mitarbeiter auf Blattebene sind. Diese Iteration erzeugt 0 Zeilen, und die Iteration wird angehalten.

Im Laufe dieser Iterationen akkumuliert die UNION ALL-Klausel die Ergebnisse. Die Ergebnisse jeder Iteration werden zu den Ergebnissen der vorherigen Iterationen hinzugefügt. Nach Abschluss der letzten Iteration werden die akkumulierten Zeilen (wie alle in einer WITH-Klausel erzeugten Zeilen) der Haupt-SELECT-Klausel der Abfrage zur Verfügung gestellt. Diese Haupt-SELECT-Klausel kann dann die Zeilen abfragen.

Diese Beispielabfrage verwendet Einrückungen, um die hierarchische Struktur der Daten zu verdeutlichen. Wenn Sie sich die Ausgabe ansehen, werden Sie feststellen, dass die Daten eines Mitarbeiters umso weiter eingerückt sind, je niedriger seine Ebene ist.

Die Einrückung wird durch die Spalte indent gesteuert. Die Einrückung beginnt mit 0 Zeichen (eine leere Zeichenfolge in der Ankerklausel) und wird für jede Iteration, d. h. für jede Ebene in der Hierarchie, um 4 Zeichen (---) erhöht.

Es ist also sehr wichtig, dass die Verknüpfungen korrekt sind und die richtigen Spalten in der rekursiven Klausel ausgewählt sind. Die Spalten in der SELECT-Anweisung der rekursiven Klausel müssen den Spalten in der Ankerklausel korrekt entsprechen. Denken Sie daran, dass die Abfrage mit dem Vorstandsvorsitzenden beginnt, dann die stellvertretenden Vorstandsvorsitzenden und anschließend die Personen auswählt, die direkt den stellvertretenden Vorstandsvorsitzenden unterstellt sind usw. Bei jeder Iteration werden die Mitarbeiter ausgewählt, deren Feld manager_id einem der managers.employee_id-Werte der Vorgesetzten entspricht, die in der vorherigen Iteration erstellt wurden.

Anders ausgedrückt ist ID in der Vorgesetztenansicht die Vorgesetzten-ID für die nächste Ebene von Mitarbeitern. Die Mitarbeiter-IDs müssen bei jeder Iteration in der Hierarchie abwärts voranschreiten (Vorstandsvorsitzender, stellvertretender Vorgesetzter, oberes Management, mittleres Management usw.). Wenn die Mitarbeiter-IDs nicht fortgesetzt werden, kann die Abfrage in einer Endlosschleife enden (wenn dieselbe manager_ID in verschiedenen Iterationen immer wieder in der managers.employee_ID-Spalte des Vorgesetzten angezeigt wird) oder eine Ebene überspringen bzw. auf andere Weise fehlschlagen.

Geordnete Ausgabe

Das vorherige Beispiel enthielt keine ORDER BY-Klausel. Obwohl die Datensätze der einzelnen Mitarbeiter ordnungsgemäß eingerückt sind, wurde nicht unbedingt jeder Mitarbeiter direkt unter seinem Vorgesetzten angezeigt. Im folgenden Beispiel wird eine Ausgabe mit korrekter Einrückung generiert, sodass sich die Mitarbeiter unter ihren jeweiligen Vorgesetzten befinden.

Die ORDER BY-Klausel der Abfrage nutzt eine zusätzliche Spalte mit dem Namen sort_key. Bei Iteration der rekursiven Klausel wird der Sortierschlüssel akkumuliert. Sie können sich den Sortierschlüssel als eine Zeichenfolge vorstellen, die die gesamte Weisungskette der aus Ihrer Sicht höher gestellten Mitarbeiter enthält (Ihren Vorgesetzten, den Vorgesetzten Ihres Vorgesetzten usw.). Die höchste Person in dieser Weisungskette (der Vorstandsvorsitzende) steht am Anfang der Sortierschlüsselzeichenfolge. Obwohl Sie den Sortierschlüssel normalerweise nicht anzeigen würden, wird zum besseren Verständnis in der folgenden Abfrage der Sortierschlüssel zur Ausgabe hinzugefügt.

Jede Iteration sollte die Länge des Sortierschlüssels um denselben Betrag (dieselbe Anzahl von Zeichen) erhöhen. Die Abfrage verwendet also eine UDF (benutzerdefinierte Funktion) mit dem Namen skey und die folgende Definition, um Segmente des Sortierschlüssels mit konsistenter Länge zu generieren:

CREATE OR REPLACE FUNCTION skey(ID VARCHAR)
  RETURNS VARCHAR
  AS
  $$
    SUBSTRING('0000' || ID::VARCHAR, -4) || ' '
  $$
  ;
Copy

Hier ist ein Beispiel für die Ausgabe der Funktion SKEY:

SELECT skey(12);
+----------+
| SKEY(12) |
|----------|
| 0012     |
+----------+
Copy

Hier ist die endgültige Version der Abfrage. Damit werden die Mitarbeiter direkt unter ihren jeweiligen Vorgesetzten gesetzt und basierend auf der „Ebene“ des Mitarbeiters eingerückt:

WITH RECURSIVE managers 
      -- Column list of the "view"
      (indent, employee_ID, manager_ID, employee_title, sort_key) 
    AS 
      -- Common Table Expression
      (
        -- Anchor Clause
        SELECT '' AS indent, 
            employee_ID, manager_ID, title AS employee_title, skey(employee_ID)
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT indent || '--- ',
            employees.employee_ID, employees.manager_ID, employees.title, 
            sort_key || skey(employees.employee_ID)
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT 
         indent || employee_title AS Title, employee_ID, 
         manager_ID, 
         sort_key
    FROM managers
    ORDER BY sort_key
  ;
+----------------------------------+-------------+------------+-----------------+
| TITLE                            | EMPLOYEE_ID | MANAGER_ID | SORT_KEY        |
|----------------------------------+-------------+------------+-----------------|
| President                        |           1 |       NULL | 0001            |
| --- Vice President Engineering   |          10 |          1 | 0001 0010       |
| --- --- Programmer               |         100 |         10 | 0001 0010 0100  |
| --- --- QA Engineer              |         101 |         10 | 0001 0010 0101  |
| --- Vice President HR            |          20 |          1 | 0001 0020       |
| --- --- Health Insurance Analyst |         200 |         20 | 0001 0020 0200  |
+----------------------------------+-------------+------------+-----------------+
Copy

Die nächste Abfrage zeigt, wie auf ein Feld der vorherigen (höheren) Ebene in der Hierarchie verwiesen wird. Achten Sie besonders auf die Spalte mgr_title:

WITH RECURSIVE managers 
      -- Column names for the "view"/CTE
      (employee_ID, manager_ID, employee_title, mgr_title) 
    AS
      -- Common Table Expression
      (

        -- Anchor Clause
        SELECT employee_ID, manager_ID, title AS employee_title, NULL AS mgr_title
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT 
            employees.employee_ID, employees.manager_ID, employees.title, managers.employee_title AS mgr_title
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT employee_title AS Title, employee_ID, manager_ID, mgr_title
    FROM managers
    ORDER BY manager_id NULLS FIRST, employee_ID
  ;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_TITLE                  |
|----------------------------+-------------+------------+----------------------------|
| President                  |           1 |       NULL | NULL                       |
| Vice President Engineering |          10 |          1 | President                  |
| Vice President HR          |          20 |          1 | President                  |
| Programmer                 |         100 |         10 | Vice President Engineering |
| QA Engineer                |         101 |         10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | Vice President HR          |
+----------------------------+-------------+------------+----------------------------+
Copy

Stückliste

Vorgesetzte-/Mitarbeiterhierarchien sind nicht die einzigen Hierarchien mit variabler Tiefe, die Sie in einer einzelnen Tabelle speichern und mit einem rekursiven CTE verarbeiten können. Ein weiteres gängiges Beispiel für hierarchische Daten ist eine „Stückliste“, bei der jede Komponente mit ihren Unterkomponenten aufgelistet wird, von denen jede wiederum mit ihren Unterkomponenten aufgelistet werden kann.

Angenommen, Ihre Tabelle enthält hierarchische Daten, z. B. die Komponenten eines Autos. Ihr Auto enthält wahrscheinlich Komponenten wie einen Motor, Räder usw. Viele dieser Komponenten enthalten Unterkomponenten (z. B. kann ein Motor eine Kraftstoffpumpe enthalten). Die Kraftstoffpumpe wiederum kann einen Motor, einen Schlauch usw. umfassen. Mit einem rekursiven CTE können Sie alle diese Komponenten und ihre Unterkomponenten auflisten.

Ein Beispiel für eine Abfrage, die eine Stückliste erzeugt, finden Sie unter WITH.

Problembehandlung bei rekursiven CTEs

Rekursive CTE-Abfrage wird ausgeführt, bis sie erfolgreich oder das Zeitlimit überschritten ist

Dieses Problem kann durch zwei verschiedene Szenarien verursacht werden:

  • Ihre Datenhierarchie weist einen Zyklus auf.

  • Sie haben eine Endlosschleife erstellt.

Ursache 1: Zyklische Datenhierarchie

Wenn Ihre Datenhierarchie einen Zyklus enthält (d. h. sie ist kein echter Strukturbaum), gibt es drei mögliche Lösungen:

Lösung 1.1:

Wenn die Daten eigentlich keinen Zyklus enthalten sollen, dann korrigieren Sie die Daten.

Lösung 1.2:

Begrenzen Sie die Abfrage auf irgendeine Weise (z. B. Begrenzen der Anzahl an Ausgabezeilen). Beispiel:

WITH RECURSIVE t(n) AS
    (
    SELECT 1
    UNION ALL
    SELECT N + 1 FROM t
   )
 SELECT n FROM t LIMIT 10;
Copy
Lösung 1.3:

Verwenden Sie keine Abfrage, die einen rekursiven CTE enthält, der hierarchische Daten erwartet.

Ursache 2: Endlosschleife

Eine Endlosschleife kann auftreten, wenn die Projektionsklausel in recursive_clause einen Wert aus dem übergeordneten Element (der vorherigen Iteration) ausgibt und nicht aus dem untergeordneten Element (der aktuellen Iteration). Die nächste Iteration verwendet dann in der Join-Verknüpfung diesen Wert und nicht den Wert aus der aktuellen Iteration.

Der folgende Pseudocode zeigt ein ungefähres Beispiel dafür:

CREATE TABLE employees (employee_ID INT, manager_ID INT, ...);
INSERT INTO employees (employee_ID, manager_ID) VALUES
        (1, NULL),
        (2, 1);

WITH cte_name (employee_ID, manager_ID, ...) AS
  (
     -- Anchor Clause
     SELECT employee_ID, manager_ID FROM table1
     UNION ALL
     SELECT manager_ID, employee_ID   -- <<< WRONG
         FROM table1 JOIN cte_name
           ON table1.manager_ID = cte_name.employee_ID
  )
SELECT ...
Copy

In diesem Beispiel übergibt die rekursive Klausel ihren übergeordneten Wert (die manager_id) in der Spalte, die den aktuellen/untergeordneten Wert (die employee_id) enthalten sollte. Das übergeordnete Element wird in der nächsten Iteration als „aktueller“ Wert angezeigt und erneut als „aktueller“ Wert an die folgende Generation übergeben, sodass die Abfrage nie die Ebenen nach unten durchläuft, sondern jedes Mal die gleiche Ebene verarbeitet.

Schritt 1::

Angenommen, die Ankerklausel wählt die Werte employee_id = 1 und manager_id = NULL aus.

CTE:

employee_ID  manager_ID
-----------  ---------
      1         NULL
Copy
Schritt 2::

Bei der ersten Iteration der rekursiven Klausel ist employee_id = 2 und manager_id = 1 in table1.

CTE:

employee_ID  manager_ID
-----------  ----------
       1         NULL
Copy

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...
Copy

Ergebnis der Verknüpfung (JOIN) in der rekursiven Klausel:

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                NULL
 ...
Copy

Projektion:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...
Copy

Da jedoch die Spalten employee_id und manager_id in der Projektion vertauscht sind, lautet die tatsächliche Ausgabe der Abfrage (und damit der Inhalt des CTE zu Beginn der nächsten Iteration):

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...
Copy
Schritt 3::

Bei der zweiten Iteration der rekursiven Klausel:

CTE:

employee_ID  manager_ID
-----------  ----------
       1         2
Copy

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...
Copy

Ergebnis der Verknüpfung (JOIN) in rekursiver Klausel:

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                2
 ...
Copy

Projektion:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...
Copy

Ergebnis der Abfrage (Inhalt von CTE zu Beginn der nächsten Iteration):

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...
Copy

Wie Sie sehen können, ist die Zeile im CTE am Ende der zweiten Iteration dieselbe wie zu Beginn der Iteration:

  • employee_id ist 1.

  • manager_id ist 2.

Daher ist das Ergebnis der Verknüpfung bei der nächsten Iteration das gleiche wie das Ergebnis der Verknüpfung bei der aktuellen Iteration und die Abfrage führt die Schleife endlos aus.

Wenn Sie eine Endlosschleife erstellt haben:

Lösung 2:

Stellen Sie sicher, dass die rekursive Klausel die korrekten Variablen in der korrekten Reihenfolge übergibt.

Stellen Sie außerdem sicher, dass die JOIN-Bedingung in der rekursiven Klausel korrekt ist. In einem typischen Fall sollte das übergeordnete Element der „aktuellen“ Zeile mit dem untergeordneten/aktuellen Wert der übergeordneten Zeile verknüpft werden.