WITH common_table_expression (Transact-SQL)

Gibt ein temporäres benanntes 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 einzelnen Anweisung SELECT, INSERT, UPDATE, MERGE oder DELETE 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 zum Erstellen und Verwenden allgemeiner Tabellenausdrücke

Die folgenden Richtlinien gelten für nicht rekursive allgemeine Tabellenausdrücke. Informationen zu Richtlinien, die für rekursive allgemeine Tabellenausdrücke gelten, finden Sie unter "Richtlinien zum Definieren und Verwenden rekursiver allgemeiner Tabellenausdrücke" 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 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 dieser Fall eintritt, 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 Joinverhalten und unbeabsichtigte Abfrageergebnisse. Weitere Informationen finden Sie unter UPDATE (Transact-SQL).

Richtlinien zum Definieren und Verwenden rekursiver allgemeiner Tabellenausdrücke

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 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 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 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 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 Wert für MAXRECURSION 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, das das Resultset des Cursors definiert. Für rekursive allgemeine Tabellenausdrücke sind nur schnelle Vorwärtscursor und statische (Momentaufnahme) 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 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.

• Analyse- und Aggregatfunktionen im rekursiven Teil des allgemeinen Tabellenausdrucks werden auf das Set für die aktuelle Rekursionsebene und nicht auf das Set für den allgemeinen Tabellenausdruck angewendet. Funktionen wie ROW_NUMBER werden nur für die von der aktuellen Rekursionsebene übergebene Teilmenge von Daten und nicht für die an den rekursiven Teil des allgemeinen Tabellenausdrucks übergebene gesamte Datenmenge ausgeführt. Weitere Informationen finden Sie unter K. Using analytical functions in a recursive CTE.

Beispiele

A. Erstellen eines einfachen allgemeinen Tabellenausdrucks

Im folgenden Beispiel wird die gesamte Anzahl der Aufträge pro Jahr für jeden Vertriebsmitarbeiter unter Adventure Works Cycles angezeigt.

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

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 der Aufträge für alle Jahre angezeigt, in denen der Vertriebsmitarbeiter aktiv war.

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
    SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;
GO

C. 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. Das Beispiel beginnt mit dem Erstellen und Ausfüllen der dbo.MyEmployees-Tabelle.

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
    EmployeeID smallint NOT NULL,
    FirstName nvarchar(30)  NOT NULL,
    LastName  nvarchar(40) NOT NULL,
    Title nvarchar(50) NOT NULL,
    DeptID smallint NOT NULL,
    ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

USE AdventureWorks2008R2;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
ORDER BY ManagerID;
GO

D. 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 AdventureWorks2008R2;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
WHERE EmployeeLevel <= 2 ;
GO

E. 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 AdventureWorks2008R2;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), e.FirstName + ' ' + e.LastName)
    FROM dbo.MyEmployees AS e
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('|    ' , EmployeeLevel) +
        e.FirstName + ' ' + e.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '|    ' + FirstName + ' ' + 
                 LastName)
    FROM dbo.MyEmployees AS e
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

F. 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 AdventureWorks2008R2;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.MyEmployees
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  dbo.MyEmployees 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 AdventureWorks2008R2;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.MyEmployees
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM dbo.MyEmployees AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

G. 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 AdventureWorks2008R2;
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

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

Im folgenden Beispiel wird der PerAssemblyQty-Wert für alle Teile aktualisiert, die zur Fertigung des Produkts "Road-550-W Yellow, 44" (ProductAssemblyID800) verwendet werden. Der allgemeine Tabellenausdruck gibt eine hierarchische Liste mit Teilen zurück, die zum Erstellen der ProductAssemblyID 800 direkt verwendet werden, sowie mit den Komponenten, die zum Erstellen dieser Teile verwendet werden, usw. Nur die Zeilen, die vom allgemeinen Tabellenausdruck zurückgegeben werden, werden verändert.

USE AdventureWorks2008R2;
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
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0; 

I. Verwenden mehrerer Ankerelemente und rekursiver Elemente

Im folgenden Beispiel werden mehrere Ankerelemente und rekursive 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('dbo.Person','U') IS NOT NULL DROP TABLE dbo.Person;
GO
CREATE TABLE dbo.Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT dbo.Person 
VALUES(1, 'Sue', NULL, NULL)
      ,(2, 'Ed', NULL, NULL)
      ,(3, 'Emma', 1, 2)
      ,(4, 'Jack', 1, 2)
      ,(5, 'Jane', NULL, NULL)
      ,(6, 'Bonnie', 5, 4)
      ,(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 dbo.Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM dbo.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, dbo.Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, dbo.Person
WHERE Generation.ID = Person.ID;
GO

J. Verwenden von Analysefunktionen in einem rekursiven allgemeinen Tabellenausdruck

Im folgenden Beispiel wird ein Fehler gezeigt, der beim Verwenden einer Analyse- oder Aggregatfunktion im rekursiven Teil eines allgemeinen Tabellenausdrucks auftreten kann.

DECLARE @t1 TABLE (itmID int, itmIDComp int);
INSERT @t1 VALUES (1,10), (2,10); 

DECLARE @t2 TABLE (itmID int, itmIDComp int); 
INSERT @t2 VALUES (3,10), (4,10); 

WITH vw AS
 (
    SELECT itmIDComp, itmID
    FROM @t1

    UNION ALL

    SELECT itmIDComp, itmID
    FROM @t2
) 
,r AS
 (
    SELECT t.itmID AS itmIDComp
           , NULL AS itmID
           ,CAST(0 AS bigint) AS N
           ,1 AS Lvl
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t (itmID) 

UNION ALL

SELECT t.itmIDComp
    , t.itmID
    , ROW_NUMBER() OVER(PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N
    , Lvl + 1
FROM r 
    JOIN vw AS t ON t.itmID = r.itmIDComp
) ;

SELECT Lvl, N FROM r

Die folgenden Ergebnisse sind die erwarteten Ergebnisse für die Abfrage.

Lvl  N
1    0
1    0
1    0
1    0
2    4
2    3
2    2
2    1

Die folgenden Ergebnisse sind die tatsächlichen Ergebnisse für die Abfrage.

Lvl  N
1    0
1    0
1    0
1    0
2    1
2    1
2    1
2    1

N gibt 1 für jede Übergabe des rekursiven Teils des allgemeinen Tabellenausdrucks zurück, da nur die Teilmenge der Daten für diese Rekursionsebene an ROWNUMBER übergeben wird. Für jede Iteration des rekursiven Teils der Abfrage wird nur eine Zeile an ROWNUMBER übergeben.

Siehe auch

Verweis

CREATE VIEW (Transact-SQL)

DELETE (Transact-SQL)

EXCEPT und INTERSECT (Transact-SQL)

INSERT (Transact-SQL)

SELECT (Transact-SQL)

UPDATE (Transact-SQL)

Konzepte

Rekursive Abfragen mithilfe von allgemeinen Tabellenausdrücken

Verwenden allgemeiner Tabellenausdrücke