WITH common_table_expression (Transact-SQL)

Gibt ein temporäres Resultset an, das als allgemeiner Tabellenausdruck (CTE, Common Table Expression) bekannt ist. Dieser wird von einer einfachen Abfrage abgeleitet und innerhalb des Ausführungsbereichs einer einzigen SELECT-, INSERT-, UPDATE- oder DELETE-Anweisung definiert. Diese Klausel kann auch in einer CREATE VIEW-Anweisung als Teil der definierenden SELECT-Anweisung verwendet werden. Ein allgemeiner Tabellenausdruck kann auch Verweise auf sich selbst enthalten. In diesem Fall handelt es sich um einen rekursiven allgemeinen Tabellenausdruck.

Transact-SQL-Syntaxkonventionen

Syntax

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

Argumente

• expression_name
  Ein gültiger Bezeichner für den allgemeinen Tabellenausdruck. expression_name darf nicht mit dem Namen eines anderen allgemeinen Tabellenausdrucks identisch sein, der in derselben WITH <common_table_expression>-Klausel definiert ist. expression_name kann jedoch mit dem Namen einer Basistabelle oder Basissicht identisch sein. Bei jedem Verweis auf expression_name in der Abfrage wird der allgemeine Tabellenausdruck verwendet und nicht das Basisobjekt.

• column_name
  Gibt einen Spaltennamen im allgemeinen Tabellenausdruck an. Innerhalb der Definition eines allgemeinen Tabellenausdrucks sind doppelte Namen nicht zulässig. Die Anzahl der angegebenen Spaltennamen muss der Anzahl der Spalten im Resultset von CTE_query_definition entsprechen. Die Liste der Spaltennamen ist optional, wenn in der Abfragedefinition für alle Spalten verschiedene Namen angegeben werden.

• CTE_query_definition
  Gibt eine SELECT-Anweisung an, mit deren Resultset der allgemeine Tabellenausdruck aufgefüllt wird. Die SELECT-Anweisung für CTE_query_definition muss die gleichen Anforderungen erfüllen wie für das Erstellen einer Sicht erforderlich sind. Als Ausnahme gilt, dass ein allgemeiner Tabellenausdruck keinen anderen allgemeinen Tabellenausdruck definieren kann. Weitere Informationen finden Sie im Abschnitt zu den Hinweisen und unter CREATE VIEW (Transact-SQL).

  Wenn CTE_query_definition mehrfach definiert ist, müssen die Abfragedefinitionen durch einen der folgenden Mengenoperatoren verbunden werden: UNION ALL, UNION, EXCEPT oder INTERSECT. Weitere Informationen zum Verwenden von Abfragedefinitionen für rekursive Tabellenausdrücke finden Sie im folgenden Abschnitt "Hinweise" und unter Rekursive Abfragen mithilfe von allgemeinen Tabellenausdrücken.

Hinweise

Richtlinien für das Erstellen und Verwenden von allgemeinen Tabellenausdrücken (CTEs)

Die folgenden Richtlinien gelten für nicht rekursive allgemeine Tabellenausdrücke. Richtlinien zu rekursiven allgemeinen Tabellenausdrücken finden Sie im entsprechenden Abschnitt weiter unten.

