Was ist ein CTE in SQL Server?

Was ist ein CTE, und wie schreibt man einen CTE in SQL Server? Begleiten Sie uns auf eine Reise, auf der wir alle typischen Verwendungen einer CTE in SQL Server kennen lernen.

CTEs (oder Common Table Expressions) sind eine SQL-Funktion, die zur Definition eines temporären benannten Ergebnisses verwendet wird. Sie können sich das wie eine temporäre Tabelle vorstellen, deren Ausgabe nur verfügbar ist, wenn die Hauptabfrage ausgeführt wird. Das ist praktisch, weil das CTE-Ergebnis nirgendwo gespeichert wird, sondern immer innerhalb der Abfrage wie jede andere Tabelle referenziert werden kann. CTEs werden am häufigsten in der Anweisung SELECT verwendet, sie können aber auch in den Anweisungen INSERT, UPDATE und DELETE verwendet werden.

CTEs sind ein relativ neues SQL-Feature. Sie wurden mit dem SQL: 1999 Standard (SQL 3) eingeführt. Im Jahr 2005 wurden sie in SQL Server 2005 verfügbar gemacht.

In unserem interaktiven Kurs Rekursive Abfragen in MS SQL Server können Sie praktische Erfahrungen mit CTEs in SQL Server sammeln. Sie lernen die Syntax von CTEs in SQL Server, wie Sie mehr als ein CTE verwenden, wie Sie sie verschachteln und wie Sie sie in SELECT, INSERT, UPDATE und DELETE einsetzen können. Es gibt auch einen Abschnitt, der die Rekursion erklärt und wie man eine rekursive CTE schreibt.

Wir werden alle diese Themen in diesem Artikel behandeln. Allerdings können wir hier nicht mit den 112 interaktiven Übungen mithalten, die der Kurs bietet. Neben dem Kurs und diesem Artikel gibt es noch einige andere Möglichkeiten, CTEs zu lernen.

SQL Server CTE-Syntax

Die grundlegende SQL Server CTE-Syntax lautet:

WITH cte AS (  
  SELECT
    ...
)

SELECT
  ...
FROM cte;

Ein CTE in SQL Server wird mit dem Schlüsselwort WITH eingeleitet. Dann folgen der CTE-Name (hier cte), das Schlüsselwort AS und die Klammern. Die Klammern enthalten die CTE-Definition. Einfach ausgedrückt handelt es sich um eine reguläre SELECT -Anweisung, nur in Form eines CTE.

Der nächste Teil kommt nach den Klammern und wird als Hauptabfrage bezeichnet. Ohne diesen Teil funktioniert die CTE nicht. Diese Hauptabfrage ist im allgemeinen Beispiel eine SELECT -Anweisung, die auf die CTE in der FROM -Klausel verweist. Wie bereits erwähnt, könnte die Hauptabfrage auch eine INSERT, UPDATE oder DELETE Anweisung anstelle von SELECT sein.

Eine andere Möglichkeit, eine CTE in SQL Server zu schreiben, ist die explizite Angabe der Spalten, die wie folgt aussieht:

WITH cte (cte_columns) AS (
  SELECT
    ...	
)

SELECT
  ...
FROM cte;

Der einzige Unterschied besteht darin, dass Sie die CTE-Spalten explizit vor dem Schlüsselwort AS definieren. Dies ist nützlich, wenn die CTE-Spalten Aliasnamen benötigen (z. B. wenn sie Funktionen enthalten); die Abfrage ist lesbarer, wenn die Aliasnamen auf diese Weise zugewiesen werden.

Wie Sie anhand der Beispiele in diesem Artikel sehen werden, ist das Hauptargument für die Verwendung von CTE in SQL Server die bessere Lesbarkeit des Codes. Sie können sich auch einige der anderen Vorteile ansehen.

Da Sie nun die grundlegende CTE-Syntax kennen, werden wir jeden Ansatz in einem Beispiel verwenden. Im weiteren Verlauf werden wir leichte Änderungen in der Syntax je nach Abfrageverwendung aufzeigen.

Beispiele für CTE in SQL Server

Bevor wir Code schreiben, sollten wir uns mit dem Datensatz vertraut machen. Die Tabelle ist flight_database, die historische Flugdaten enthält. Sie hat die folgenden Spalten:

