Verwenden allgemeiner Tabellenausdrücke
Ein allgemeiner Tabellenausdruck (Common Table Expression oder CTE) kann als temporäres Resultset betrachtet werden, das im Ausführungsbereich einer einzigen SELECT-, INSERT-, UPDATE-, DELETE- oder CREATE VIEW-Anweisung definiert wird. Ein allgemeiner Tabellenausdruck ähnelt einer abgeleiteten Tabelle dahingehend, dass er nicht als Objekt gespeichert wird und nur für die Dauer der Abfrage vorhanden ist. Im Gegensatz zu einer abgeleiteten Tabelle kann ein allgemeiner Tabellenausdruck auf sich selbst verweisen, und es können in der gleichen Abfrage mehrere Verweise auf ihn vorhanden sein.
Ein allgemeiner Tabellenausdruck kann für Folgendes verwendet werden:
Erstellen einer rekursiven Abfrage. Weitere Informationen finden Sie unter Rekursive Abfragen mithilfe von allgemeinen Tabellenausdrücken.
Als Ersatz für eine Sicht, wenn die allgemeine Verwendung einer Sicht nicht erforderlich ist; d. h., Sie müssen die Definition nicht in den Metadaten speichern.
Ermöglichen der Gruppierung nach einer Spalte, die aus einer skalaren untergeordneten SELECT-Anweisung oder aus einer Funktion abgeleitet ist, die entweder nicht deterministisch ist oder externen Zugriff besitzt.
Mehrfaches Verweisen auf die sich ergebende Tabelle in der gleichen Anweisung.
Das Verwenden eines allgemeinen Tabellenausdrucks bietet den Vorteil verbesserter Lesbarkeit und einfacherer Verwaltung komplexer Abfragen. Die Abfrage kann in separate, einfache, logische Komponentenblöcke aufgeteilt werden. Diese einfachen Blöcke können zum Erstellen komplexerer allgemeiner Zwischentabellenausdrücke verwendet werden, bis das endgültige Resultset generiert wird.
Allgemeine Tabellenausdrücke können in benutzerdefinierten Routinen, z. B. Funktionen, gespeicherten Prozeduren, Triggern oder Sichten, definiert werden.
Struktur eines allgemeinen Tabellenausdrucks
Ein allgemeiner Tabellenausdruck besteht aus dem Namen eines Ausdrucks, der den allgemeinen Tabellenausdruck darstellt, einer optionalen Spaltenliste und einer Abfrage, die den allgemeinen Tabellenausdruck definiert. Nachdem ein allgemeiner Tabellenausdruck definiert wurde, kann auf ihn wie auf eine Tabelle oder Sicht in einer SELECT-, INSERT-, UPDATE- oder DELETE-Anweisung verwiesen werden. Ein allgemeiner Tabellenausdruck kann auch in einer CREATE VIEW-Anweisung als Bestandteil ihrer definierenden SELECT-Anweisung verwendet werden.
Die grundlegende Syntax für einen allgemeinen Tabellenausdruck lautet folgendermaßen:
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
Die Liste der Spaltennamen ist nur optional, wenn eindeutige Namen für alle sich ergebenden Spalten in der Abfragedefinition zur Verfügung gestellt werden.
Die Anweisung für die Ausführung des allgemeinen Tabellenausdrucks lautet folgendermaßen:
SELECT <column_list>
FROM expression_name;
Beispiel
Das folgende Beispiel zeigt die Komponenten der Struktur eines allgemeinen Tabellenausdrucks: Name des Ausdrucks, Spaltenliste und Abfrage. Der allgemeine Tabellenausdruck Sales_CTE besitzt drei Spalten (SalesPersonID, SalesOrderID, and OrderDate) und ist als die Gesamtanzahl der Kaufaufträge pro Jahr für jeden Vertriebsmitarbeiter definiert.
USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
Im Folgenden wird ein Teil des Resultsets aufgeführt:
SalesPersonID TotalSales SalesYear
------------- ----------- -----------
274 4 2001
274 20 2002
274 14 2003
274 10 2004
275 56 2001
275 139 2002
275 169 2003