6 nützliche Beispiele für CTEs in SQL Server

Wie können Sie CTEs in SQL Server in Ihrem Berufsalltag als Datenprofi verwenden? Diese Frage beantworten wir Ihnen anhand von sechs Beispielen.

CTE ist die Abkürzung für Common Table Expression. Dabei handelt es sich um eine relativ neue Funktion in SQL Server, die mit SQL Server 2005 zur Verfügung gestellt wurde.

Ein CTE ist ein temporäres, benanntes Ergebnis. Dieses Ergebnis ist nur für die Abfrage verfügbar, die es ausführt. Es wird nicht gespeichert, so dass es keinen Festplattenspeicher beansprucht. Eine CTE ist einer temporären Tabelle ähnlich und kann wie jede andere Tabelle verwendet werden. CTEs werden am häufigsten mit der Anweisung SELECT verwendet, können aber auch mit INSERT, UPDATE und DELETE verwendet werden.

CTEs sind eines der anspruchsvollsten Konzepte in SQL Server. Um ihre Vorteile zu nutzen, sollten Sie sie sorgfältig strukturiert und nicht überstürzt erlernen. Unser Kurs Rekursive Abfragen in MS SQL Server zeigt Ihnen zunächst, wie Sie eine einfache CTE in SQL Server schreiben. Danach lernen Sie, mehrere CTEs zu schreiben, sie zu verschachteln und sie in SELECT, INSERT, UPDATE und DELETE Anweisungen zu verwenden. Schließlich lernen Sie etwas über hierarchische und grafische Datenstrukturen und wie Sie rekursive CTEs in SQL Server zur Abfrage solcher Daten verwenden können. Um sicherzustellen, dass Sie genug Übung bekommen, gibt es 112 interaktive Übungen, die Sie in diesem Kurs absolvieren können.

Es gibt natürlich noch einige andere Möglichkeiten, CTEs zu lernen, die Sie sich ebenfalls ansehen können.

CTE-Syntax in SQL Server

Im Allgemeinen ist die CTE-Syntax in SQL Server wie im folgenden Beispiel dargestellt:

WITH cte AS (  
  SELECT
    ...
)

SELECT
  ...
FROM cte;

CTEs müssen immer mit dem Schlüsselwort WITH beginnen. Dann folgen der Name des CTE, das Schlüsselwort AS und die Klammern. In diesen Klammern definieren Sie den CTE. Wie Sie in unseren Beispielen sehen werden, bedeutet die Definition, dass Sie die Anweisung SELECT schreiben. Weitere Einzelheiten finden Sie in diesem Artikel, in dem erklärt wird, was eine CTE ist.

6 Beispiele für CTEs in SQL Server

1: Ermitteln der durchschnittlichen Höchst- und Tiefstwerte der täglichen Datenströme

In den ersten fünf Beispielen verwenden wir denselben Datensatz. Er zeigt einige erfundene Daten von einer imaginären Musik-Streaming-Plattform, nennen wir sie Terpsichore.

Der Datensatz besteht aus drei Tabellen. Die erste ist artistund hier ist die Abfrage zum Erstellen der Tabelle. Diese Tabelle enthält die folgenden Spalten:

• id - Die ID des Künstlers und den Primärschlüssel der Tabelle.
• artist_name - Der Name des Künstlers.

Diese Tabelle enthält drei Künstler.

Die nächste Tabelle ist albums. Hier ist die Abfrage, um sie zu erstellen. Und hier sind die Spalten, die sie enthält:

• id - Die ID des Albums und der Primärschlüssel der Tabelle.
• artist_id - Der Interpret (und der Fremdschlüssel der Tabelle).
• album_title - Der Titel des Albums.
• year_released - Das Jahr der Veröffentlichung des Albums.

Es gibt zehn Alben in der Tabelle.

Die letzte Tabelle ist streams. Sie zeigt die Streaming-Daten für die einzelnen Songs. Sie können die Tabelle mit dieser Abfrage erstellen. Und die Spalten:

• id - Die Stream-ID und der Primärschlüssel der Tabelle.
• artist_id - Die ID des Interpreten und ein Fremdschlüssel.
• album_id - Die ID des Albums und ein Fremdschlüssel.
• song_title - Der Name des Liedes.
• date - Das Datum des Streams.
• number_of_streams - Die Anzahl, wie oft der Titel an einem bestimmten Datum gespielt wurde.
• pay_per_stream - Der Wert (in Dollar), den Terpsichore an die Künstler für jeden Stream zahlt.

