Was ist ein CTE in T-SQL? Eine ausführliche Anleitung für Anfänger mit 7 Beispielen

Ein gemeinsamer Tabellenausdruck (CTE) ist eine leistungsstarke T-SQL-Funktion, die die Erstellung von Abfragen in SQL Server vereinfacht. CTEs funktionieren wie virtuelle Tabellen (mit Datensätzen und Spalten), die während der Ausführung einer Abfrage spontan erstellt werden. Sie werden von der Abfrage verbraucht und nach der Ausführung der Abfrage gelöscht.

In einigen Fällen - z. B. wenn die Abfrage Daten in einem bestimmten Format erwartet und die Quelltabellen die Daten in einem anderen Format haben - kann eine CTE als Brücke dienen, um die Daten in den Quelltabellen in das von der Abfrage erwartete Format umzuwandeln.

In diesem Artikel wird beschrieben, wie Sie eine CTE in T-SQL, dem von Microsoft SQL Server verwendeten SQL-Dialekt, erstellen können. Um noch mehr über CTEs in T-SQL zu erfahren, empfehle ich unseren interaktiven Kurs Rekursive Abfragen in MS SQL Server. Er enthält über 100 praktische Übungen zu CTEs in T-SQL. Sie werden alles, was in diesem Artikel behandelt wird, selbständig üben!

Gemeinsame Tabellenausdrücke (CTEs) in T-SQL

Während der Ausführung einer Abfrage können Sie sich auf den CTE wie auf eine normale Tabelle beziehen. Er kann in den Anweisungen SELECT, INSERT, UPDATE und DELETE verwendet werden. CTEs waren nicht Teil der ursprünglichen SQL-Sprache. Sie wurden 1999 als neue Funktion in die SQL-Standarddefinition aufgenommen (SQL 3). Im Jahr 2005 wurden sie in SQL Server verfügbar gemacht.

Die in diesem Artikel gezeigten SQL-Beispielabfragen basieren auf der Tabelle olympic_games. Die Tabelle enthält die Ergebnisse aller olympischen Spiele, einschließlich 3 Datensätze (Gold, Silber und Bronze) für jede Sportart.

Beispiel 1: Eine einfache CTE

Um die CTE-Syntax in T-SQL zu erklären, lassen Sie uns eine einfache Abfrage schreiben. Angenommen, wir wollen einen Bericht mit Ländern und der Anzahl der Goldmedaillen, die jedes Land im Marathon gewonnen hat. Wir können zunächst eine CTE mit dem Namen gold_in_marathon erstellen, um die Zeilen mit den Goldmedaillen bei Marathonläufen von Männern und Frauen zurückzugeben. In der CTE gold_in_marathon benötigen wir nur die Spalten city, year und country.

Beachten Sie, dass alle CTEs mit dem Schlüsselwort WITH beginnen, gefolgt von Klammern. In die Klammern schreiben Sie die Abfrage, die die CTE zurückgeben soll. Unten sehen Sie die vollständige Abfrage. Die CTE ist dabei fett gedruckt:

WITH gold_in_marathon AS
(
  SELECT 
    city, 
    year, 
    country
  FROM olympic_games
  WHERE medal_type = 'Gold' 
  AND sport IN ('Marathon Men', 'Marathon Women')
)
SELECT 
  country, 
  count(*) AS gold_medals_in_marathon 
FROM gold_in_marathon
GROUP BY country
ORDER BY gold_medals_in_marathon DESC;

In der obigen T-SQL-Abfrage können Sie zwei verschiedene Abfragen erkennen. Die erste wird durch die in Klammern eingeschlossene WITH -Klausel definiert: Dies ist die Abfrage, die den Inhalt des CTEs definiert. Dies ist eine reguläre SQL-Abfrage: Sie können alle verschiedenen SQL-Merkmale eingeben (WHERE, GROUP BY, HAVING, UNION, usw.). Die Datenbank verwendet das Ergebnis, um eine virtuelle Tabelle namens gold_in_marathon zu erstellen. Sie können den Namen der CTE (gold_in_marathon) nach der WITH Klausel sehen.

