Good Mood

Zusammenfassung: in diesem tutorial erfahren Sie, wie Sie die common table expression, oder CTE in SQL Server mithilfe der WITH – Klausel.

Einführung von CTE in SQL Server

CTE steht für common table expression. Mit einem CTE können Sie eine temporäre benannte Ergebnismenge definieren, die vorübergehend im Ausführungsbereich einer Anweisung verfügbar ist, z. B. SELECT, INSERT, UPDATE, DELETE oder MERGE.,

Das Folgende zeigt die gängige Syntax eines CTE in SQL Server:

Code language: SQL (Structured Query Language) (sql)

Geben Sie in dieser Syntax:

• Zunächst den Ausdrucksnamen (expression_name) an, auf den Sie später in einer Abfrage verweisen können.
• Geben Sie als Nächstes eine Liste von durch Kommas getrennten Spalten nach dem expression_name an. Die Anzahl der Spalten muss der Anzahl der Spalten entsprechen, die in CTE_definitiondefiniert sind.
• Verwenden Sie dann das Schlüsselwort AS nach dem Ausdrucksnamen oder der Spaltenliste, wenn die Spaltenliste angegeben ist.,
• Definieren Sie danach eineSELECT – Anweisung, deren Ergebnismenge den allgemeinen Tabellenausdruck füllt.
• Beziehen Sie sich abschließend auf den allgemeinen Tabellenausdruck in einer Abfrage (SQL_statement), z. B. SELECT, INSERT, UPDATE, DELETE oder MERGE.

Wir bevorzugen es, allgemeine Tabellenausdrücke zu verwenden, anstatt Unterabfragen zu verwenden, da allgemeine Tabellenausdrücke besser lesbar sind., Wir verwenden CTE auch in den Abfragen, die Analysefunktionen (oder Fensterfunktionen) enthalten

SQL Server CTE Beispiele

Nehmen wir einige Beispiele für die Verwendung gängiger Tabellenausdrücke.

A) Einfaches SQL Server CTE-Beispiel

Diese Abfrage verwendet eine CTE, um die Verkaufsbeträge von Vertriebsmitarbeitern im Jahr 2018 zurückzugeben:

Das folgende Bild zeigt die Ergebnismenge:

In diesem Beispiel:

• Zuerst haben wir cte_sales_amounts als den Namen des allgemeinen Tabellenausdrucks definiert., der CTE gibt ein Ergebnis zurück, das aus drei Spalten besteht staff, year und sales abgeleitet von der Definitionsabfrage.
• Zweitens haben wir eine Abfrage erstellt, die den Gesamtumsatz nach Vertriebsmitarbeiter und Jahr zurückgibt, indem Daten aus den Tabellen orders, und staffs abgefragt wurden.
• Drittens haben wir in der äußeren Abfrage auf die CTE verwiesen und nur die Zeilen ausgewählt, deren Jahr 2018 ist.,

Beachten Sie, dass dieses Beispiel ausschließlich zu Demonstrationszwecken dient, damit Sie nach und nach verstehen, wie allgemeine Tabellenausdrücke funktionieren. Es gibt einen optimaleren Weg, um das Ergebnis ohne Verwendung von CTE zu erzielen.

B) Verwenden eines gemeinsamen Tabellenausdrucks zum Erstellen von Berichtsdurchschnitten basierend auf Zählungen

In diesem Beispiel wird die CTE verwendet, um die durchschnittliche Anzahl von Verkaufsaufträgen im Jahr 2018 für alle Vertriebsmitarbeiter zurückzugeben.

Hier ist die Ausgabe:

Code language: SQL (Structured Query Language) (sql)

In diesem Beispiel:

Zuerst haben wir cte_sales als Namen des allgemeinen Tabellenausdrucks verwendet., Wir haben die Spaltenliste des CTE übersprungen, sodass sie von der CTE-Definitionsanweisung abgeleitet ist. In diesem Beispiel enthält es die Spalten staff_id und order_count.

Zweitens verwenden wir die folgende Abfrage, um die Ergebnismenge zu definieren, die den allgemeinen Tabellenausdruck cte_sales. Die Abfrage gibt die Anzahl der Bestellungen im Jahr 2018 durch Vertriebsmitarbeiter zurück.

Drittens verweisen wir in der äußeren Anweisung auf die cte_sales und verwenden die AVG() – Funktion, um den durchschnittlichen Kundenauftrag aller Mitarbeiter abzurufen.,

Code language: SQL (Structured Query Language) (sql)

C) Verwenden mehrerer SQL Server CTE in einem einzigen Abfragebeispiel

Das folgende Beispiel verwendet zwei CTE cte_category_counts und cte_category_sales, um die Anzahl der Produkte und Verkäufe für jede Produktkategorie zurückzugeben. Die äußere Abfrage verbindet zwei CTEs mit der Spalte category_id.,

Hier ist die Ergebnismenge:

In diesem Tutorial haben Sie gelernt, wie Sie in SQL Server gängige Tabellenausdrücke oder CTE verwenden, um komplexe Abfragen auf leicht verständliche Weise zu erstellen.