Diese Tabelle enthält 45 Zeilen. Wir zeigen Ihnen nur die ersten fünf, damit Sie ein Gefühl für die Logik der Tabelle bekommen.

Und nun das Beispiel! Beginnen wir damit, nur eine CTE in SQL Server zu schreiben. Damit berechnen wir die durchschnittliche höchste und niedrigste Anzahl der täglichen Streams.

Hier ist der Code; wir werden ihn weiter unten erklären:

WITH daily_streaming AS (
  SELECT date,
	   MIN(number_of_streams) AS minimum_streaming,
	   MAX(number_of_streams) AS maximum_streaming
  FROM streams
  GROUP BY date
)

SELECT AVG(minimum_streaming) AS average_minimum_daily_streaming,
	 AVG(maximum_streaming) AS average_maximum__daily_streaming
FROM daily_streaming;

Eine CTE eignet sich hervorragend, um die Logik einer beliebigen Berechnung in SQL Server aufzuschlüsseln.

Denken Sie daran, dass an einem Tag mehrere Songs gestreamt werden. Der Zweck unserer CTE ist es, die niedrigste und die höchste Anzahl von Streams pro Tag zu ermitteln.

Wie bereits erwähnt, beginnt eine CTE in SQL Server immer mit dem Schlüsselwort WITH, dem der CTE-Name folgt. Unser CTE heißt daily_streaming.

Nach dem Schlüsselwort AS folgt in Klammern eine Anweisung SELECT - also die CTE-Definition. Wir verwenden sie zusammen mit den Aggregatfunktionen MIN() und MAX(), um die höchste und niedrigste Anzahl von Streams nach Datum zu berechnen.

Die nächste Anweisung SELECT verwendet Daten aus der CTE, auf die in der Klausel FROM verwiesen wird. Wie bereits erwähnt, kann eine CTE wie jede andere Tabelle verwendet werden. In dieser SELECT verwenden wir die Aggregatfunktion AVG(), um den Durchschnitt der täglichen Höchst- und Tiefstwerte der Datenströme zu ermitteln.

Die Ausgabe zeigt, dass der durchschnittliche Tiefpunkt bei 90 Strömen liegt. Der Durchschnitt der höchsten täglichen Streams liegt bei 8.367.

2: Berechnen Sie die durchschnittliche Gesamtvergütung pro Song

Jetzt wollen wir das Gelernte in der Praxis anwenden. Wir werden wieder eine einzelne CTE schreiben.

Das zu lösende Problem besteht darin, die durchschnittliche Gesamtgebühr zu ermitteln, die Terpsichore für jeden Song bezahlt hat.

Hier ist die Lösung:

WITH paid_per_song AS (
  SELECT song_title,
	   SUM(number_of_streams * pay_per_stream) AS total_pay
  FROM streams
  GROUP BY id, song_title, pay_per_stream
)

SELECT song_title,
       AVG(total_pay) AS average_total_pay
FROM paid_per_song
GROUP BY song_title
ORDER BY average_total_pay DESC;

Wir verwenden SQL Server CTE, um die Gesamtgebühr pro Song zu berechnen, indem wir die Anzahl der Streams mit der Gebühr pro Stream multiplizieren und dann mit der Aggregatfunktion SUM() summieren.

An der CTE-Syntax ändert sich nichts: Zuerst kommt WITH, dann der CTE-Name, und danach kommt AS.

Dann verwenden wir ein SELECT, das das CTE aufruft, um die durchschnittliche Vergütung pro Song zu berechnen. Es ist ganz einfach: Verwenden Sie AVG(), verweisen Sie auf die CTE in FROM und gruppieren Sie nach dem Titel des Liedes.

Die Abfrage liefert das folgende Ergebnis:

Wir sehen, dass der Song "Uptown" insgesamt 47,4803330 $ verdient hat. Die zweit- und drittplatzierten Songs nach Einnahmen sind "I Wanna Be Your Lover" und "Little Red Corvette". Wenn du ein Fan dieses Künstlers bist, dann brauchst du kein SQL, um herauszufinden, wer alle drei Songs geschrieben hat.