• Auf einen allgemeinen Tabellenausdruck muss eine einzige SELECT-, INSERT-, UPDATE- oder DELETE-Anweisung folgen, die auf eine oder alle Spalten mit einem allgemeinen Tabellenausdruck verweist. Ein allgemeiner Tabellenausdruck kann auch in einer CREATE VIEW-Anweisung als Teil der definierenden SELECT-Anweisung der Sicht verwendet werden.
• In einem nicht rekursiven allgemeinen Tabellenausdruck können mehrere Abfragedefinitionen für allgemeine Tabellenausdrücke definiert werden. Die Definitionen müssen durch einen der folgenden Mengenoperatoren verbunden werden: UNION ALL, UNION, INTERSECT oder EXCEPT.
• Ein allgemeiner Tabellenausdruck kann in einer WITH-Klausel auf sich selbst und auf vorher definierte allgemeine Tabellenausdrücke verweisen. Ein Vorwärtsverweis ist nicht zulässig.
• Die Angabe mehrerer WITH-Klauseln in einem allgemeinen Tabellenausdruck ist nicht zulässig. Wenn beispielsweise CTE_query_definition eine Unterabfrage umfasst, dann darf diese Unterabfrage keine geschachtelte WITH-Klausel enthalten, über die ein weiterer allgemeiner Tabellenausdruck definiert wird.
• Die folgenden Klauseln dürfen in CTE_query_definition nicht verwendet werden:
  • COMPUTE oder COMPUTE BY
  • ORDER BY (Ausnahme: wenn eine TOP-Klausel angegeben ist)
  • INTO
  • OPTION-Klausel mit Abfragehinweisen
  • FOR XML
  • FOR BROWSE
• Wird ein allgemeiner Tabellenausdruck in einer Anweisung verwendet, die zu einem Batch gehört, muss nach der Anweisung, die davor steht, ein Semikolon gesetzt werden.
• Eine Abfrage, die auf einen allgemeinen Tabellenausdruck verweist, kann zur Definition eines Cursors verwendet werden.
• In einem allgemeinen Tabellenausdruck kann auf Tabellen auf Remoteservern verwiesen werden.
• Wenn ein allgemeiner Tabellenausdruck ausgeführt wird, können alle Hinweise, die auf einen allgemeinen Tabellenausdruck verweisen, Konflikte mit anderen Hinweisen verursachen, die auftreten, wenn der allgemeine Tabellenausdruck auf die zugrunde liegenden Tabellen zugreift. Dies gilt auch für Hinweise, die auf Sichten in Abfragen verweisen. Wenn das passiert, gibt die Abfrage einen Fehler zurück. Weitere Informationen finden Sie unter Sichtauflösung.

Richtlinien für das Definieren und Verwenden von rekursiven allgemeinen Tabellenausdrücken

Die folgenden Richtlinien gelten für das Definieren eines rekursiven allgemeinen Tabellenausdrucks:

• Die Definition des rekursiven allgemeinen Tabellenausdrucks muss mindestens zwei Abfragedefinitionen für allgemeine Tabellenausdrücke enthalten, und zwar ein Ankerelement und ein rekursives Element. Mehrere Ankerelemente und rekursive Elemente können definiert werden. Jedoch müssen alle Ankerelement-Abfragedefinitionen vor die erste Definition eines rekursiven Elements gesetzt werden. Alle Abfragedefinitionen für allgemeine Tabellenausdrücke**sind Ankerelemente, es sei denn, sie verweisen auf den allgemeinen Tabellenausdruck selbst.
• Ankerelemente müssen durch einen der folgenden Mengenoperatoren verbunden werden: UNION ALL, UNION, INTERSECT oder EXCEPT. UNION ALL ist der einzige Mengenoperator, der zwischen dem letzten Ankerelement und dem ersten rekursiven Element sowie bei der Verbindung mehrerer rekursiver Elemente zulässig ist.
• Ankerelemente und rekursive Elemente müssen die gleiche Anzahl Spalten haben.
• Der Datentyp einer Spalte im rekursiven Element und der Datentyp der entsprechenden Spalte im Ankerelement müssen übereinstimmen.
• Die FROM-Klausel eines rekursiven Elements darf nur einmal auf den allgemeinen Tabellenausdruck expression_name verweisen.
• Die folgenden Elemente sind in CTE_query_definition eines rekursiven Elements nicht zulässig:
  • SELECT DISTINCT
  • GROUP BY
  • HAVING
  • Skalare Aggregation
  • TOP
  • LEFT, RIGHT, OUTER JOIN (INNER JOIN ist zulässig)
  • Unterabfragen
  • Ein Hinweis, der auf einen rekursiven Verweis für einen allgemeinen Tabellenausdruck innerhalb von CTE_query_definition angewendet wird.