• id - Die ID des Datensatzes und den Primärschlüssel (PK) der Tabelle.
• flight_id - Die Flugnummer nach IATA-Standard.
• airline - Der Name der Fluggesellschaft.
• flight_date - Das Datum des Fluges.
• departure_airport - Der Flughafen, von dem der Flug gestartet ist.
• arrival_airport - Der Flughafen, auf dem der Flug gelandet ist.
• planned_departure - Die Uhrzeit, zu der der Flug abfliegen sollte.
• actual_departure - Die Zeit des tatsächlichen Abflugs des Fluges.
• planned_arrival - Die Uhrzeit, zu der der Flug hätte ankommen sollen.
• actual_arrival - Die Uhrzeit der tatsächlichen Ankunft des Fluges.
• airport_distance - Die Entfernung zwischen dem Start- und dem Zielflughafen in Kilometern.

Dies sind fiktive Daten für den Flughafen Amsterdam Schiphol. Alle Zeiten sind GMT+1, was uns den Vergleich der Abflug- und Ankunftszeiten erleichtert.

Hier sind ein paar Zeilen dieser Tabelle:

Die Spalte id ist eindeutig, da es sich um den Primärschlüssel der Tabelle handelt. Die Spalte flight_id ist nicht eindeutig, da es Daten für dieselben Flüge an unterschiedlichen Daten gibt.

Sie können diesen Datensatz mit Hilfe des Codes im Link erstellen. Wenn Sie SQL Server installieren müssen, finden Sie hier eine Anleitung dazu.

Beispiel 1: Standard-CTE in SQL Server

Die Aufgabe besteht darin, eine CTE zu schreiben und die längsten Abflug- und Ankunftsverspätungen nach IATA-Flugnummer zu finden.

Hier ist die Abfrage:

WITH delay_times AS (
  SELECT 
    flight_id,
    flight_date,
    DATEDIFF(minute, planned_departure, actual_departure) AS departure_delay,
    DATEDIFF(minute, planned_arrival, actual_arrival) AS arrival_delay
  FROM flight_database
)

SELECT 
  flight_id,
  MAX(departure_delay) AS max_departure_delay,
  MAX(arrival_delay) AS max_arrival_delay
FROM delay_times
GROUP BY flight_id;

Die CTE beginnt mit dem Schlüsselwort WITH. Der CTE-Name lautet delay_times. Nach dem Schlüsselwort AS und der öffnenden Klammer folgt eine CTE-Definition in Form einer SELECT -Anweisung. Sie berechnet die Differenz zwischen dem geplanten und dem tatsächlichen Abflug mit Hilfe der Funktion DATEDIFF(). Der gleiche Ansatz wird bei der Berechnung der Differenz zwischen geplanter und tatsächlicher Ankunft angewandt. Beide Ergebnisse sind in Minuten angegeben. Da diese Spalten Funktionen verwenden, haben sie jeweils einen Alias.

Nachdem die Klammern geschlossen wurden, ist es an der Zeit, die Hauptabfrage zu schreiben. Es handelt sich um eine SELECT -Anweisung, die auf die delay_times CTE verweist und die Aggregatfunktionen von SQL Server MAX() zweimal verwendet, um die längste Abflug- und Ankunftsverspätung nach Flug zu berechnen.

Die Ausgabe sieht folgendermaßen aus. Die maximale Verspätung des Fluges DL 47 beim Abflug betrug 5 Minuten. Die maximale Verspätung bei der Ankunft war 0; der Flug kam immer pünktlich an.

Beispiel 2: CTE mit explizit definierten Spalten

Das folgende Beispiel ist sehr ähnlich. Der einzige Unterschied besteht darin, dass wir die kleinsten Abflug- und Ankunftsverspätungen pro Flug ermitteln wollen. Außerdem werden wir explizit definierte Spalten verwenden. Gehen Sie wie folgt vor:

WITH delay_times (flight_id, flight_date, departure_delay, arrival_delay) AS (
  SELECT 
    flight_id,
    flight_date,
    DATEDIFF(minute, planned_departure, actual_departure),
    DATEDIFF(minute, planned_arrival, actual_arrival)
  FROM flight_database
)

SELECT 
  flight_id,
  MIN(departure_delay) AS min_departure_delay,
  MIN(arrival_delay) AS min_arrival_delay
FROM delay_times
GROUP BY flight_id;

Diese CTE heißt wieder delay_times. Um die CTE-Spalten explizit zu definieren, schreiben Sie sie in Klammern vor das Schlüsselwort AS.

Die CTE selbst unterscheidet sich nicht wesentlich von der vorherigen: Sie verwendet wieder die Funktion DATEDIFF(), um die Verzögerungsdifferenzen zu berechnen. Die einzige Änderung besteht darin, dass die Aliasnamen für diese beiden (und die beiden anderen) Spalten früher definiert werden, und zwar mit dem Namen der CTE.

Die Hauptabfrage ist fast dieselbe wie zuvor. Der Unterschied besteht darin, dass sie jetzt die Funktion MIN() verwendet, da das Ziel darin besteht, die kleinsten Verzögerungen zu berechnen.