3: Finde das am meisten gestreamte Album jedes Künstlers

In dieser Übung müssen Sie das am meisten gestreamte Album jedes Künstlers finden. Geben Sie den Namen des Künstlers, den Titel des Albums und die Anzahl der Streams pro Album aus.

Auch hier handelt es sich um eine Abfrage mit nur einer CTE. Sie ist jedoch etwas komplexer als die beiden vorherigen - es gibt einige JOINs und eine Fensterfunktion.

WITH album_streaming AS (
  SELECT artist_id,
	   album_id,
	   SUM(number_of_streams) AS streams_by_album,
	   RANK() OVER (PARTITION BY artist_id ORDER BY SUM(number_of_streams) DESC) AS streaming_rank
  FROM streams
  GROUP BY artist_id, album_id
)

SELECT artist_name,
       album_title,
	 streams_by_album
FROM album_streaming alst
JOIN albums al
ON alst.album_id = al.id
JOIN artist ar 
ON al.artist_id = ar.id
WHERE streaming_rank = 1;

Auch hier ist die CTE-Syntax in SQL Server vertraut. Konzentrieren wir uns also darauf, was diese CTE tut. Wir verwenden sie, um die Alben nach Künstler zu ordnen. Zunächst wählen wir die IDs von Künstler und Album aus. Dann verwenden wir SUM(), um die Anzahl der Streams pro Album zu berechnen.

Jetzt kommt der entscheidende Teil - das Ranking der Ausgabe mit der Funktion RANK() window. Nach dem Aufruf der Funktion sehen wir die Klausel OVER() - eine obligatorische Klausel für SQL-Fensterfunktionen. Wir partitionieren den Datensatz nach der Künstler-ID und ordnen die Daten innerhalb jeder Partition nach der Anzahl der Streams in absteigender Reihenfolge.

Was bedeutet das in der Praxis? Es bedeutet, dass die Fensterfunktion die Alben eines Interpreten in eine Rangfolge bringt, dann wird die Rangfolge neu gestartet, sobald die Funktion den nächsten Interpreten erreicht, und so weiter. Das Album des Künstlers mit den meisten Streams wird in seiner Partition an erster Stelle gereiht.

Wenn Sie nur diese SELECT -Anweisung innerhalb der CTE ausführen, erhalten Sie diese Ausgabe:

Wie Sie sehen können, werden die Alben des ersten Künstlers nach der Anzahl der Streams von der ersten bis zur dritten Stelle gereiht. Wenn wir den zweiten Künstler erreichen, beginnt die Rangfolge von neuem. Das Gleiche gilt für den dritten Künstler.

Schauen wir uns nun an, was die zweite Anweisung SELECT bewirkt. Eigentlich ist es nichts Kompliziertes. Sie gibt den Namen des Künstlers und des Albums sowie die Anzahl der Streams zurück. Was diese Abfrage kompliziert macht, ist, dass wir drei Tabellen verknüpfen müssen.

Die erste Verknüpfung ist die CTE album_streaming. Dann verbinden wir sie mit albums und dann mit der artist Tabelle. Am Ende filtern wir die Daten mit der WHERE Klausel, da wir nur an dem am meisten gestreamten Album interessiert sind.

Sie erhalten dieses Ergebnis:

Das meistgestreamte Album von Prince ist "Dirty Mind" mit 13.820 Streams. Bei Jimi Hendrix ist das meistgestreamte Album "Are You Experienced" und bei Santana ist es "Abraxas".

Bei dieser Lösung werden die Fensterfunktionen verwendet, daher hier eine Erinnerung daran, wie sie beim Ranking von Daten funktionieren.

4: Berechnen Sie die durchschnittlichen Streams pro Song und vergleichen Sie sie mit den durchschnittlichen Streams pro Datum

Jetzt werden die Dinge etwas komplizierter. Aber nicht zu sehr, keine Sorge. Wir bauen auf dem auf, was wir bisher über CTEs in SQL Server gelernt haben.

Hier müssen wir die durchschnittliche Anzahl der Streams pro Song ermitteln. Dann müssen wir die durchschnittliche Anzahl der Streams pro Datum berechnen.

