WITH common_table_expression (Transact-SQL)

Artikel
07/19/2024

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric

Gibt ein temporäres benanntes Resultset an, das als allgemeiner Tabellenausdruck (CTE, Common Table Expression) bezeichnet wird. Dies wird von einer einfachen Abfrage abgeleitet und innerhalb des Ausführungsbereichs einer einzelnen SELECT-, INSERT-, UPDATE-, DELETE- oder MERGE-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 nur 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 Ansicht. Als Ausnahme gilt, dass mit einem allgemeinen Tabellenausdruck kein anderer allgemeiner Tabellenausdruck definiert werden kann. Weitere Informationen finden Sie im Abschnitt „Hinweise“ und unter CREATE VIEW (Transact-SQL).

Wenn CTE_query_definition mehrfach definiert wurde, müssen die Abfragedefinitionen durch einen der folgenden Mengenoperatoren verbunden werden: UNION ALL, UNION, EXCEPT oder INTERSECT.

Richtlinien zum Erstellen und Verwenden allgemeiner Tabellenausdrücke

Die folgenden Richtlinien gelten für nicht rekursive allgemeine Tabellenausdrücke. Informationen zu Richtlinien für rekursive allgemeine Tabellenausdrücke finden Sie weiter unten im Abschnitt Richtlinien zum Definieren und Verwenden rekursiver allgemeiner Tabellenausdrücke.

Auf einen allgemeinen Tabellenausdruck muss eine einzelne 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 angegeben 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 eine „CTE_query_definition“ beispielsweise eine Unterabfrage enthält, darf diese Unterabfrage keine geschachtelte WITH-Klausel enthalten, die einen weiteren allgemeinen Tabellenausdruck definiert.
Weitere Informationen zu geschachtelten CTEs in Microsoft Fabric finden Sie unter "Geschachtelter Common Table Expression (CTE)" in Fabric Data Warehouse (Transact-SQL).For more information on nested CTEs in Microsoft Fabric, see Nested Common Table Expression (CTE) in Fabric Data Warehouse (Transact-SQL).
Die folgenden Klauseln dürfen in CTE_query_definition nicht verwendet werden:
- ORDER BY (Ausnahme: wenn eine TOP-Klausel angegeben ist)
- INTO
- OPTION-Klausel mit Abfragehinweisen
- 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.
Beim Ausführen einer CTE können alle Hinweise, die auf eine CTE verweisen, in Konflikt mit anderen Hinweisen stehen, die beim Zugriff auf die zugrunde liegenden Tabellen durch die CTE ermittelt werden, auf die gleiche Weise wie Hinweise, die in Abfragen auf Ansichten verweisen. Wenn das passiert, gibt die Abfrage einen Fehler zurück.

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 Spaltenanzahl aufweisen.
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 expression_name des allgemeinen Tabellenausdrucks verweisen.
Die folgenden Elemente sind in CTE_query_definition eines rekursiven Elements nicht zulässig:
- SELECT DISTINCT
- GROUP BY
- PIVOT(Wenn die Datenbankkompatibilitätsstufe 110 oder höher ist. Weitere Informationen zu Datenbank-Engine Features finden Sie in SQL Server 2016.)
- HAVING
- Skalare Aggregation
- TOP
- LEFT, RIGHT, OUTER JOIN (INNER JOIN ist erlaubt)
- 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.
Eine falsch zusammengesetzte rekursive CTE kann eine Endlosschleife verursachen. Wenn beispielsweise die Abfragedefinition des rekursiven Elements für übergeordnete und untergeordnete Spalten die gleichen Werte zurückgibt, entsteht eine Endlosschleife. Sie können die Anzahl der für eine bestimmte Anweisung zulässigen Rekursionsebenen einschränken, um eine Endlosschleife zu verhindern. Dazu verwenden Sie den MAXRECURSION-Hinweis und einen Wert zwischen 0 und 32.767 in der OPTION-Klausel der INSERT-, UPDATE-, DELETE- oder SELECT-Anweisung. Somit können Sie die Ausführung der Anweisung steuern, bis das Codeproblem behoben wurde, 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 mithilfe von CTEs definiert werden. Der allgemeine Tabellenausdruck ist das select_statement-Argument, welches 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.
In einem 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 die Menge für die aktuelle Rekursionsebene und nicht auf die Menge 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 weiter unten im Beispiel I.: „Verwenden von Analysefunktionen in einem rekursiven allgemeinen Tabellenausdruck“.