Das Ergebnis zeigt, dass die geringste Verspätung des Fluges DL 47 gleich Null war. Mit anderen Worten: Er war mindestens einmal pünktlich. Der Flug LH 2301 war nie pünktlich. Er war sowohl beim Abflug als auch bei der Ankunft mindestens 20 Minuten verspätet.

Beispiel 3: Eine verschachtelte CTE in SQL Server

In SQL Server liegt eine verschachtelte CTE vor, wenn es mindestens zwei CTEs gibt und die zweite CTE auf die erste verweist. Dies wird im folgenden Beispiel benötigt. Die Aufgabe besteht darin, die durchschnittliche Flugdauer in Minuten und die durchschnittliche Fluggeschwindigkeit in km/h zu berechnen.

Hier ist der Code:

WITH flight_duration AS (
  SELECT 
    flight_id,
    DATEDIFF(MINUTE, actual_departure, actual_arrival) AS minutes_of_flight,
    airport_distance
  FROM flight_database
),

average_flight_duration AS (
  SELECT 
    flight_id,
    AVG(minutes_of_flight) AS average_flight_duration,
    airport_distance
  FROM flight_duration
  GROUP BY flight_id, airport_distance
)

SELECT 
  flight_id,
  average_flight_duration,
  airport_distance/(CAST(average_flight_duration AS DECIMAL(10,2))/60) AS average_flight_speed
FROM average_flight_duration;

Die erste CTE wird wie üblich in SQL Server geschrieben: WITH Der Name der CTE ist AS und die Anweisung SELECT. Diese Abfrage errechnet die Flugdauer in Minuten. Nachdem Sie die Klammern geschlossen haben, schreiben Sie die zweite CTE. Aber Vorsicht: Die beiden CTEs müssen durch ein Komma voneinander getrennt werden. Wenn Sie mit dem Schreiben des zweiten CTE beginnen, gibt es auch kein WITH Schlüsselwort: Sie beginnen direkt mit dem CTE-Namen. Alles andere ist wie üblich. Diese zweite CTE verweist auf die erste CTE in der FROM -Klausel, um die durchschnittliche Flugdauer nach Flug für alle Daten zu berechnen.

Es gibt keine Unterschiede in der Syntax der Hauptabfrage. Diese Abfrage verweist auf die zweite CTE. Sie berechnet die durchschnittliche Fluggeschwindigkeit, indem sie die Entfernung zwischen den Flughäfen durch die durchschnittliche Flugdauer dividiert. Das Ergebnis wird in eine Dezimalzahl umgewandelt. Außerdem wird es durch 60 geteilt, so dass die Durchschnittsgeschwindigkeit in Kilometern pro Stunde angezeigt wird.

Die Ausgabe zeigt, dass z. B. der Flug LH 987 durchschnittlich 54 Minuten braucht, um das Ziel zu erreichen, und dass die Durchschnittsgeschwindigkeit 407,14 km/h beträgt.

Wenn Sie mehr üben möchten, finden Sie hier ein weiteres Beispiel für einen verschachtelten CTE.

Beispiel 4: Ein CTE in einer UPDATE-Anweisung

In diesem Beispiel zeigen wir Ihnen, wie CTEs in der Anweisung UPDATE funktionieren. So wie es unten gezeigt wird, könnten Sie auch die Anweisung INSERT verwenden.

Die Aufgabe ist die Aktualisierung der flight_database. Genauer gesagt, aktualisieren wir die Spalte airport_distance. Sie enthält derzeit Daten in Kilometern, sollte aber in Meilen geändert werden.

Und so geht's:

WITH distance_in_miles AS (
  SELECT 
    flight_id,
    airport_distance * 0.621371 AS airport_distance_miles
  FROM flight_database
)

UPDATE flight_database
SET airport_distance = airport_distance_miles
FROM distance_in_miles dim 
JOIN flight_database fd ON dim.flight_id = fd.flight_id;

Wie immer beginnen Sie mit dem Schlüsselwort WITH. Die CTE distance_in_miles wird verwendet, um Kilometer in Meilen umzuwandeln. Es ist ganz einfach: Multiplizieren Sie die Werte mit 0,621371.

Die Hauptabfrage lautet nun UPDATE anstelle von SELECT. Nichts Schwieriges; folgen Sie einfach der Anweisungssyntax. Aktualisieren Sie die Spalte airport_distance mit den Werten aus der Spalte airport_distance_miles, die in der CTE erscheint. Verbinden Sie die Tabelle und die CTE, und das war's. Die Tabelle ist aktualisiert.

Hier sehen Sie einige Werte vor der Aktualisierung:

Und hier sind die gleichen Zeilen mit den aktualisierten Abstandswerten:

