Wie man eine rekursive CTE in SQL Server schreibt

Ein Leitfaden zum Verständnis und zur Verwendung rekursiver CTEs in SQL Server

SQL Server bietet viele leistungsstarke Tools für die Arbeit mit Daten, einschließlich Common Table Expressions (CTEs). Ein CTE ist eine temporäre benannte Ergebnismenge, auf die Sie in einer SELECT, INSERT, UPDATE oder DELETE Anweisung verweisen können. CTEs können lange Abfragen in kleinere, besser handhabbare Teile der Logik aufteilen und sie lesbarer machen.

SQL Server bietet rekursive Common Table Expressions. Mit einem rekursiven Common Table Expression (CTE) in SQL Server können Sie rekursive Abfragen zu hierarchischen oder graphenbasierten Datenstrukturen, wie z. B. Organigrammen, Familienstammbäumen, Transportnetzwerken usw., durchführen. Rekursive Abfragen werden verwendet, um die Beziehungen zwischen den Datenelementen zu durchlaufen.

In diesem Artikel werden wir mit der Standard-CTE-Syntax beginnen und dann untersuchen, wie rekursive CTEs in SQL Server geschrieben werden können.

Eine gute Möglichkeit, Ihr Verständnis für rekursive CTEs zu vertiefen, ist unser Kurs über Rekursive Abfragen in MS SQL Server. Hier lernen Sie, wie Sie die anspruchsvollste Art von Abfragen meistern und lange SQL-Abfragen effektiv organisieren können.

Grundlagen von CTEs in SQL Server

Lassen Sie uns zunächst die Grundlagen von CTEs besprechen. Ein CTE beginnt mit der WITH Klausel, gefolgt vom Namen des CTE und der SELECT Anweisung, die es definiert. Die Syntax einer CTE sieht wie folgt aus:

WITH cte_name AS (
   SELECT 
     column1,
     column2,
     …
   FROM table
   … 
) 
SELECT … 
FROM cte_name, …;

Fangen wir ganz oben an. Als erstes geben wir unserer CTE einen Namen, cte_name. Diese CTE wählt einige Spalten (column1, column2, ...) aus table. Der Rest der Abfrage (die äußere Abfrage) kann sich auf die CTE und ihre Spalten beziehen, als ob die CTE eine normale Tabelle wäre.

Sie können sich eine CTE als temporäre Ansicht vorstellen, auf die Sie in der äußeren Abfrage verweisen können, wie wir es im obigen Beispiel tun.

Angenommen, Sie möchten eine Abfrage schreiben, die die Einkäufe zurückgibt, die von den fünf Mitarbeitern mit dem höchsten Gehalt in der Vertriebsabteilung verwaltet werden. Die Tabellen, die wir in diesem Beispiel verwenden werden, heißen employees (enthält die Spalten employee_id, employee_name, und salary) und purchase (enthält die Spalten id, date, customer_id, und total_amount).

WITH top_5_sales_employees AS (
   SELECT TOP 5
     employee_id,
     employee_name 
   FROM employees
   WHERE department_id = ‘Sales’ 
   ORDER BY salary DESC
) 
SELECT 
  p.id AS purchase_id,
  p.date,
  p.total_amount,
  e.id AS employee_id
  e.employee_name 
FROM top_5_sales_employees e
JOIN purchase p
ON p.employee_id = e.id;

Diese CTE beginnt mit dem Schlüsselwort WITH und dem Namen der CTE, top_5_sales_employees. In den Klammern wählen wir die Spalten aus, die wir in die CTE aufnehmen wollen, und geben die Bedingungen für die Abfrage in der Klausel WHERE an. Schließlich verwenden wir die Anweisung SELECT, um die entsprechenden Spalten aus der CTE auszuwählen, als ob es sich um eine reguläre Tabelle handeln würde.

Wenn Sie mehr darüber erfahren möchten, was CTEs sind und wie Sie sie schreiben, lesen Sie unseren Artikel Was ist eine CTE in SQL Server? Wenn Sie nach weiteren CTE-Beispielen suchen, finden Sie diese in diesem Artikel.

Wie man rekursive CTEs in SQL Server verwendet

Was ist also ein rekursiver CTE in SQL Server? Ein rekursiver CTE ist ein CTE-Typ, der in der SELECT-Anweisung auf sich selbst verweist und eine Schleife bildet. Rekursive CTEs werden verwendet, um hierarchische Datenstrukturen zu durchlaufen, wie z. B. Organigramme oder Netzwerke.