Die Ausgabe sollte beide Metriken zeigen. Außerdem sollte die Differenz zwischen dem durchschnittlichen Stream pro Song und dem täglichen Durchschnitt (als prozentuale Differenz), der Songtitel und die Daten angezeigt werden.

Bis jetzt haben wir Abfragen mit einer CTE geschrieben. Dieses Mal besteht die Lösung aus zwei CTEs. Schauen wir uns an, wie das funktioniert:

WITH streams_per_song AS (
  SELECT song_title,
	   AVG(number_of_streams) AS average_streams_per_song
  FROM streams
  GROUP BY song_title
),

streams_per_date AS (
  SELECT date,
	   AVG(number_of_streams) AS average_streams_per_date
  FROM streams
  GROUP BY date
)

SELECT song_title,
	 average_streams_per_song,
	 date,
	 average_streams_per_date,
	 (average_streams_per_song - average_streams_per_date)/CAST(average_streams_per_date AS DECIMAL(10,2))*100 AS diff_from_daily_average
FROM streams_per_song, streams_per_date;

Die erste CTE wird wie üblich geschrieben. Wir verwenden sie, um die durchschnittliche Anzahl der Streams pro Song mit AVG() zu berechnen.

Nach dem Schließen der Klammern muss die erste CTE von der zweiten CTE mit einem Komma getrennt werden.

Dann schreiben wir den zweiten CTE. Und siehe da! Es gibt keine WITH! Das ist richtig. Wenn Sie mehrere CTEs in eine Abfrage in SQL Server schreiben, schreiben Sie WITH nur vor die erste CTE. Die zweite (und jede folgende CTE) beginnt mit dem Namen der CTE; alles andere ist gleich.

Diese zweite Abfrage dient zur Berechnung der durchschnittlichen Anzahl der Streams pro Datum. Auch hier verwenden wir die Funktion AVG().

Die dritte SELECT verwendet die Daten aus beiden CTEs. Sie gibt alle erforderlichen Spalten zurück. Die letzte Spalte ist diff_from_daily_average. Wir berechnen sie, indem wir die durchschnittlichen Streams pro Datum von den durchschnittlichen Streams pro Song abziehen. Die Differenz wird durch die durchschnittlichen Streams pro Datum geteilt und mit 100 multipliziert, um den Prozentsatz zu erhalten. Außerdem haben wir das Ergebnis mit der Funktion CAST() in einen dezimalen Datentyp umgewandelt.

Aufgrund der Größe der Ausgabe zeigen wir nur die ersten paar Zeilen:

Die Daten zeigen, dass der durchschnittliche tägliche Stream für den 1. Januar 2023 1.031 beträgt. "All Along the Watchtower" liegt 21,14 % unter diesem Durchschnitt. Die nächsten beiden Songs liegen 28,13% und 43,45% unter dem Tagesdurchschnitt, und so weiter.

5: Berechnen Sie den höchsten und den niedrigsten durchschnittlichen Albumpreis pro Künstler

Lassen Sie uns erklären, was wir damit meinen. Wir wollen zunächst die Durchschnittsvergütung nach Album und Datum ermitteln. Dann müssen wir den niedrigsten und den höchsten Gehaltswert pro Album ermitteln. Danach wollen wir die Daten nach Künstler aggregieren. Zusammen mit dem Namen des Künstlers müssen wir den Wert der niedrigsten Gage anzeigen, die der Künstler für ein Album erhalten hat. Dasselbe gilt für die höchste Gage für ein Album.

Die Lösung in SQL Server enthält zwei CTEs. Dieses Mal handelt es sich jedoch um eine verschachtelte CTE. Das heißt, die zweite CTE verweist auf die erste CTE. Schauen wir uns an, wie das funktioniert:

WITH pay_per_album AS (
  SELECT album_id,
	   date,
	   AVG(number_of_streams * pay_per_stream) AS average_pay_per_album
  FROM streams
  GROUP BY album_id, date
),
	
min_max_average_pay AS (
  SELECT album_id,
	   MIN(average_pay_per_album) AS lowest_average_pay_by_album,
	   MAX(average_pay_per_album) AS highest_average_pay_by_album
  FROM pay_per_album
  GROUP BY album_id
)

SELECT artist_name,
	 MIN(lowest_average_pay_by_album) AS lowest_album_pay_by_artist,
	 MAX(highest_average_pay_by_album) AS highest_album_pay_by_artist