Die folgenden Richtlinien gelten für die Verwendung eines rekursiven allgemeinen Tabellenausdrucks:

• Alle Spalten, die vom rekursiven allgemeinen Tabellenausdruck zurückgegeben werden, lassen Null zu, unabhängig davon, ob die Spalten, die von den beteiligten SELECT-Anweisungen zurückgegeben werden, Null zulassen.
• Ist ein rekursiver allgemeiner Tabellenausdruck falsch zusammengesetzt, kann dies zu einer Endlosschleife führen. Wenn beispielsweise die Abfragedefinition des rekursiven Elements für übergeordnete und untergeordnete Spalten die gleichen Werte zurückgibt, entsteht eine Endlosschleife. Um eine Endlosschleife zu verhindern, können Sie die Anzahl der für eine bestimmte Anweisung zulässigen Rekursionsebenen einschränken. Dazu verwenden Sie MAXRECURSION und einen Wert zwischen 0 und 32.767 in der OPTION-Klausel der Anweisung INSERT, UPDATE, DELETE oder SELECT. Somit können die Ausführung der Anweisung steuern, bis Sie das Codeproblem behoben haben, das die Schleife verursacht. Der serverübergreifende Standardwert ist 100. Wenn 0 angegeben ist, wird keine Beschränkung angewendet. Pro Anweisung kann nur ein Wert für MAXRECURSION angegeben werden. Weitere Informationen finden Sie unter Abfragehinweis (Transact-SQL).
• Eine Sicht, die einen rekursiven allgemeinen Tabellenausdruck enthält, kann nicht zum Aktualisieren von Daten verwendet werden.
• Cursor können für Abfragen definiert werden, die allgemeine Tabellenausdrücke verwenden. Der allgemeine Tabellenausdruck ist das select_statement-Argument, das das Resultset des Cursors definiert. Für rekursive allgemeine Tabellenausdrücke sind nur schnelle Vorwärtscursor und statische (Snapshot) Cursor zulässig. Wird in einem rekursiven allgemeinen Tabellenausdruck ein anderer Cursortyp angegeben, wird der Cursortyp in einen statischen Typ konvertiert.
• Im allgemeinen Tabellenausdruck kann auf Tabellen auf Remoteservern verwiesen werden. Wenn im rekursiven Element des allgemeinen Tabellenausdrucks auf den Remoteserver verwiesen wird, wird für jede Remotetabelle ein Spool erstellt, sodass auf die Tabellen wiederholt lokal zugegriffen werden kann.

Beispiele

A. Erstellen eines einfachen allgemeinen Tabellenausdrucks

Im folgenden Beispiel wird die Anzahl Mitarbeiter gezeigt, die bei Adventure Works Cycles direkt den einzelnen Managern unterstellt sind.

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) 
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports 
FROM DirReps 
ORDER BY ManagerID;
GO

B. Verwenden eines allgemeinen Tabellenausdrucks zum Einschränken von Zählern und für die Wiedergabe von Durchschnittswerten

Im folgenden Beispiel wird die durchschnittliche Anzahl Mitarbeiter gezeigt, die direkt den einzelnen Managern unterstellt sind.

WITH DirReps (Manager, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
) 
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps 
WHERE DirectReports>= 2 ;
GO

C. Mehrfaches Verweisen auf einen allgemeinen Tabellenausdruck

Im folgenden Beispiel wird in der Tabelle SalesOrderHeader für die einzelnen Verkäufer die Gesamtanzahl von Bestellungen gezeigt sowie das Datum der jeweils letzten Bestellung. In der ausgeführten Anweisung wird zwei Mal auf den allgemeinen Tabellenausdruck verwiesen: einmal, um die ausgewählten Spalten für den Verkäufer zurückzugeben und noch einmal, um ähnliche Detailinformationen für den Manager des Verkäufers abzurufen. Die Daten für den Verkäufer und den Manager 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