Nehmen wir an, wir haben eine Tabelle namens employees mit Spalten für den Namen des Mitarbeiters, die Abteilung und den Vorgesetzten. Dies ist in der folgenden Tabelle dargestellt, in der jeder Datensatz einen Mitarbeiter und seinen Vorgesetzten innerhalb der Organisation enthält.

Auf den ersten Blick ist leicht zu erkennen, wer wem unterstellt ist und wie diese Organisationshierarchie aussieht. Hätten wir jedoch Hunderte von Mitarbeitern, wäre es viel schwieriger, den Sinn der Daten zu erkennen.

Wir können eine rekursive CTE verwenden, um einen hierarchischen Baum mit den Mitarbeitern des Unternehmens zu erstellen. Dazu würden wir diese Abfrage ausführen:

WITH employee_manager_cte AS (
  SELECT 
    id, 
    name,
    department,
    manager_id,
    manager_name,
    1 AS level 
  FROM employees 
  WHERE manager_id IS NULL 
  UNION ALL 
  SELECT 
    e.id, 
    e.name,
    e.department,
    e.manager_id,
    e.manager_name,
    level + 1 
  FROM employees e 
  INNER JOIN employee_manager_cte r 
	ON e.manager_id = r.id 
) 
SELECT * 
FROM employee_manager_cte;

Lassen Sie uns diese Abfrage Schritt für Schritt aufschlüsseln.

Zunächst definieren wir die rekursive CTE mit dem Namen employee_manager_cte. Wir wählen die Spalten aus, die wir in die Abfrage aufnehmen wollen: id, name, department, manager_id, manager_name und level. Die Spalte level wird verwendet, um die Tiefe des Baums zu verfolgen. Wir beginnen mit der Ebene 1; im Laufe der Schleife erhöht sich diese Zahl.

Dieser Abschnitt vor UNION ALL wird als Ankerelement bezeichnet. Im Ankerelement beginnen wir unsere Schleife. In unserem Beispiel wählen wir alle Mitarbeiter aus, deren Vorgesetzter NULL ist. In unserem Organigramm sind das die Mitarbeiter, die ganz oben stehen. In diesem Fall gibt es nur einen Mitarbeiter auf dieser Ebene: Sarah Smith, die Geschäftsführerin.

Der Teil nach UNION ALL wird rekursives Mitglied genannt. Im rekursiven Mitglied fügen wir neue Zeilen zu den bereits berechneten Zeilen hinzu. In unserem Beispiel verbinden wir die Tabelle employees Tabelle mit der employee_manager_cte CTE in der Spalte manager_id. Dadurch entsteht eine Schleife, die den Baum von oben nach unten durchläuft. Wir fügen 1 zur Spalte level hinzu, um die Tiefe jedes Knotens zu verfolgen.

Schließlich wählen wir alle Spalten aus der employee_manager_cte CTE aus.

Wenn Sie diese Abfrage ausführen, verarbeitet SQL Server zunächst das Ankerelement, das Sarah Smith als Wurzel des Baums auswählt. Anschließend verarbeitet er das rekursive Mitglied, das Sarah Smith mit ihrem direkten Bericht (Jane Miller) verbindet. Dann verbindet er Jane Miller mit ihrem direkten Bericht (John Doe) und John Doe mit seinem direkten Bericht (keine). Da der Ergebnismenge keine weiteren Zeilen hinzugefügt werden können, beendet SQL Server die Verarbeitung der CTE und gibt das Endergebnis zurück.

Hier sehen Sie, wie die Ergebnismenge aussieht:

Ein weiterer hervorragender Erklärungsartikel, den Sie sich ansehen können, ist Was ist eine rekursive CTE in SQL? Dort finden Sie weitere praktische Beispiele für rekursive CTEs in SQL.

Tipps zum Schreiben von rekursiven CTEs in SQL Server

1. Beginnen Sie mit dem Ankerelement

Das Ankermitglied ist der Startpunkt der rekursiven CTE. Es ist der Teil der Abfrage, der den Basisfall oder den ersten Satz von Zeilen definiert, die zurückgegeben werden. In unserem Beispiel mit dem Organigramm ist dies die oberste Ebene der Verwaltung. Stellen Sie sicher, dass das Ankermitglied alle Spalten zurückgibt, die Sie in der endgültigen Ergebnismenge benötigen.

2. Stellen Sie sicher, dass die Anzahl der Spalten übereinstimmt.

Das rekursive Mitglied und das Ankermitglied müssen die gleiche Anzahl von Spalten und die gleichen Datentypen haben, die den Spalten entsprechen: In einem rekursiven CTE verweist das rekursive Mitglied auf das CTE selbst, daher ist es wichtig sicherzustellen, dass das rekursive Mitglied und das Ankermitglied die gleiche Anzahl von Spalten und die gleichen Datentypen in den entsprechenden Spalten haben.

