MIT common_table_expression (Transact-SQL)

Gilt für:SQL Server Azure SQL-Datenbank Verwaltete Azure SQL-Instanz Azure Synapse Analytics Analytics Platform System (PDW)SQL-Analyseendpunkt in Microsoft Fabric Lagerhaus in Microsoft Fabric SQL-Datenbank 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 eines einzelnen SELECT, , INSERT, UPDATE, oder MERGEDELETEeiner Anweisung definiert. Diese Klausel kann auch als Teil der definierten CREATE VIEW Anweisung in einer 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.

Weitere Informationen finden Sie unter Rekursive Abfragen mit allgemeinen Tabellenausdrücken.

Transact-SQL-Syntaxkonventionen

Syntax

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

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

Arguments

expression_name

Ein gültiger Bezeichner für den allgemeinen Tabellenausdruck. expression_name müssen sich vom Namen eines anderen allgemeinen Tabellenausdrucks unterscheiden, der in derselben WITH <common_table_expression> Klausel definiert ist, aber expression_name kann mit dem Namen einer Basistabelle oder -ansicht identisch sein. Jeder Verweis auf expression_name in der Abfrage verwendet den allgemeinen Tabellenausdruck 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 mit der Anzahl der Spalten im Resultset der CTE_query_definition übereinstimmen. 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, deren Resultset den allgemeinen Tabellenausdruck auffüllt. Die SELECT Anweisung für CTE_query_definition muss die gleichen Anforderungen erfüllen wie beim Erstellen einer Ansicht, außer dass eine CTE keine weitere CTE definieren kann. Weitere Informationen finden Sie im Abschnitt "Hinweise" und "CREATE VIEW".

Wenn mehrere CTE_query_definition definiert sind, müssen die Abfragedefinitionen von einem der folgenden Satzoperatoren verknüpft werden: UNION ALL, , , UNION, EXCEPToder INTERSECT.

Nutzungsrichtlinien

Abfrageergebnisse aus allgemeinen Tabellenausdrücken sind nicht materialisiert. Für jeden äußeren Verweis auf das benannte Resultset muss die definierte Abfrage erneut ausgeführt werden. Bei Abfragen, die mehrere Verweise auf das benannte Resultset erfordern, sollten Sie stattdessen ein temporäres Objekt verwenden.

Sie können eine gespeicherte Prozedur in einem allgemeinen Tabellenausdruck nicht ausführen.

Verwendungsrichtlinien zu rekursiven und nichtcursiven CTEs finden Sie in den folgenden Abschnitten.

Richtlinien für nichtcursive allgemeine Tabellenausdrücke
Richtlinien für rekursive allgemeine Tabellenausdrücke

Richtlinien für nichtcursive allgemeine Tabellenausdrücke

Note

Die folgenden Richtlinien gelten für nicht rekursive allgemeine Tabellenausdrücke. Richtlinien, die für rekursive allgemeine Tabellenausdrücke gelten, finden Sie unter Richtlinien für rekursive allgemeine Tabellenausdrücke.

Einem CTE muss ein einzelner SELECT, INSERT, UPDATE, , , MERGEoder DELETE eine Anweisung folgen, die auf einige oder alle CTE-Spalten 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 beispielsweise eine CTE_query_definition eine Unterabfrage enthält, kann diese Unterabfrage keine geschachtelte WITH Klausel enthalten, die eine andere CTE 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 können nicht in der CTE_query_definition verwendet werden:

ORDER BY (außer wenn eine TOP Klausel OFFSET/FETCH angegeben wird)
INTO
OPTION Klausel mit Abfragehinweisen ¹
FOR BROWSE

¹ Die OPTION Klausel kann nicht in einer CTE-Definition verwendet werden. Sie kann nur in der äußersten SELECT Anweisung verwendet werden.

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 für rekursive allgemeine Tabellenausdrücke

Note

Die folgenden Richtlinien gelten für die Definition eines rekursiven allgemeinen Tabellenausdrucks. Richtlinien, die für nichtcursive CTEs gelten, finden Sie unter Richtlinien für nichtcursive allgemeine Tabellenausdrücke.

Die rekursive CTE-Definition muss mindestens zwei CTE-Abfragedefinitionen, ein Ankerelement und ein rekursives Element enthalten. 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.

Anchor-Member müssen von einem der folgenden Satzoperatoren kombiniert werden: UNION ALL, , UNION, , INTERSECToder EXCEPT. UNION ALL ist der einzige Satzoperator, der zwischen dem letzten Ankerelement und dem ersten rekursiven Element zulässig ist und wenn mehrere rekursive Member kombiniert werden.

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 die CTE-expression_name verweisen.

Die folgenden Elemente sind im CTE_query_definition eines rekursiven Elements nicht zulässig:

SELECT DISTINCT
GROUP BY
PIVOT ¹
HAVING
Skalare Aggregation
TOP
LEFT, RIGHT, OUTER JOIN (INNER JOIN ist erlaubt)
Subqueries
Ein Hinweis, der auf einen rekursiven Verweis auf ein CTE innerhalb eines CTE_query_definition angewendet wird.

¹ Wenn die Datenbankkompatibilitätsebene 110 oder höher ist. Weitere Informationen zu den Features des Datenbankmoduls finden Sie in SQL Server 2016.

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. Um eine Endlosschleife zu verhindern, können Sie die Anzahl der für eine bestimmte Anweisung zulässigen Rekursionsstufen einschränken, indem Sie den MAXRECURSION Hinweis und einen Wert zwischen 0 und 32767 in der OPTION Klausel der INSERT, , UPDATEDELETEoder SELECT Anweisung verwenden. 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.
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. Das CTE ist das select_statement-Argument , das den 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 CTE-Abfrage handelt, werden Index-Spools/Lazy-Spools im Abfrageplan angezeigt und verfügen über das zusätzliche WITH STACK Prädikat. 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 kann nicht im CTE_query_definition verwendet werden, außer wenn eine TOP Klausel angegeben wird.
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, verhalten 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 SELECT dieser Verweis auf CTE eine falsche Spalte verwendet, die in CTE nicht vorhanden ist, wird der sp_prepare Fehler übergeben, ohne den Fehler zu erkennen, aber der Fehler wird stattdessen sp_execute ausgelöst.

Examples

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 verwendet wird, um die Geldbeträge in einem Währungsformat anzuzeigen, wurde in SQL Server 2012 (11.x) eingeführt.

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

Nachdem der Codierungsfehler behoben wurde, ist MAXRECURSION nicht mehr erforderlich. 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
         INNER 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 PerAssemblyQty Wert für alle Teile aktualisiert, die zum Erstellen 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
     INNER 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
         INNER 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;

Feedback

War diese Seite hilfreich?

Last updated on 2025-11-18