FROM min_max_average_pay mmap
JOIN albums al
ON mmap.album_id = al.id
JOIN artist ar
ON al.artist_id = ar.id
GROUP BY artist_name;

Seien Sie vorsichtig, wenn Sie die Code-Erklärung lesen! Es ist leicht, sich in all diesen Aggregationen zu verirren. Um die Sache einfacher zu machen, habe ich jeden Teil der Abfrage kopiert und mit einer Erklärung versehen. Wir beginnen mit der ersten CTE:

WITH pay_per_album AS (
  SELECT album_id,
	   date,
	   AVG(number_of_streams * pay_per_stream) AS average_pay_per_album
  FROM streams
  GROUP BY album_id, date
),

Die erste CTE berechnet die durchschnittliche Vergütung pro Album und das Datum. Dazu wird die Anzahl der Streams mit dem Preis pro Stream multipliziert und AVG() verwendet.

min_max_average_pay AS (
  SELECT album_id,
	   MIN(average_pay_per_album) AS lowest_average_pay_by_album,
	   MAX(average_pay_per_album) AS highest_average_pay_by_album
  FROM pay_per_album
  GROUP BY album_id
)

Wenn Sie die zweite CTE in SQL Server schreiben, ist die Syntax die gleiche wie im vorherigen Beispiel - keine zusätzlichen WITH, beginnen Sie mit dem CTE-Namen und trennen Sie die CTEs mit einem Komma. Der einzige Unterschied besteht darin, dass die zweite CTE diesmal auf die erste CTE und nicht auf das ursprüngliche Dataset verweist.

Dieser verschachtelte CTE verwendet die Funktionen MIN() und MAX(), um für jedes Album den niedrigsten und höchsten Durchschnittslohn für alle Daten zu ermitteln. Die erste CTE wird in FROM referenziert.

SELECT artist_name,
	 MIN(lowest_average_pay_by_album) AS lowest_album_pay_by_artist,
	 MAX(highest_average_pay_by_album) AS highest_album_pay_by_artist
FROM min_max_average_pay mmap
JOIN albums al
ON mmap.album_id = al.id
JOIN artist ar
ON al.artist_id = ar.id
GROUP BY artist_name;

Schließlich gibt es SELECT, das den zweiten CTE mit den Funktionen albums und artist Tabellen verbindet. Wir wenden erneut die Funktionen MIN() und MAX() auf das Ergebnis der zweiten CTE an. Dies dient dazu, nur die Werte des niedrigsten und des höchsten gezahlten Preises aller Alben eines jeden Künstlers zurückzugeben.

Nach Ausführung der Abfrage erhalten wir das folgende Ergebnis:

6: Suche nach dem längsten Weg zwischen Rotterdam und Amsterdam

Dies ist eine Abwandlung des Problems des kürzesten Pfades in der Graphentheorie, mit dem Unterschied, dass wir nach dem längsten Pfad suchen werden.

Ein Graph ist eine Art Datenstruktur, die aus Knoten oder Punkten besteht, die durch Kanten verbunden sind. Da sie miteinander verbunden sind, ist es möglich, einen Weg von einem Knoten zum anderen zu finden, auch wenn sie nicht direkt miteinander verbunden sind.

Stellen Sie sich das als eine Karte mit Straßen vor. Das ist genau das Beispiel, das wir hier verwenden werden. Unten sehen Sie die Tabelle cities_distancedie die Städte und die Entfernung zwischen ihnen anzeigt. Verwenden Sie diese Abfrage, um die Tabelle zu erstellen. Sie enthält diese Spalten:

• city_from - Die Ausgangsstadt.
• city_to - Die Ankunftsstadt.
• distance - Die Entfernung zwischen den beiden Städten, in Kilometern.

Hier sind die Daten:

Wir müssen den längsten Weg von Rotterdam nach Amsterdam finden. Der Weg sollte die Namen aller Städte entlang des Weges enthalten, getrennt durch '/'. Außerdem sollten wir die Länge des längsten Weges angeben.

Wenn wir von dem längsten Weg sprechen, wollen wir Rundwege ausschließen (bei denen man endlos viele Runden machen und die Entfernung vergrößern kann). Wir wollen, dass dieser längste Weg nur einmal durch eine bestimmte Stadt führt.