3. Immer UNION ALL

In SQL Server dürfen Sie UNION ALL nur verwenden, um ein Ankerelement und ein rekursives Element zu kombinieren: Wenn Sie das Ankermitglied und das rekursive Mitglied kombinieren, müssen Sie UNION ALL und nicht UNION verwenden. UNION ALL erhält alle Zeilen, einschließlich der Duplikate, während UNION die Duplikate entfernt. UNION ist in rekursiven Abfragen in SQL Server nicht erlaubt.

4. Hüten Sie sich vor Endlosschleifen!

Stellen Sie sicher, dass Sie eine Abbruchprüfung in Ihre Abfrage schreiben. Eine Abbruchprüfung ist eine Bedingung, die verhindert, dass die rekursive CTE in einer unendlichen Schleife läuft. Ohne eine Abbruchprüfung führt die Abfrage standardmäßig maximal 100 Rekursionen aus und bricht dann ab.

Die Abbruchprüfung ist normalerweise in der WHERE-Klausel des rekursiven Elements enthalten und gibt an, wann die Rekursion beendet werden soll.

Wenn Sie diese Tipps befolgen, können Sie effiziente und effektive rekursive CTEs schreiben, mit denen Sie komplexe hierarchische Probleme in SQL Server lösen können. Weitere Einzelheiten finden Sie in diesem Artikel über bewährte CTE-Verfahren.

Rekursive CTEs in SQL Server - weitere Beispiele

Beispiel 1: Rekursive CTE für ein Transportnetzwerk

Ein weiteres Beispiel für die Verwendung einer rekursiven CTE ist die Darstellung eines Verkehrsnetzes. Angenommen, wir haben eine Tabelle namens routes die Informationen über Transportrouten zwischen Städten speichert. Sie enthält die Ausgangsstadt, die Zielstadt und die Entfernung zwischen den Städten. Wir möchten eine Abfrage schreiben, die alle Städte zurückgibt, die von einer bestimmten Ausgangsstadt aus erreichbar sind, zusammen mit der Gesamtentfernung zu jeder Stadt.

Unsere Daten befinden sich in einer Tabelle namens routes:

Hier ist die Abfrage:

WITH recursive_cte AS (
  SELECT 
    source_city, 
    destination_city, 
    distance,
    source_city AS visited_cities 
   FROM routes 
   WHERE source_city = ‘New York’
   
   UNION ALL 
   
   SELECT 
     r.source_city, 
     r.destination_city, 
     r.distance + rc.distance,
     rc.visited_cities + ‘,’ + r.destination_city
   FROM routes r 
   INNER JOIN recursive_cte rc 
	ON r.source_city = rc.destination_city 
   WHERE rc.distance < 2000
AND CHARINDEX(',' + r.destination_city + ',', ',' +                                 rc.visited_cities + ',') = 0
) 
SELECT 
  destination_city, 
  distance
FROM recursive_cte

Diese rekursive CTE beginnt mit dem Ankerelement, das alle Routen auswählt, die in New York beginnen. Im rekursiven Mitglied verbinden wir die Tabelle Routen mit der recursive_cte CTE auf der Spalte source_city, um alle Städte zu finden, die von New York aus erreichbar sind.

Wir haben eine neue Spalte mit der Bezeichnung visited_cities hinzugefügt, in der die Liste der besuchten Städte als kommagetrennte Zeichenfolge gespeichert wird. Wir initialisieren diese Spalte im Ankerelement der CTE, indem wir sie auf die Ausgangsstadt setzen. Im rekursiven Element verketten wir die aktuelle Stadt mit der Liste der besuchten Städte und prüfen mit der Funktion CHARINDEX, ob die Zielstadt bereits besucht wurde. Wenn die Zielstadt noch nicht besucht wurde, fügen wir sie der Liste der besuchten Städte hinzu und setzen die Rekursion fort

Wir fügen der Ergebnismenge so lange Zeilen hinzu, bis keine weiteren Städte mehr hinzuzufügen sind oder bis wir unsere Abbruchprüfung erreichen. Die Ergebnismenge zeigt alle Städte, die von New York aus erreichbar sind, und die Gesamtentfernung zu jeder Stadt.

Beispiel 2: Verwendung einer rekursiven CTE für Aufgabenabhängigkeiten in einem Projekt

Ein weiteres Szenario, in dem wir eine rekursive CTE verwenden könnten, wäre das Verständnis von Aufgabenabhängigkeiten für ein Projekt.

