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, NumberOfOrders und MaxDate) und ist als die Gesamtanzahl der Kaufaufträge und das aktuellste Bestelldatum in der SalesOrderHeader-Tabelle für jeden Vertriebsmitarbeiter definiert. Wenn die Anweisung ausgeführt wird, wird zweimal auf den allgemeinen Tabellenausdruck verwiesen: zuerst zum Zurückgeben der ausgewählten Spalten für den Vertriebsmitarbeiter und dann nochmals zum Abrufen ähnlicher Einzelheiten für den Vorgesetzten des Vertriebsmitarbeiters. Die Daten für den Vertriebsmitarbeiter und den Vorgesetzten werden in einer Zeile zurückgegeben.
USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS OS
ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM
ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO
Dies ist ein Auszug aus dem Resultset:
EmployeeID NumberOfOrders MaxDate ManagerID NumberOfOrders MaxDate
----------- -------------- ---------- --------- -------------- ----------
268 48 2004-06-01 273 NULL NULL
275 450 2004-06-01 268 48 2004-06-01
276 418 2004-06-01 268 48 2004-06-01
277 473 2004-06-01 268 48 2004-06-01