Um dieses Problem zu lösen, werden wir eine rekursive CTE verwenden. Dabei handelt es sich um eine Abfrage, die auf sich selbst verweist, bis sie das Ende der Daten erreicht. Diese Eigenschaft ist ideal für die Abfrage von Diagrammdaten, bei denen mehrere Wege zum gleichen Ziel führen können.

Schauen wir uns an, wie diese rekursive CTE funktioniert:

WITH longest_path AS (
  SELECT cd.city_to,
         CAST((cd.city_from + '/' + cd.city_to) AS VARCHAR(100)) AS path,
        cd.distance AS distance
   FROM cities_distance cd
   WHERE cd.city_from = 'Rotterdam'

UNION ALL

  SELECT cd.city_to,
         CAST((lp.path + '/' + cd.city_to) AS VARCHAR(100)) AS path,
         CAST((lp.distance + cd.distance) AS DECIMAL(10,2))
  FROM longest_path lp 
  INNER JOIN cities_distance cd
  ON cd.city_from = lp.city_to
  WHERE lp.city_to <> 'Amsterdam'
  AND lp.path NOT LIKE '%/' + cd.city_to + '/%'
)

SELECT TOP 1 lp.path,
	 lp.distance
FROM longest_path lp
WHERE lp.city_to = 'Amsterdam'
ORDER BY lp.distance DESC;

Die Syntax sieht genauso aus wie zuvor - die rekursive Abfrage beginnt auch in SQL Server mit WITH.

Wie üblich gibt es eine SELECT -Anweisung in den Klammern. Es sind zwei, um genau zu sein. Schauen wir uns an, was die erste Anweisung bewirkt.

WITH longest_path AS (
  SELECT cd.city_to,
         CAST((cd.city_from + '/' + cd.city_to) AS VARCHAR(100)) AS path,
         cd.distance AS distance
  FROM cities_distance cd
  WHERE cd.city_from = 'Rotterdam'

Die erste SELECT in der Rekursion wird Ankermitglied genannt. Es wird verwendet, um den Startpunkt der Rekursion zu bestimmen. Der Startpunkt wird Rotterdam sein, was wir durch Filtern dieser Stadt in WHERE erhalten. Die Spalte city_to wird verwendet, um alle Endziele anzuzeigen, die direkt von Rotterdam aus erreicht werden können. In der Spalte path werden alle Ausgangs- und Zielorte aufgelistet. Die Länge dieser Route wird in der Spalte distance angezeigt.

Dann folgt UNION ALL, die die Ergebnisse des Ankers und des rekursiven Mitglieds, d.h. der zweiten SELECT, verbindet. Die Zusammenführung dieser beiden Abfragen ist notwendig, damit die Rekursion funktioniert.

Hinweis: In einigen anderen SQL-Dialekten ist es auch möglich, UNION zu verwenden. SQL Server erlaubt jedoch nur UNION ALL.

Nun kommen wir zu dem rekursiven Mitglied. Es referenziert das CTE selbst in FROM und verbindet es mit der Tabelle cities_distance. Damit die Abfragen vereinigt werden können, müssen sie beide die gleiche Anzahl von Spalten desselben Datentyps haben. Die ersten beiden Spalten sind die gleichen wie im Ankermitglied. Die Spalte longest_path summiert alle Entfernungen, um alle Städte von Rotterdam aus zu erreichen.

SELECT cd.city_to,
       CAST((lp.path + '/' + cd.city_to) AS VARCHAR(100)) AS path,
       CAST((lp.distance + cd.distance) AS DECIMAL(10,2))
FROM longest_path lp 
INNER JOIN cities_distance cd
ON cd.city_from = lp.city_to
WHERE lp.city_to <> 'Amsterdam'
AND lp.path NOT LIKE '%/' + cd.city_to + '/%'
)

Wir haben auch zwei Bedingungen in WHERE hinzugefügt. Die erste schließt alle Beziehungen aus, bei denen Amsterdam das Endziel ist; wir suchen den längsten, nicht den kürzesten Weg nach Amsterdam. Die zweite Bedingung stellt sicher, dass jede neue Stadt, die dem Pfad hinzugefügt wird, nicht bereits in diesem Pfad enthalten ist. Andernfalls würde die Abfrage in eine endlose Rekursion münden. Dies entspricht dem oben Gesagten: Der längste Weg sollte nicht mehr als einmal in dieselbe Stadt führen.