Allgemeine Tabellenausdrücke in Azure Synapse Analytics and Analytics Platform System (PDW)

Die aktuelle Implementierung von CTEs in Azure Synapse Analytics and Analytics Platform System (PDW) verfügt über die folgenden Features und Anforderungen:

Ein allgemeiner Tabellenausdruck kann in einer SELECT-Anweisung angegeben werden.
Ein allgemeiner Tabellenausdruck kann in einer CREATE VIEW-Anweisung angegeben werden.
Ein allgemeiner Tabellenausdruck kann in einer CREATE TABLE AS SELECT-Anweisung (CTAS) angegeben werden.
Ein allgemeiner Tabellenausdruck kann in einer CREATE REMOTE TABLE AS SELECT-Anweisung (CRTAS) angegeben werden.
Ein allgemeiner Tabellenausdruck kann in einer CREATE EXTERNAL TABLE AS SELECT-Anweisung (CETAS) angegeben werden.
Ein allgemeiner Tabellenausdruck kann auf eine Remotetabelle verweisen.
Ein allgemeiner Tabellenausdruck kann auf eine externe Tabelle verweisen.
In einem allgemeinen Tabellenausdruck können mehrere Abfragedefinitionen für allgemeine Tabellenausdrücke definiert werden.
Ein CTE kann von SELECT, , INSERT, UPDATE, oder DELETEMERGE Anweisungen gefolgt werden.
Ein allgemeiner Tabellenausdruck, der Verweise auf sich selbst (ein rekursiver allgemeiner Tabellenausdruck) enthält, wird nicht unterstützt.
Die Angabe mehrerer WITH-Klauseln in einem allgemeinen Tabellenausdruck ist nicht zulässig. Wenn die Abfragedefinition eines allgemeinen Tabellenausdrucks beispielsweise eine Unterabfrage enthält, darf diese Unterabfrage keine geschachtelte WITH-Klausel enthalten, die einen weiteren allgemeinen Tabellenausdruck definiert.
Eine ORDER BY-Klausel darf in der CTE_query_definition nicht verwendet werden. Eine Ausnahme gilt, wenn eine TOP-Klausel angegeben ist.
Wird ein allgemeiner Tabellenausdruck in einer Anweisung verwendet, die zu einem Batch gehört, muss auf die vorangehende Anweisung ein Semikolon folgen.
Wenn sie in von ctEs erstellten Anweisungen verwendet sp_preparewerden, verhält sich CTEs auf die gleiche Weise wie andere SELECT Anweisungen in APS PDW. Wenn CTEs jedoch als Teil von CETAS verwendet werden, der von sp_prepareCETAS vorbereitet wird, kann das Verhalten von SQL Server und anderen APS PDW-Anweisungen aufgrund der Implementierung der Bindung für sp_prepareSQL Server zurückstellen. Wenn in einer SELECT-Anweisung, die auf einen allgemeinen Tabellenausdruck verweist, eine falsche, im allgemeinen Tabellenausdruck nicht vorhandene Spalte verwendet wird, wird sp_prepare ohne Erkennung des Fehlers durchlaufen. Stattdessen wird der Fehler jedoch während sp_execute ausgelöst.

Beispiele

A. Erstellen eines allgemeinen Tabellenausdrucks (CTE, Common Table Expression)

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

-- 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;

B. Verwenden eines allgemeinen Tabellenausdrucks zum Einschränken von Anzahlen und Wiedergeben von Durchschnittswerten

Im folgenden Beispiel wird die durchschnittliche Anzahl der Verkaufsaufträge der Vertriebsmitarbeiter für alle Jahre veranschaulicht.

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;

C. Verwenden mehrerer Definitionen für allgemeine Tabellenausdrücke in einer einzelnen Abfrage

Im folgenden Beispiel wird veranschaulicht, wie mehrere allgemeine Tabellenausdrücke in einer einzelnen Abfrage definiert werden. Ein Komma wird verwendet, um die CTE-Abfragedefinitionen zu trennen. Die FORMAT-Funktion, die zum Anzeigen der Geldbeträge in einem Währungsformat verwendet wird, ist in SQL Server 2012 und höheren Versionen verfügbar.

WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)
AS
-- Define the first CTE query.
(
    SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
       GROUP BY SalesPersonID, YEAR(OrderDate)

)
, -- Use a comma to separate multiple CTE definitions.

-- Define the second CTE query, which returns sales quota data by year for each sales person.
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)
AS
(
       SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear
       FROM Sales.SalesPersonQuotaHistory
       GROUP BY BusinessEntityID, YEAR(QuotaDate)
)

-- Define the outer query by referencing columns from both CTEs.
SELECT SalesPersonID
  , SalesYear
  , FORMAT(TotalSales,'C','en-us') AS TotalSales
  , SalesQuotaYear
  , FORMAT (SalesQuota,'C','en-us') AS SalesQuota
  , FORMAT (TotalSales -SalesQuota, 'C','en-us') AS Amt_Above_or_Below_Quota
FROM Sales_CTE
JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID
                    AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear
ORDER BY SalesPersonID, SalesYear;

Dies ist ein Auszug aus dem Resultset.

SalesPersonID SalesYear   TotalSales    SalesQuotaYear SalesQuota  Amt_Above_or_Below_Quota
------------- ---------   -----------   -------------- ---------- ----------------------------------
274           2005        $32,567.92    2005           $35,000.00  ($2,432.08)
274           2006        $406,620.07   2006           $455,000.00 ($48,379.93)
274           2007        $515,622.91   2007           $544,000.00 ($28,377.09)
274           2008        $281,123.55   2008           $271,000.00  $10,123.55

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

Im folgenden Beispiel werden Vorgesetzte in einer Hierarchieliste sowie die Mitarbeiter angezeigt, die diesen unterstellt sind. In diesem Beispiel wird zunächst die dbo.MyEmployees-Tabelle erstellt und aufgefüllt.

-- 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 SMALLINT NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC),
CONSTRAINT FK_MyEmployees_ManagerID_EmployeeID FOREIGN KEY (ManagerID) REFERENCES dbo.MyEmployees (EmployeeID)
);
-- 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);

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;

Verwenden eines rekursiven allgemeinen Tabellenausdrucks, um zwei Rekursionsebenen anzuzeigen

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.

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 ;

Verwenden eines rekursiven allgemeinen Tabellenausdrucks, um eine Hierarchieliste anzuzeigen

Im folgenden Beispiel werden die Namen des Vorgesetzten und der Mitarbeiter sowie deren Titel hinzugefügt. Die Hierarchieebenen von Vorgesetzten und Mitarbeitern werden zusätzlich hervorgehoben, indem die einzelnen Ebenen jeweils eingerückt werden.

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;

Verwenden von MAXRECURSION zum Abbrechen einer Anweisung

Mit MAXRECURSION kann verhindert werden, dass ein rekursiver allgemeiner Tabellenausdruck, der fehlerhaft formuliert ist, 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.

--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);

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

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;

E. 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 montieren.

USE AdventureWorks2022;
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;

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

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

USE AdventureWorks2022;
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;

H. 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 mit Werten aufgefüllt, 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

I. 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.

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

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.

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

J. Verwenden eines allgemeinen Tabellenausdrucks in einer CTAS-Anweisung

Im folgenden Beispiel wird eine neue Tabelle mit der Gesamtanzahl der Verkäufe pro Jahr für jeden Vertriebsmitarbeiter von Adventure Works Cycles angezeigt.

USE AdventureWorks2022;
GO
CREATE TABLE SalesOrdersPerYear
WITH
(
    DISTRIBUTION = HASH(SalesPersonID)
)
AS
    -- 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

K. Verwenden eines allgemeinen Tabellenausdrucks in einer CETAS-Anweisung

Im folgenden Beispiel wird eine neue externe Tabelle mit der Gesamtanzahl der Verkäufe pro Jahr für jeden Vertriebsmitarbeiter von Adventure Works Cycles angezeigt.

USE AdventureWorks2022;
GO
CREATE EXTERNAL TABLE SalesOrdersPerYear
WITH
(
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:5000/files/Customer',
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|' )
)
AS
    -- 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