Angenommen, wir haben eine Tabelle namens tasks die Informationen über Aufgaben in einem Projekt speichert, einschließlich der task_id, der task_name, der ID der Aufgabe, von der sie abhängt (depends_on_task_id), und der time_required zur Fertigstellung der Aufgabe. Wir möchten eine Abfrage schreiben, die die Gesamtzeit berechnet, die für die Erledigung einer bestimmten Aufgabe erforderlich ist, einschließlich aller abhängigen Aufgaben.

Die Daten werden unten angezeigt:

Schreiben wir eine Abfrage, die die Gesamtzeit, die für die Entwicklungsaufgabe und ihre Abhängigkeiten benötigt wird, abruft.

WITH recursive_cte AS (
  SELECT 
    task_id, 
    task_name, 
    depends_on_task_id, 
    time_required
  FROM tasks 
  WHERE task_id = 2
  
  UNION ALL 

  SELECT 
    t.task_id, 
    t.task_name, 
    depends_on_task_id, 
    t.time_required + rc.time_required
  FROM tasks t 
  INNER JOIN recursive_cte rc 
	ON t.depends_on_task_id = rc.task_id
WHERE rc.total_time < 20) 
SELECT 
  task_name, 
  time_required as total_time
FROM recursive_cte
GROUP BY task_name;

Diese rekursive CTE beginnt mit dem Ankerelement, das die Zeile in der Tabelle tasks Tabelle mit task_id = 2 (Entwicklung) auswählt. Im rekursiven Mitglied verbinden wir die tasks mit der Tabelle recursive_cte CTE über die Spalten depends_on_task_id und task_id, um alle Aufgaben zu finden, die von Entwicklung abhängen. Wir berechnen die für jede Aufgabe benötigte Gesamtzeit, indem wir die für die aktuelle Aufgabe benötigte Zeit zur Gesamtzeit der vorherigen Aufgaben addieren.

Schließlich werden die Ergebnisse nach task_name und time_required für jede Aufgabe abgefragt. Hier sind die Ergebnisse:

Die Spalte task_name zeigt den Namen jeder Aufgabe, und die Spalte total_time zeigt die Gesamtzeit, die für die Aufgabe und alle vorherigen Aufgaben benötigt wird. Die erste Zeile zeigt zum Beispiel, dass die Gesamtzeit, die für die Entwicklung und alle Abhängigkeiten benötigt wird, 15 beträgt, was der Summe der Zeit entspricht, die für die Entwicklung (10) und die Tests (5) benötigt wird.

Die anderen Zeilen zeigen dasselbe Konzept, wobei die Spalten task_name und total_time die Namen der einzelnen Aufgaben und deren Gesamtzeitbedarf angeben.

Erfahren Sie mehr über rekursive CTEs in SQL Server

Rekursive CTEs in SQL Server können in einer Vielzahl von Szenarien nützlich sein, wie zum Beispiel:

• Mitarbeiterhierarchien: Eine rekursive CTE kann verwendet werden, um einen Baum von Mitarbeitern und ihren Managern zu durchlaufen und die Gesamtvergütung aller Mitarbeiter in einem bestimmten Zweig zu berechnen.
• Produktkategorien: Ein rekursiver CTE kann verwendet werden, um einen Baum von Produktkategorien zu durchlaufen und alle Produkte in einer bestimmten Kategorie und ihren Unterkategorien zu finden.
• Soziale Netzwerke: Ein rekursiver CTE kann verwendet werden, um einen Graphen sozialer Netzwerkverbindungen zu durchlaufen, um alle Freunde eines bestimmten Benutzers und deren Verbindungen zu finden.
• Abhängigkeiten von Aufgaben: Eine rekursive CTE kann dazu verwendet werden, einen Baum von Aufgabenabhängigkeiten zu durchlaufen, um die für die Fertigstellung eines Projekts erforderliche Zeit zu berechnen.

Im Allgemeinen kann jedes Szenario, in dem Daten hierarchisch oder in einer Graphenstruktur organisiert sind, von der Verwendung einer rekursiven CTE profitieren. Wenn Sie sich an bewährte Verfahren halten, z. B. mit dem Ankermitglied beginnen, sicherstellen, dass das rekursive und das Ankermitglied dieselbe Anzahl von Spalten und Datentypen haben, und eine Abbruchprüfung schreiben, können Sie effiziente und genaue Abfragen schreiben, die hierarchische Datenstrukturen durchlaufen und komplexe Probleme lösen.

Vertiefen Sie Ihr Wissen über rekursive CTEs in unserem Kurs Rekursive Abfragen in MS SQL Server. Die 112 interaktiven Übungen werden Ihnen helfen, dieses fortgeschrittene Thema zu beherrschen!