Beispiel 5: Eine rekursive CTE in SQL Server

Unser letztes Beispiel ist das Schreiben einer rekursiven CTE in SQL Server. Dies ist eine CTE, die auf sich selbst verweist. Sie wird am häufigsten bei der Abfrage hierarchischer Daten (z. B. Unternehmensorganisation) oder bei Diagrammen verwendet, bei denen einige oder alle Teile miteinander verbunden sind (denken Sie an eine Straßenkarte mit den Entfernungen zwischen den Städten). Das Beispiel, das wir Ihnen zeigen werden, ist ein wenig einfacher. Das Wichtigste ist, dass Sie die Rekursion verstehen und wissen, wie man sie in eine CTE übersetzt.

Nehmen wir an, der Flughafen verfügt über eine bestimmte Anzahl von Flugslots. Außerdem gibt es einen Preis pro Slot. Jedes Jahr erhöht der Flughafen die Anzahl der Slots um 150; der Preis pro Slot bleibt gleich. Wir wollen die Anzahl der Slots, den Preis pro Slot und die gesamten Slot-Einnahmen für das aktuelle und die nächsten vier Jahre angeben.

Hier ist die rekursive CTE, um dies zu tun:

WITH airport_slots AS (
  SELECT 
    1 AS id,
    400000 AS number_of_slots,
    20574421.00 AS price_per_slot,
    CAST(20574421.00 * 400000 AS DECIMAL) AS slot_revenue
		   
  UNION ALL

  SELECT 
    id + 1,
    number_of_slots + 150,
    price_per_slot,
    CAST(price_per_slot * (number_of_slots + 150) AS DECIMAL)
  FROM airport_slots
  WHERE id <= 4
)

SELECT *
FROM airport_slots;

Auch hier beginnt die rekursive Abfrage mit dem Schlüsselwort WITH. Das erste SELECT in der CTE wird als Ankermitglied bezeichnet. Wir legen die Startwerte für die ID, die Anzahl der Slots und den Preis pro Slot fest. Außerdem multiplizieren wir diese beiden Werte, um die Slot-Einnahmen zu erhalten.

Dann kommt UNION ALL, das das Ankermitglied mit dem zweiten SELECT verbindet (das als rekursives Mitglied bezeichnet wird). UNION ALL schreibt vor, dass die Anzahl der Spalten und ihre Datentypen in beiden SELECT Anweisungen im CTE gleich sein müssen.

Das rekursive Mitglied verweist auf die CTE selbst. Bei jeder Rekursion wird die ID um eins und die Anzahl der Slots um 150 erhöht. Der Preis pro Slot bleibt dabei gleich. Die Slot-Einnahmen ergeben sich aus der erhöhten Anzahl der Slots multipliziert mit dem Preis pro Slot.

Wir haben auch die WHERE Klausel verwendet, um die Rekursion zu stoppen, sobald die ID gleich vier ist. Die Rekursion macht daraus fünf, und wir wollen eine Fünfjahresprojektion (aktuelles Jahr + nächste vier Jahre).

Danach ist es das Gleiche wie bei jeder CTE. Die Hauptabfrage ist SELECT.

Und das ist die Ausgabe:

Wir haben weitere Beispiele für rekursive CTEs in unserem Blog. Wir haben uns in diesem Artikel an die SQL Server-Syntax gehalten, aber Sie können auch lernen, wie man Rekursionen in PostgreSQL und Oracle durchführt.

SQL Server CTEs machen Sie besser!

Und zwar buchstäblich. Die meisten Aufgaben, die CTEs in SQL Server erfüllen, können auch mit einer Unterabfrage erledigt werden. Aber stellen Sie sich vor, wie die obigen Codes aussehen würden - nicht schön! Eine der typischen Verwendungen von CTEs in SQL Server besteht darin, dass sie Ihnen helfen, lange Abfragen zu organisieren. CTEs machen Abfragen besser lesbar, indem sie Teile der Abfrage benennen. Auf diese Weise können Sie jeden Teil einer komplexen Berechnung leicht aufschlüsseln und die Berechnung logisch gestalten.

Wenn Sie dies wissen, werden Sie und Ihr T-SQL-Code besser. Es gibt auch eine SQL-Funktion, die ohne CTEs nicht möglich ist: rekursive Abfragen. Sie sind unverzichtbar bei der Abfrage von hierarchischen und grafischen Daten. Alle Details dazu erfahren Sie in unserem Kurs Rekursive Abfragen in MS SQL Server.

Und wenn Sie sich für eine SQL-Stelle bewerben, gehen Sie vor dem Vorstellungsgespräch unbedingt unsere fünf CTE-Beispiele durch!