Die zweite Abfrage ist die äußere Abfrage. Sie verweist auf die CTE gold_in_marathon wie auf jede andere Tabelle. Wenn die Ausführung der äußeren Abfrage abgeschlossen ist, wird die CTE gold_in_marathon zerstört, und Sie können nicht mehr auf sie verweisen.

Beachten Sie, dass die CTE nur die Spalten city, year und country aus der Tabelle olympic_games. Nach dem Erstellen der CTE führt die Datenbank die äußere Abfrage aus, die wiederum die CTE gold_in_marathon liest, die Zeilen nach country gruppiert und die Funktion COUNT() verwendet, um die Anzahl der Goldmedaillen für jedes Land zu ermitteln. Unten sehen Sie die Ergebnisse der Abfrage:

Beispiel 2: Verwendung von CTEs mit umbenannten (Aliased) Spalten in T-SQL

In der nächsten Abfrage werden wir eine Spalte in der CTE explizit umbenennen, indem wir einen Alias verwenden. Nehmen wir an, wir wollen einen Bericht mit einer Länderrangliste für Marathonergebnisse (Frauen und Männer). Jeder Spieler erhält 3 Punkte für jede Goldmedaille, 2 Punkte für jede Silbermedaille und 1 Punkt für jede Bronzemedaille. Wir werden ein CTE mit dem Namen player_points erstellen, um die Punkte der einzelnen Spieler zu berechnen. Da wir eine berechnete Spalte für die Punkte verwenden werden, müssen wir dieser Spalte mithilfe eines Alias einen Namen zuweisen. Schauen wir uns die vollständige Abfrage an:

WITH player_points AS
(
  SELECT 
    country,
    winner_name, 
    SUM(
      CASE medal_type 
        WHEN 'Gold' THEN 3
	  WHEN 'Silver' THEN 2
	  WHEN 'Bronze' THEN 1
	END
	)  AS player_total
    FROM   olympic_games
    WHERE sport in ('Marathon Men', 'Marathon Women')
    GROUP BY country,winner_name
)
SELECT 
  country, 
  SUM(player_total) AS country_points 
FROM player_points
GROUP BY country
ORDER BY country_points DESC;

In der player_points CTE berechnen wir die Punkte jedes Spielers anhand einer berechneten Spalte, die keinen Namen hat. Wir müssen einen Namen für diese Spalte definieren, um in der äußeren Abfrage auf sie verweisen zu können.

Eine Möglichkeit, einen Namen zu definieren, ist die Verwendung eines Alias (später werden wir eine andere Möglichkeit sehen) unter Verwendung der Klausel AS. Wie Sie sehen können, wurde die Spalte in der CTE-Definition player_total genannt. In der äußeren Abfrage gruppieren wir die Zeilen nach Land, um die Gesamtpunktzahl für jedes Land zu berechnen. Beachten Sie, dass wir den Ausdruck SUM(player_total) verwenden und wiederum einen Alias verwenden, um die Spalte in country_points umzubenennen. Die Ergebnisse der Abfrage sehen Sie unten:

Bevor ich diesen Abschnitt schließe, möchte ich Ihnen den Artikel What Is a CTE in SQL Server? empfehlen, in dem Sie viele Beispiele für Abfragen mit CTEs in T-SQL finden. Der Artikel Was ist ein Common Table Expression (CTE) in SQL? behandelt das Thema CTEs in Standard-SQL; beide Artikel sind eine gute Ergänzung zu diesem Artikel. Wenn Sie einen Kurs über CTEs und rekursive Abfragen besuchen möchten, empfehle ich wiederum Rekursive Abfragen in MS SQL Server.

Beispiel 3: Verwendung mehrerer CTEs in derselben T-SQL-Abfrage

In diesem Abschnitt werden wir zwei Beispielabfragen zeigen, die mehr als eine CTE verwenden. Im ersten Beispiel werden wir zwei unabhängige CTEs verwenden, und die Hauptabfrage wird auf beide zugreifen.

Angenommen, wir möchten einen Bericht mit den Namen der olympischen Athleten erstellen, die mindestens eine Gold- und eine Silbermedaille gewonnen haben. Der erste CTE heißt gold. Nach dem CTE-Namen sehen Sie die Namen der Spalten (winner_name und gold_medals), die explizit in Klammern definiert sind. Dies ist die andere Möglichkeit, eine Spalte in der CTE umzubenennen.

