WITH common_table_expression (Transact-SQL)

Die folgenden Richtlinien gelten für nicht rekursive allgemeine Tabellenausdrücke. Informationen zu Richtlinien, die für rekursive allgemeine Tabellenausdrücke gelten, finden Sie im entsprechenden Abschnitt weiter unten.

• Auf einen allgemeinen Tabellenausdruck muss eine einzelne Anweisung SELECT, INSERT, UPDATE, MERGE oder DELETE folgen, die auf einige 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 verknüpft werden: UNION ALL, UNION, INTERSECT oder EXCEPT.

• Ein allgemeiner Tabellenausdruck kann in der gleichen 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 auf die vorangehende Anweisung ein Semikolon folgen.

• 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. In diesem Fall gibt die Abfrage einen Fehler zurück. Weitere Informationen finden Sie unter Sichtauflösung.

• Wenn ein allgemeiner Tabellenausdruck das Ziel einer UPDATE-Anweisung ist, müssen alle Verweise auf den allgemeinen Tabellenausdruck in der Anweisung übereinstimmen. Wenn dem allgemeinen Tabellenausdruck z. B. ein Alias in der FROM-Klausel zugewiesen wird, muss der Alias für alle weiteren Verweise auf den allgemeinen Tabellenausdruck verwendet werden. Mehrdeutige Verweise auf allgemeine Tabellenausdrücke erzeugen möglicherweise unerwartetes Verknüpfungsverhalten und unbeabsichtigte Abfrageergebnisse. Weitere Informationen finden Sie unter UPDATE (Transact-SQL).

Die folgenden Richtlinien gelten für die Definition rekursiver allgemeiner Tabellenausdrücke:

• 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 Definition des ersten 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 verknüpft 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 Verknüpfung 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 auf einen allgemeinen Tabellenausdruck innerhalb von CTE_query_definition angewendet wird.

Die folgenden Richtlinien gelten für die Verwendung rekursiver allgemeiner Tabellenausdrücke:

• 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 die Abfragedefinition des rekursiven Elements für übergeordnete und untergeordnete Spalten beispielsweise 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 den MAXRECURSION-Hinweis und einen Wert zwischen 0 und 32.767 in der OPTION-Klausel der Anweisung INSERT, UPDATE, MERGE, DELETE oder SELECT. Dadurch können Sie die Ausführung der Anweisung steuern, bis Sie das Codeproblem behoben haben, das die Schleife verursacht. Der serverweite Standardwert ist 100. Wenn 0 angegeben wird, wird keine Beschränkung angewendet. Pro Anweisung kann nur ein MAXRECURSION-Wert angegeben werden. Weitere Informationen finden Sie unter Abfragehinweise (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, durch das das Resultset des Cursors definiert wird. Für rekursive allgemeine Tabellenausdrücke sind nur schnelle Vorwärtscursor und statische Cursor (Snapshot) 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 verwiesen werden, die sich auf Remoteservern befinden. Wenn im rekursiven Element des allgemeinen Tabellenausdrucks auf den Remoteserver verwiesen wird, wird für jede Remotetabelle ein Spoolvorgang erstellt, sodass auf die Tabellen wiederholt lokal zugegriffen werden kann. Wenn es sich um eine Abfrage für einen allgemeinen Tabellenausdruck handelt, wird im Abfrageplan Index Spool/Lazy Spool mit dem zusätzlichen WITH STACK-Prädikat angezeigt. Dies ist eine Möglichkeit, um eine ordnungsgemäße Rekursion zu gewährleisten.

• SQL Server 2008 lässt keine Analyse- und Aggregatfunktionen im rekursiven Teil des allgemeinen Tabellenausdrucks zu.

Im folgenden Beispiel wird die Anzahl der Mitarbeiter gezeigt, die den einzelnen Vorgesetzten bei Adventure Works Cycles direkt 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

Im folgenden Beispiel wird die durchschnittliche Anzahl der Mitarbeiter gezeigt, die den einzelnen Vorgesetzten 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

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

Im folgenden Beispiel wird die Hierarchieliste der Vorgesetzten 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

Im folgenden Beispiel werden Vorgesetzte sowie die Mitarbeiter angezeigt, die diesen 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

Das folgende Beispiel baut auf Beispiel C auf, indem die Namen der Vorgesetzten und Mitarbeiter mit den entsprechenden Titeln hinzugefügt werden. Die Hierarchieebenen von Vorgesetzten 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

MAXRECURSION kann verwendet werden, um zu verhindern, dass ein fehlerhaft formatierter rekursiver 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

Sobald 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

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

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

Im folgenden Beispiel wird der VacationHours-Wert für alle Mitarbeiter, die ManagerID 12 direkt oder indirekt unterstellt sind, um 25 Prozent erhöht. 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, sind 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

Im folgenden Beispiel werden mehrere Ankerelemente und rekursive Elemente verwendet, um alle Vorfahren einer bestimmten Person zurückzugeben. Es wird eine Tabelle mit eingefügten Werten erstellt, um den Familienstammbaum anzulegen, 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