D. Verwenden eines rekursiven allgemeinen Tabellenausdrucks, um mehrere Rekursionsebenen anzuzeigen

Im folgenden Beispiel wird die Hierarchieliste der Manager angezeigt sowie die Mitarbeiter, die diesen unterstellt sind.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;
GO

E. Verwenden eines rekursiven allgemeinen Tabellenausdrucks, um zwei Rekursionsebenen anzuzeigen

Im folgenden Beispiel werden Manager sowie die Mitarbeiter angezeigt, die diesen direkt unterstellt sind. Die Anzahl der zurückgegebenen Ebenen wird auf zwei Ebenen eingeschränkt.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 ;
GO

F. Verwenden eines rekursiven allgemeinen Tabellenausdrucks, um eine Hierarchieliste anzuzeigen

Das folgende Beispiel baut auf Beispiel C auf, indem die Namen der Manager und Mitarbeiter und deren Titel hinzugefügt werden. Die Hierarchieebenen von Managern und Mitarbeitern werden zusätzlich hervorgehoben, indem die einzelnen Ebenen jeweils eingerückt werden.

USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

G. Verwenden von MAXRECURSION zum Abbrechen einer Anweisung

MAXRECURSION kann verwendet werden, um zu verhindern, dass ein fehlerhaft formatierter allgemeiner Tabellenausdruck in eine Endlosschleife gerät. Im folgenden Beispiel wird absichtlich eine Endlosschleife erstellt. Außerdem wird MAXRECURSION verwendet, um die Anzahl der Rekursionsebenen auf zwei zu beschränken.

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

Wenn der Fehler im Code behoben wurde, wird MAXRECURSION nicht mehr benötigt. Das folgende Beispiel zeigt den korrigierten Code.

USE AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

H. Verwenden eines allgemeinen Tabellenausdrucks, um eine rekursive Beziehung in einer SELECT-Anweisung selektiv zu durchlaufen

Im folgenden Beispiel wird die Hierarchie von Produktgruppen und Komponenten gezeigt, die erforderlich sind, um das Fahrrad für ProductAssemblyID = 800 zu erstellen.

USE AdventureWorks;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel 
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

I. Verwenden eines rekursiven allgemeinen Tabellenausdrucks in einer UPDATE-Anweisung

Im folgenden Beispiel wird der Wert für VacationHours für alle Mitarbeiter um 25 Prozent erhöht, die direkt oder indirekt ManagerID 12 unterstellt sind. Der allgemeine Tabellenausdruck gibt eine Hierarchieliste aller Mitarbeiter zurück, die ManagerID 12 direkt unterstellt sind sowie der Mitarbeiter, die sich jeweils eine Hierarchiestufe darunter befinden, usw. Nur die Zeilen, die vom allgemeinen Tabellenausdruck zurückgegeben werden, werden verändert.

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

J. Verwenden mehrerer Ankerelemente und rekursiver Elemente

Im folgenden Beispiel werden mehrere Ankerelemente und rekursiven Elemente verwendet, um alle Vorfahren einer bestimmten Person zurückzugeben. Eine Tabelle wird erstellt und in diese werden Werte eingefügt, um den Familienstammbaum zu erstellen, der vom rekursiven allgemeinen Tabellenausdruck zurückgegeben wird.

-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother 
    FROM Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO

Siehe auch

Verweis

CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL)
EXCEPT und INTERSECT (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
UPDATE (Transact-SQL)

Andere Ressourcen

Rekursive Abfragen mithilfe von allgemeinen Tabellenausdrücken
Verwenden allgemeiner Tabellenausdrücke

Hilfe und Informationen

Informationsquellen für SQL Server 2005