Die zweite CTE hat den Namen silver und zwei Spalten: winner_name und silver_medals. Beachten Sie, dass wir die Klausel WITH nicht vor die zweite CTE gesetzt haben. Die Klausel WITH wird nur ein einziges Mal vor der ersten CTE-Definition verwendet. Wenn wir mehr CTEs definieren müssen, brauchen wir nur ein Komma vor der Definition der nächsten CTE(s).

WITH gold(winner_name,gold_medals) AS
(
  SELECT 
    winner_name, 
    count(*)
  FROM olympic_games
  WHERE medal_type = 'Gold'
  GROUP BY winner_name
), 
silver(winner_name,silver_medals) AS
(
  SELECT 
    winner_name,
    count(*)
  FROM olympic_games
  WHERE medal_type = 'Silver'
  GROUP BY winner_name
)
SELECT  
  g.winner_name, 
  g.gold_medals, 
  s.silver_medals
FROM gold g 
JOIN silver s 
ON g.winner_name = s.winner_name;

In der vorherigen T-SQL-Abfrage haben wir zwei CTEs erstellt: gold und silver mit der winner_name und der Anzahl der von jedem Spieler gewonnenen Medaillen (Gold oder Silber). Dann haben wir in der Hauptabfrage beide CTEs mit einer JOIN Klausel verbunden, als ob es sich um reguläre Tabellen handeln würde. Da JOIN ohne Schlüsselwörter als INNER JOIN funktioniert, werden im Abfrageergebnis nur Datensätze für dieselbe winner_name in beiden Tabellen angezeigt. Die Ausgabe ist unten dargestellt:

Beispiel 4: Eine SQL Server-Abfrage mit einer CTE, die auf einer anderen CTE basiert

Als Nächstes werden wir zwei CTEs in einer Abfrage erstellen, wobei das zweite CTE auf dem ersten CTE basiert. Angenommen, wir möchten eine Abfrage erstellen, um die TOP 3 Länder für die Anzahl der bei einer Olympiade gewonnenen Medaillen zu erhalten. Wir wollen die Länder nicht wiederholen, d.h. wenn die erste und zweite Position für dasselbe Land sind, wollen wir dieses Land nur einmal anzeigen. Die Abfrage wird wie folgt aussehen:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS quantity_of_medals
  FROM olympic_games
  GROUP BY city, year, country
), 
country_best_game AS
(
  SELECT  
    country, 
    city, 
    year, 
    quantity_of_medals 
  FROM medals m1
  WHERE quantity_of_medals = ( 
    SELECT max(quantity_of_medals) 
    FROM medals m2
    WHERE  m1.country = m2.country
  )
)
SELECT TOP 3 country, 
  city, 
  year, 
  quantity_of_medals 
FROM country_best_game 
ORDER BY quantity_of_medals DESC;

In dieser Abfrage haben wir einen CTE mit der Bezeichnung medals erstellt, der die Anzahl der Medaillen enthält, die jedes Land bei den einzelnen olympischen Spielen gewonnen hat, bei denen das Land mindestens eine Medaille gewonnen hat. Danach berechnen wir einen zweiten CTE mit der Bezeichnung country_best_game (basierend auf dem ersten CTE); er enthält eine Zeile für jedes Land mit der maximalen Anzahl von Medaillen, die in einem einzigen Spiel gewonnen wurden. Schließlich wählen wir in der Hauptabfrage nur die drei Länder mit den meisten Medaillen in einem einzelnen Spiel aus. Die Ergebnisse der Abfrage sehen Sie unten:

An dieser Stelle möchte ich Ihnen den Artikel How to Learn SQL Common Table Expressions empfehlen, in dem Sie verschiedene Ansätze zum Erlernen von CTEs nachlesen können. Sie werden auch mehrere Beispielabfragen sehen, die CTEs in Standard-SQL verwenden.

Beispiel 5: Verwendung eines CTE in einem T-SQL INSERT