L. Verwenden mehrerer, durch Kommas getrennter allgemeiner Tabellenausdrücke in einer Anweisung

Im folgenden Beispiel wird veranschaulicht, wie zwei allgemeine Tabellenausdrücke in eine einzige Anweisung eingeschlossen werden. Die allgemeinen Tabellenausdrücke dürfen nicht verschachtelt werden (keine Rekursion).

WITH
CountDate (TotalCount, TableName) AS
    (
     SELECT COUNT(datekey), 'DimDate' FROM DimDate
    ) ,
CountCustomer (TotalAvg, TableName) AS
    (
     SELECT COUNT(CustomerKey), 'DimCustomer' FROM DimCustomer
    )
SELECT TableName, TotalCount FROM CountDate
UNION ALL
SELECT TableName, TotalAvg FROM CountCustomer;

Freigeben über

WITH common_table_expression (Transact-SQL)

Syntax

Argumente

expression_name

column_name

CTE_query_definition

Richtlinien zum Erstellen und Verwenden allgemeiner Tabellenausdrücke

Richtlinien zum Definieren und Verwenden rekursiver allgemeiner Tabellenausdrücke

Allgemeine Tabellenausdrücke in Azure Synapse Analytics and Analytics Platform System (PDW)

Beispiele

A. Erstellen eines allgemeinen Tabellenausdrucks (CTE, Common Table Expression)

B. Verwenden eines allgemeinen Tabellenausdrucks zum Einschränken von Anzahlen und Wiedergeben von Durchschnittswerten

C. Verwenden mehrerer Definitionen für allgemeine Tabellenausdrücke in einer einzelnen Abfrage

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

Verwenden eines rekursiven allgemeinen Tabellenausdrucks, um zwei Rekursionsebenen anzuzeigen

Verwenden eines rekursiven allgemeinen Tabellenausdrucks, um eine Hierarchieliste anzuzeigen

Verwenden von MAXRECURSION zum Abbrechen einer Anweisung

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

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

H. Verwenden mehrerer Ankerelemente und rekursiver Elemente

I. Verwenden von Analysefunktionen in einem rekursiven allgemeinen Tabellenausdruck

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

J. Verwenden eines allgemeinen Tabellenausdrucks in einer CTAS-Anweisung

K. Verwenden eines allgemeinen Tabellenausdrucks in einer CETAS-Anweisung

L. Verwenden mehrerer, durch Kommas getrennter allgemeiner Tabellenausdrücke in einer Anweisung

Feedback

Zusätzliche Ressourcen

Freigeben über

WITH common_table_expression (Transact-SQL)

Syntax

Argumente

expression_name

column_name

CTE_query_definition

Richtlinien zum Erstellen und Verwenden allgemeiner Tabellenausdrücke

Richtlinien zum Definieren und Verwenden rekursiver allgemeiner Tabellenausdrücke

Allgemeine Tabellenausdrücke in Azure Synapse Analytics and Analytics Platform System (PDW)

Beispiele

A. Erstellen eines allgemeinen Tabellenausdrucks (CTE, Common Table Expression)

B. Verwenden eines allgemeinen Tabellenausdrucks zum Einschränken von Anzahlen und Wiedergeben von Durchschnittswerten

C. Verwenden mehrerer Definitionen für allgemeine Tabellenausdrücke in einer einzelnen Abfrage

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

Verwenden eines rekursiven allgemeinen Tabellenausdrucks, um zwei Rekursionsebenen anzuzeigen

Verwenden eines rekursiven allgemeinen Tabellenausdrucks, um eine Hierarchieliste anzuzeigen

Verwenden von MAXRECURSION zum Abbrechen einer Anweisung

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

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

H. Verwenden mehrerer Ankerelemente und rekursiver Elemente

I. Verwenden von Analysefunktionen in einem rekursiven allgemeinen Tabellenausdruck

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

J. Verwenden eines allgemeinen Tabellenausdrucks in einer CTAS-Anweisung

K. Verwenden eines allgemeinen Tabellenausdrucks in einer CETAS-Anweisung

L. Verwenden mehrerer, durch Kommas getrennter allgemeiner Tabellenausdrücke in einer Anweisung

Zugehöriger Inhalt

Feedback

Zusätzliche Ressourcen