Um besser zu verstehen, wovon wir sprechen, sehen Sie hier die Ausgabe der rekursiven CTE:

Sie können sehen, dass es drei Wege von Rotterdam nach Amsterdam gibt. Eine ist direkt, die Entfernung beträgt 78,20 km. Der zweite und dritte Weg führen über andere Städte und sind 165,90 bzw. 120,50 km lang.

Wohlgemerkt, dies ist nicht das Endergebnis! Unsere Lösung enthält auch eine SELECT -Anweisung, die auf das CTE verweist:

SELECT TOP 1 lp.path,
	 lp.distance
FROM longest_path lp
WHERE lp.city_to = 'Amsterdam'
ORDER BY lp.distance DESC;

Diese SELECT gibt den Weg und die Entfernung zurück. Wir verwenden den Befehl TOP 1 in Kombination mit ORDER BY, um den längsten Weg von Rotterdam nach Amsterdam zu ermitteln. Wir haben die Daten von der längsten bis zur kürzesten Entfernung sortiert, so dass die erste Zeile auch die längste Strecke ist.

Hier ist das Endergebnis:

Diese Ausgabe zeigt, dass der längste Weg von Rotterdam nach Amsterdam 165,9 km beträgt und über Leiden, Gouda und Utrecht führt.

Wenn Sie von diesem Thema nicht genug bekommen können, finden Sie hier noch einige weitere CTE-Beispiele.

Wann sollten Sie CTEs verwenden?

Wie Sie in unseren Beispielen gesehen haben, hat eine CTE in SQL Server viele Funktionen.

Einer davon ist, dass sie im Allgemeinen die Lesbarkeit des Codes verbessert. Alle oben genannten Lösungen (außer der Rekursion) hätten auch mit Unterabfragen geschrieben werden können. Aber das würde den Code viel länger und unübersichtlicher machen.

Die Unterabfragen werden in der Regel entgegen der Logik des Problems geschrieben, das man zu lösen versucht. Sie haben zunächst die Hauptabfrage, die die Ausgabe der Unterabfrage verwendet. Die Unterabfrage ist also in der Regel der erste Schritt der Berechnung, auch wenn sie im Code nicht so platziert ist.

CTEs hingegen können so geschrieben werden, dass sie der Logik des Problems folgen. Sie können mehrere separate CTEs schreiben und ihre Ausgaben in die SELECT Anweisungen einbinden. Sie können auch auf die Ausgabe einer CTE mit der zweiten Abfrage (oder dritten, vierten...) verweisen, wobei die letzte SELECT Anweisung eine weitere Berechnungsebene darstellt.

In einem der Beispiele wurde auch gezeigt, wie man Daten in SQL Server mit Hilfe einer Fensterfunktion und einer CTE einordnen kann.

Und wenn Sie rekursive Abfragen in SQL Server schreiben wollen, geht das nicht ohne CTEs. Ein CTE kann nicht rekursiv sein, aber ohne CTE gibt es keine rekursiven Abfragen. Neben Graphen ist die Rekursion äußerst hilfreich bei der Abfrage hierarchischer Strukturen, wie z. B. der Datenorganisation und von Stammbäumen.

Mehr über die Verwendung von CTEs finden Sie hier.

CTEs sind das Tor zur Fortgeschrittenes SQL Nutzung!

CTEs sind eines der fortgeschritteneren SQL Server-Konzepte. Wenn Sie sich die Tür zu fortgeschrittenen SQL Server-Fähigkeiten öffnen wollen, sind CTEs ein Muss.

Wenn Ihre Abfragen in SQL Server immer komplexer werden, werden Sie bald feststellen, dass das Erlernen von CTEs eine der besten Entscheidungen war, die Sie je getroffen haben. Sie sind auch ein Sprungbrett für rekursive Abfragen, mit denen Sie ungewöhnliche Arten von Datenstrukturen in SQL Server abfragen können, z. B. Hierarchien und Diagramme.

Dieser Artikel ist nur eine Vorschau auf das Wissen, das Sie im Kurs Rekursive Abfragen in MS SQL Server finden können. Hören Sie hier also nicht auf. Es gibt noch viel mehr zu lernen!