In T-SQL können CTEs auch in den Anweisungen UPDATE, INSERT und DELETE verwendet werden. Generell gilt, dass jeder SQL-Befehl, der eine eingebettete SELECT (z. B. eine CREATE VIEW -Anweisung) zulässt, eine CTE unterstützen kann. Sehen wir uns ein Beispiel für eine INSERT mit einem gemeinsamen Tabellenausdruck in T-SQL an.

Nehmen wir an, wir haben eine Tabelle namens country_medals_by_game mit den Spalten country, city, game, number_of_medals, und delta_with_previous_game. Der Inhalt jeder Spalte ist klar, mit Ausnahme der Spalte delta_with_previous_game. Wir werden diese Spalte jedoch noch nicht verwenden, daher werden wir sie später erläutern. Die INSERT zum Auffüllen der Tabelle ist die folgende:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS medals_won
  FROM olympic_games
  GROUP BY city, year, country
)
INSERT INTO country_medals_by_game(city,year,country,quantity_of_medals)
SELECT city, year, country, medals_won
FROM medals;

In der vorherigen INSERT haben wir eine CTE namens medals verwendet. Beachten Sie, dass INSERT mit der CTE-Definition beginnt (Sie können bei Bedarf mehrere CTEs definieren). Nach der Definition des CTE beginnt die reguläre Anweisung INSERT. In diesem Beispiel verwendet INSERT eine SELECT -Anweisung, die ihrerseits auf die zuvor definierte medals CTE zugreift.

Beispiel 6: Verwendung einer CTE in einem SQL Server UPDATE

Die Spalte delta_with_previous_game speichert die Differenz der Medaillen, die ein Land bei zwei aufeinanderfolgenden Olympischen Spielen gewonnen hat. Wenn das Land die Anzahl der gewonnenen Medaillen erhöht hat, enthält diese Spalte die Differenz der Medaillenanzahl als positiven Wert. Wenn das Land weniger Medaillen gewonnen hat als bei der vorherigen Olympiade, hat die Spalte einen negativen Wert. Das UPDATE zum Auffüllen der Spalte lautet:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS medals_won
  FROM olympic_games
  GROUP BY city, year, country
)
UPDATE country_medals_by_game 
SET delta_with_previous_game = (
 SELECT COALESCE(quantity_of_medals - COALESCE(prevgame.medals_won,0),0)
 FROM medals prevgame
 WHERE country_medals_by_game.year = prevgame.year + 4
  AND country_medals_by_game.country = prevgame.country
);

In dieser UPDATE haben wir mit einer WITH Klausel begonnen, um die gleiche medals CTE zu definieren, die wir bereits verwendet haben. Am Ende des CTE-Definitionsabschnitts beginnt die Anweisung UPDATE. In der SET Klausel verwenden wir eine Unterabfrage, um die Differenz der bei zwei aufeinander folgenden Olympischen Spielen gewonnenen Medaillen zu berechnen. Beachten Sie, dass die Unterabfrage auf die CTE medals zugreift und die Bedingung ...

country_medals_by_game.year = prevgame.year + 4

... besteht darin, eine Zeile in country_medals_by_game mit der Zeile in Medaillen für die vorangegangenen Olympischen Spiele (die vier Jahre zuvor stattfanden) abzugleichen. Interessant ist folgende Feststellung: Für die Länder, die nicht an zwei zusammenhängenden Spielen teilgenommen haben, setzen wir die Spalte delta_with_previous_game auf NULL. Dies bedeutet, dass wir die Differenz nicht berechnen können; die Verwendung einer Null für diese Spalte ist nicht korrekt, da dies bedeuten würde, dass das Land an den vorherigen Spielen teilgenommen hat, was nicht der Fall war.

Beispiel 7: Verwendung von CTEs für Rekursive Abfragen in T-SQL

In SQL Server ist es üblich, Tabellen zu haben, die Datenhierarchien darstellen (z. B. Mitarbeiter-Manager, Teil-Teil oder Eltern-Kind). Um diese Hierarchien in beliebiger Richtung zu durchlaufen - von oben nach unten oder von unten nach oben - verwendet SQL Server ein Konstrukt namens rekursive CTEs.

Um eine Datenhierarchie in unserer Olympic-Datenbank zu haben, fügen wir der Tabelle ein Spaltenpaar hinzu olympic_games. Nehmen wir an, wir wollen die Medaillen identifizieren, die einen Weltrekord darstellen. Wir können eine Textspalte mit der Bezeichnung record hinzufügen und sie mit true belegen, wenn eine Medaille mit einem neuen Weltrekord verbunden ist.

Außerdem wissen wir, dass jeder Rekord einen vorherigen Rekord bricht, also fügen wir eine weitere Spalte mit der Bezeichnung previous_record_medal hinzu, in der wir die medal_id des vorherigen Rekords eintragen. Wir haben nun eine Datenhierarchie, die wir mit einer rekursiven Abfrage lesen können. olympic_games Tabelle mit ihren neuen Spalten:

Nehmen wir an, wir möchten einen Bericht mit der Liste der Weltrekorde im Marathonlauf der Männer erstellen. Wir können mit der Anzeige des aktuellen Weltrekords beginnen, danach den unmittelbar vorhergehenden Rekord anzeigen und so weiter. Wir benötigen eine rekursive CTE, um die Hierarchie der Marathonrekorde der Männer zu durchlaufen. Sie sieht wie folgt aus:

WITH record_history (medal_id, year, winner, country, prev_record_medal_id) AS
(
  SELECT 
    medal_id, 
    year, 
    winner_name, 
    country, 
    previous_record_medal
  FROM olympic_games
  WHERE sport = 'Marathon Men' AND record = 'true'

  UNION ALL

  SELECT 
    og.medal_id,
    og.year,
    og.winner_name,
    og.country, 
    og.previous_record_medal
  FROM olympic_games og 
  JOIN record_history mrh 
  ON og.medal_id = mrh.prev_record_medal_id
)
SELECT * 
FROM record_history;

Die CTE record_history ist das Ergebnis einer UNION ALL. Die erste Abfrage in UNION dient dazu, den aktuellen Weltrekord zu ermitteln; beachten Sie die Bedingung record = true. Nach UNION ALL folgt eine weitere Abfrage, die alle bisherigen Rekorde im Marathonlauf der Männer ermittelt. Der Schlüssel zur Verbindung einer Medaillenrekordzeile mit der vorherigen Medaillenrekordzeile ist die Bedingung:

og.medal_id = mrh.prev_record_medal_id

Die Ergebnisse der Abfrage sind unten aufgeführt:

Um eine Endlosschleife in einer rekursiven CTE zu verhindern, gibt es eine Grenze für die Anzahl der zulässigen Aufrufe. In SQL Server ist diese standardmäßig auf 100 festgelegt. Sie können diese Grenze jedoch mit dem Parameter MAXRECURSION am Ende der rekursiven Abfrage ändern.

Wenn Sie sich eingehender mit rekursiven Abfragen beschäftigen möchten, empfehle ich Ihnen die Artikel How to Write a Recursive CTE in SQL Server und Do it in SQL: Rekursiver SQL-Baum-Traversal. Sie finden dort mehrere Beispiele und verschiedene Ansätze zur Erklärung rekursiver Abfragen.

CTEs sind eine leistungsstarke Funktion der T-SQL Sprache

In diesem Artikel haben wir behandelt, wie man T-SQL CTEs verwendet, um komplexe Abfragen für SQL Server-Datenbanken zu vereinfachen. Bevor ich schließe, möchte ich noch einige Artikel zum Thema SQL Server empfehlen. Der erste ist Top 5 SQL CTE Interview Questions, in dem Sie Tipps finden, wie Sie ein Bewerbungsgespräch für einen SQL-Job meistern können. Ein weiterer interessanter Artikel ist How to Install Microsoft SQL Server 2019 and SQL Server Management Studio, in dem Sie Hilfe bei der Installation von SQL Server finden können.

Schließlich möchte ich Ihnen noch den Kurs Rekursive Abfragen in MS SQL Server ans Herz legen. Dort lernen Sie, wie man Bäume und Graphen in T-SQL verarbeitet und wie Sie Ihre Abfragen effektiv organisieren. Entwickeln Sie Ihre Fähigkeiten und steigern Sie Ihr Vermögen!