WITH common_table_expression (Transact-SQL)WITH common_table_expression (Transact-SQL)

GILT FÜR: jaSQL Server (ab 2008) jaAzure SQL-DatenbankjaAzure SQL Data Warehouse jaParallel Data WarehouseAPPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Gibt ein temporäres benanntes Resultset an, das als allgemeiner Tabellenausdruck (CTE, Common Table Expression) bezeichnet wird.Specifies a temporary named result set, known as a common table expression (CTE). Dieser wird von einer einfachen Abfrage abgeleitet und innerhalb des Ausführungsbereichs einer einzelnen SELECT-, INSERT-, UPDATE- oder DELETE-Anweisung definiert.This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. Diese Klausel kann auch in einer CREATE VIEW-Anweisung als Teil der definierenden SELECT-Anweisung verwendet werden.This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. Ein allgemeiner Tabellenausdruck kann auch Verweise auf sich selbst enthalten.A common table expression can include references to itself. In diesem Fall handelt es sich um einen rekursiven allgemeinen Tabellenausdruck.This is referred to as a recursive common table expression.

Themenlinksymbol Transact-SQL-SyntaxkonventionenTopic link icon Transact-SQL Syntax Conventions

SyntaxSyntax

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

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

ArgumenteArguments

expression_nameexpression_name
Ein gültiger Bezeichner für den allgemeinen Tabellenausdruck.Is a valid identifier for the common table expression. 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.expression_name must be different from the name of any other common table expression defined in the same WITH <common_table_expression> clause, but expression_name can be the same as the name of a base table or view. Bei jedem Verweis auf expression_name in der Abfrage wird der allgemeine Tabellenausdruck verwendet und nicht das Basisobjekt.Any reference to expression_name in the query uses the common table expression and not the base object.

column_namecolumn_name
Gibt einen Spaltennamen im allgemeinen Tabellenausdruck an.Specifies a column name in the common table expression. Innerhalb der Definition eines allgemeinen Tabellenausdrucks sind doppelte Namen nicht zulässig.Duplicate names within a single CTE definition are not allowed. Die Anzahl der angegebenen Spaltennamen muss der Anzahl der Spalten im Resultset von CTE_query_definition entsprechen.The number of column names specified must match the number of columns in the result set of the CTE_query_definition. Die Liste der Spaltennamen ist nur optional, wenn in der Abfragedefinition für alle Spalten verschiedene Namen angegeben werden.The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

CTE_query_definitionCTE_query_definition
Gibt eine SELECT-Anweisung an, mit deren Resultset der allgemeine Tabellenausdruck aufgefüllt wird.Specifies a SELECT statement whose result set populates the common table expression. 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.The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE. Weitere Informationen finden Sie im Abschnitt „Hinweise“ und unter CREATE VIEW (Transact-SQL).For more information, see the Remarks section and CREATE VIEW (Transact-SQL).

Wenn CTE_query_definition mehrfach definiert ist, müssen die Abfragedefinitionen durch einen der folgenden Mengenoperator verbunden werden: UNION ALL, UNION, EXCEPT oder INTERSECT.If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.

RemarksRemarks

Richtlinien zum Erstellen und Verwenden allgemeiner TabellenausdrückeGuidelines for Creating and Using Common Table Expressions

Die folgenden Richtlinien gelten für nicht rekursive allgemeine Tabellenausdrücke.The following guidelines apply to nonrecursive common table expressions. Informationen zu Richtlinien für rekursive allgemeine Tabellenausdrücke finden Sie unter "Richtlinien zum Definieren und Verwenden rekursiver allgemeiner Tabellenausdrücke" weiter unten.For guidelines that apply to recursive common table expressions, see "Guidelines for Defining and Using Recursive Common Table Expressions" that follows.

  • 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.A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. Ein allgemeiner Tabellenausdruck kann auch in einer CREATE VIEW-Anweisung als Teil der definierenden SELECT-Anweisung der Sicht verwendet werden.A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

  • In einem nicht rekursiven allgemeinen Tabellenausdruck können mehrere Abfragedefinitionen für allgemeine Tabellenausdrücke definiert werden.Multiple CTE query definitions can be defined in a nonrecursive CTE. Die Definitionen müssen durch einen der folgenden Mengenoperatoren verbunden werden: UNION ALL, UNION, INTERSECT oder EXCEPT.The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.

  • Ein allgemeiner Tabellenausdruck kann in einer WITH-Klausel auf sich selbst und auf vorher definierte allgemeine Tabellenausdrücke verweisen.A CTE can reference itself and previously defined CTEs in the same WITH clause. Ein Vorwärtsverweis ist nicht zulässig.Forward referencing is not allowed.

  • Die Angabe mehrerer WITH-Klauseln in einem allgemeinen Tabellenausdruck ist nicht zulässig.Specifying more than one WITH clause in a CTE is not allowed. Wenn CTE_query_definition beispielsweise eine Unterabfrage enthält, darf diese Unterabfrage keine geschachtelte WITH-Klausel enthalten, die einen weiteren allgemeinen Tabellenausdruck definiert.For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • Die folgenden Klauseln dürfen in CTE_query_definition nicht verwendet werden:The following clauses cannot be used in the CTE_query_definition:

    • ORDER BY (Ausnahme: wenn eine TOP-Klausel angegeben ist)ORDER BY (except when a TOP clause is specified)

    • INTOINTO

    • OPTION-Klausel mit AbfragehinweisenOPTION clause with query hints

    • FOR BROWSEFOR BROWSE

  • Wird ein allgemeiner Tabellenausdruck in einer Anweisung verwendet, die zu einem Batch gehört, muss auf die vorangehende Anweisung ein Semikolon folgen.When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • Eine Abfrage, die auf einen allgemeinen Tabellenausdruck verweist, kann zur Definition eines Cursors verwendet werden.A query referencing a CTE can be used to define a cursor.

  • In einem allgemeinen Tabellenausdruck kann auf Tabellen auf Remoteservern verwiesen werden.Tables on remote servers can be referenced in the CTE.

  • Wenn ein allgemeiner Tabellenausdruck ausgeführt wird, können 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.When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. Wenn das passiert, gibt die Abfrage einen Fehler zurück.When this occurs, the query returns an error.

Richtlinien zum Definieren und Verwenden rekursiver allgemeiner TabellenausdrückeGuidelines for Defining and Using Recursive Common Table Expressions

Die folgenden Richtlinien gelten für die Definition rekursiver allgemeiner Tabellenausdrücke:The following guidelines apply to defining a recursive common table expression:

  • Die Definition des rekursiven allgemeinen Tabellenausdrucks muss mindestens zwei Abfragedefinitionen für allgemeine Tabellenausdrücke enthalten, und zwar ein Ankerelement und ein rekursives Element.The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Mehrere Ankerelemente und rekursive Elemente können definiert werden. Jedoch müssen alle Ankerelement-Abfragedefinitionen vor die erste Definition eines rekursiven Elements gesetzt werden.Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. Alle Abfragedefinitionen für allgemeine Tabellenausdrücke sind Ankerelemente, es sei denn, sie verweisen auf den allgemeinen Tabellenausdruck selbst.All CTE query definitions are anchor members unless they reference the CTE itself.

  • Ankerelemente müssen durch einen der folgenden Mengenoperatoren verbunden werden: UNION ALL, UNION, INTERSECT oder EXCEPT.Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or 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.UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.

  • Ankerelemente und rekursive Elemente müssen die gleiche Spaltenanzahl aufweisen.The number of columns in the anchor and recursive members must be the same.

  • Der Datentyp einer Spalte im rekursiven Element und der Datentyp der entsprechenden Spalte im Ankerelement müssen übereinstimmen.The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.

  • Die FROM-Klausel eines rekursiven Elements darf nur einmal auf den expression_name des allgemeinen Tabellenausdrucks verweisen.The FROM clause of a recursive member must refer only one time to the CTE expression_name.

  • Die folgenden Elemente sind in CTE_query_definition eines rekursiven Elements nicht zulässig:The following items are not allowed in the CTE_query_definition of a recursive member:

    • SELECT DISTINCTSELECT DISTINCT

    • GROUP BYGROUP BY

    • PIVOT (Wenn der Datenbank-Kompatibilitätsgrad 110 oder höher ist.PIVOT (When the database compatibility level is 110 or higher. Siehe Fehlerhafte Änderungen an Features der Datenbank-Engine in SQL Server 2016.)See Breaking Changes to Database Engine Features in SQL Server 2016.)

    • HAVINGHAVING

    • Skalare AggregationScalar aggregation

    • TOPTOP

    • LEFT, RIGHT, OUTER JOIN (INNER JOIN ist zulässig)LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)

    • UnterabfragenSubqueries

    • Ein Hinweis, der auf einen rekursiven Verweis für einen allgemeinen Tabellenausdruck innerhalb von CTE_query_definition angewendet wird.A hint applied to a recursive reference to a CTE inside a CTE_query_definition.

    Die folgenden Richtlinien gelten für die Verwendung rekursiver allgemeiner Tabellenausdrücke:The following guidelines apply to using a recursive common table expression:

  • 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.All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.

  • Ist ein rekursiver allgemeiner Tabellenausdruck falsch zusammengesetzt, kann dies zu einer Endlosschleife führen.An incorrectly composed recursive CTE may cause an infinite loop. Wenn beispielsweise die Abfragedefinition des rekursiven Elements für übergeordnete und untergeordnete Spalten die gleichen Werte zurückgibt, entsteht eine Endlosschleife.For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. 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 INSERT-, UPDATE-, DELETE- oder SELECT-Anweisung.To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. Somit können Sie die Ausführung der Anweisung steuern, bis das Codeproblem behoben wurde, das die Schleife verursacht.This lets you control the execution of the statement until you resolve the code problem that is creating the loop. Der serverweite Standardwert ist 100.The server-wide default is 100. Wenn 0 angegeben wird, wird keine Beschränkung angewendet.When 0 is specified, no limit is applied. Pro Anweisung kann nur ein Wert für MAXRECURSION angegeben werden.Only one MAXRECURSION value can be specified per statement. Weitere Informationen finden Sie unter Abfragehinweise (Transact-SQL).For more information, see Query Hints (Transact-SQL).

  • Eine Sicht, die einen rekursiven allgemeinen Tabellenausdruck enthält, kann nicht zum Aktualisieren von Daten verwendet werden.A view that contains a recursive common table expression cannot be used to update data.

  • Cursor können für Abfragen definiert werden, die allgemeine Tabellenausdrücke verwenden.Cursors may be defined on queries using CTEs. Der allgemeine Tabellenausdruck ist das select_statement-Argument, welches das Resultset des Cursors definiert.The CTE is the select_statement argument that defines the result set of the cursor. Für rekursive allgemeine Tabellenausdrücke sind nur schnelle Vorwärtscursor und statische (Momentaufnahme-)Cursor zulässig.Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. Wird in einem rekursiven allgemeinen Tabellenausdruck ein anderer Cursortyp angegeben, wird der Cursortyp in einen statischen Typ konvertiert.If another cursor type is specified in a recursive CTE, the cursor type is converted to static.

  • Im allgemeinen Tabellenausdruck kann auf Tabellen auf Remoteservern verwiesen werden.Tables on remote servers may be referenced in the CTE. 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.If the remote server is referenced in the recursive member of the CTE, a spool is created for each remote table so the tables can be repeatedly accessed locally. 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.If it is a CTE query, Index Spool/Lazy Spools is displayed in the query plan and will have the additional WITH STACK predicate. Dies ist eine Möglichkeit, um eine ordnungsgemäße Rekursion zu gewährleisten.This is one way to confirm proper recursion.

  • 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.Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. 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.Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data pased to the recursive part of the CTE. Weitere Informationen finden Sie weiter unten im Beispiel K.: „Verwenden von Analysefunktionen in einem rekursiven allgemeinen Tabellenausdruck“.For more information, see example K. Using analytical functions in a recursive CTE that follows.

Features und Einschränkungen allgemeiner Tabellenausdrücke in SQL Data WarehouseSQL Data Warehouse und Parallel Data WarehouseParallel Data WarehouseFeatures and Limitations of Common Table Expressions in SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

Die aktuelle Implementierung allgemeiner Tabellenausdrücke in SQL Data WarehouseSQL Data Warehouse und Parallel Data WarehouseParallel Data Warehouse weist folgende Features und Einschränkungen auf:The current implementation of CTEs in SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse have the following features and limitations:

  • Ein allgemeiner Tabellenausdruck kann in einer SELECT-Anweisung angegeben werden.A CTE can be specified in a SELECT statement.

  • Ein allgemeiner Tabellenausdruck kann in einer CREATE VIEW-Anweisung angegeben werden.A CTE can be specified in a CREATE VIEW statement.

  • Ein allgemeiner Tabellenausdruck kann in einer CTAS-Anweisung (CTAS = CREATE TABLE AS SELECT) angegeben werden.A CTE can be specified in a CREATE TABLE AS SELECT (CTAS) statement.

  • Ein allgemeiner Tabellenausdruck kann in einer CRTAS-Anweisung (CRTAS = CREATE REMOTE TABLE AS SELECT) angegeben werden.A CTE can be specified in a CREATE REMOTE TABLE AS SELECT (CRTAS) statement.

  • Ein allgemeiner Tabellenausdruck kann in einer CETAS-Anweisung (CETAS = CREATE EXTERNAL TABLE AS SELECT) angegeben werden.A CTE can be specified in a CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.

  • Ein allgemeiner Tabellenausdruck kann auf eine Remotetabelle verweisen.A remote table can be referenced from a CTE.

  • Ein allgemeiner Tabellenausdruck kann auf eine externe Tabelle verweisen.An external table can be referenced from a CTE.

  • In einem allgemeinen Tabellenausdruck können mehrere Abfragedefinitionen für allgemeine Tabellenausdrücke definiert werden.Multiple CTE query definitions can be defined in a CTE.

  • Einem allgemeinen Tabellenausdruck muss eine einzelne SELECT-Anweisung folgen.A CTE must be followed by a single SELECT statement. Die Anweisungen INSERT, UPDATE, DELETE und MERGE werden nicht unterstützt.INSERT, UPDATE, DELETE, and MERGE statements are not supported.

  • Ein allgemeiner Tabellenausdruck, der Verweise auf sich selbst (ein rekursiver allgemeiner Tabellenausdruck) enthält, wird nicht unterstützt.A common table expression that includes references to itself (a recursive common table expression) is not supported.

  • Die Angabe mehrerer WITH-Klauseln in einem allgemeinen Tabellenausdruck ist nicht zulässig.Specifying more than one WITH clause in a CTE is not allowed. Wenn eine „CTE_query_definition“ beispielsweise eine Unterabfrage enthält, darf diese Unterabfrage keine geschachtelte WITH-Klausel enthalten, die einen weiteren allgemeinen Tabellenausdruck definiert.For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • Eine ORDER BY-Klausel darf in der „CTE_query_definition“ nicht verwendet werden. Eine Ausnahme gilt, wenn eine TOP-Klausel angegeben ist.An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.

  • Wird ein allgemeiner Tabellenausdruck in einer Anweisung verwendet, die zu einem Batch gehört, muss auf die vorangehende Anweisung ein Semikolon folgen.When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • Wenn allgemeine Tabellenausdrücke in von sp_prepare vorbereiteten Anweisungen verwendet werden, verhalten sie sich wie andere SELECT-Anweisungen in PDW.When used in statements prepared by sp_prepare, CTEs will behave the same way as other SELECT statements in PDW. Wenn allgemeine Tabellenausdrücke jedoch in von sp_prepare vorbereiteten CETAS-Anweisungen verwendet werden, kann das Verhalten aufgrund der Art und Weise, wie die Bindung für sp_prepare implementiert wird, von SQL ServerSQL Server und anderen PDW-Anweisungen abweichen.However, if CTEs are used as part of CETAS prepared by sp_prepare, the behavior can defer from SQL ServerSQL Server and other PDW statements because of the way binding is implemented for sp_prepare. 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.If SELECT that references CTE is using a wrong column that does not exist in CTE, the sp_prepare will pass without detecting the error, but the error will be thrown during sp_execute instead.

BeispieleExamples

A.A. Erstellen eines einfachen allgemeinen TabellenausdrucksCreating a simple common table expression

Im folgenden Beispiel wird die Gesamtanzahl der Aufträge pro Jahr für jeden Vertriebsmitarbeiter von Adventure Works CyclesAdventure Works Cycles angezeigt.The following example shows the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.


-- 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.B. Verwenden eines allgemeinen Tabellenausdrucks zum Einschränken von Anzahlen und Wiedergeben von DurchschnittswertenUsing a common table expression to limit counts and report averages

Im folgenden Beispiel wird die durchschnittliche Anzahl der Verkaufsaufträge der Vertriebsmitarbeiter für alle Jahre veranschaulicht.The following example shows the average number of sales orders for all years for the sales representatives.

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.C. Verwenden mehrerer Definitionen für allgemeine Tabellenausdrücke in einer einzelnen AbfrageUsing multiple CTE definitions in a single query

Im folgenden Beispiel wird veranschaulicht, wie mehrere allgemeine Tabellenausdrücke in einer einzelnen Abfrage definiert werden.The following example shows how to define more than one CTE in a single query. Die Abfragedefinitionen für allgemeine Tabellenausdrücke werden durch ein Komma voneinander getrennt.Notice that a comma is used to separate the CTE query definitions. Die FORMAT-Funktion, die verwendet wird, um die Geldbeträge in einem Währungsformat anzuzeigen, ist in SQL Server 2012 und höher verfügbar.The FORMAT function, used to display the monetary amounts in a currency format, is available in SQL Server 2012 and higher.


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

Dies ist ein Auszug aus dem Resultset.Here is a partial result set.


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.D. Verwenden eines rekursiven allgemeinen Tabellenausdrucks, um mehrere Rekursionsebenen anzuzeigenUsing a recursive common table expression to display multiple levels of recursion

Im folgenden Beispiel werden Vorgesetzte in einer Hierarchieliste sowie die Mitarbeiter angezeigt, die diesen unterstellt sind.The following example shows the hierarchical list of managers and the employees who report to them. In diesem Beispiel wird zunächst die dbo.MyEmployees-Tabelle erstellt und aufgefüllt.The example begins by creating and populating the dbo.MyEmployees table.

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

E.E. Verwenden eines rekursiven allgemeinen Tabellenausdrucks, um zwei Rekursionsebenen anzuzeigenUsing a recursive common table expression to display two levels of recursion

Im folgenden Beispiel werden Vorgesetzte sowie die Mitarbeiter angezeigt, die diesen unterstellt sind.The following example shows managers and the employees reporting to them. Die Anzahl der zurückgegebenen Ebenen wird auf zwei Ebenen eingeschränkt.The number of levels returned is limited to two.

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

F.F. Verwenden eines rekursiven allgemeinen Tabellenausdrucks, um eine Hierarchieliste anzuzeigenUsing a recursive common table expression to display a hierarchical list

Das folgende Beispiel baut auf Beispiel D auf, indem die Namen der Vorgesetzten und Mitarbeiter und deren Titel hinzugefügt werden.The following example builds on Example D by adding the names of the manager and employees, and their respective titles. Die Hierarchieebenen von Vorgesetzten und Mitarbeitern werden zusätzlich hervorgehoben, indem die einzelnen Ebenen jeweils eingerückt werden.The hierarchy of managers and employees is additionally emphasized by indenting each level.

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

G.G. Verwenden von MAXRECURSION zum Abbrechen einer AnweisungUsing MAXRECURSION to cancel a statement

Mit MAXRECURSION kann verhindert werden, dass ein rekursiver allgemeiner Tabellenausdruck, der fehlerhaft formuliert ist, in eine Endlosschleife gerät.MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. Im folgenden Beispiel wird absichtlich eine Endlosschleife erstellt. Außerdem wird MAXRECURSION verwendet, um die Anzahl der Rekursionsebenen auf zwei zu beschränken.The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two.

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

Sobald der Fehler im Code behoben wurde, wird MAXRECURSION nicht mehr benötigt.After the coding error is corrected, MAXRECURSION is no longer required. Das folgende Beispiel zeigt den korrigierten Code.The following example shows the corrected code.

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

H.H. Verwenden eines allgemeinen Tabellenausdrucks, um eine rekursive Beziehung in einer SELECT-Anweisung selektiv zu durchlaufenUsing a common table expression to selectively step through a recursive relationship in a SELECT statement

Im folgenden Beispiel wird die Hierarchie von Produktgruppen und Komponenten gezeigt, die erforderlich sind, um das Fahrrad für ProductAssemblyID = 800 zu montieren.The following example shows the hierarchy of product assemblies and components that are required to build the bicycle for ProductAssemblyID = 800.

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

I.I. Verwenden eines rekursiven allgemeinen Tabellenausdrucks in einer UPDATE-AnweisungUsing a recursive CTE in an UPDATE statement

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.The following example updates the PerAssemblyQty value for all parts that are used to build the product 'Road-550-W Yellow, 44' (ProductAssemblyID``800). 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.The common table expression returns a hierarchical list of parts that are used to build ProductAssemblyID 800 and the components that are used to create those parts, and so on. Nur die Zeilen, die vom allgemeinen Tabellenausdruck zurückgegeben werden, werden geändert.Only the rows returned by the common table expression are modified.

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

J.J. Verwenden mehrerer Ankerelemente und rekursiver ElementeUsing multiple anchor and recursive members

Im folgenden Beispiel werden mehrere Ankerelemente und rekursive Elemente verwendet, um alle Vorfahren einer bestimmten Person zurückzugeben.The following example uses multiple anchor and recursive members to return all the ancestors of a specified person. Eine Tabelle wird erstellt und mit Werten aufgefüllt, um den Familienstammbaum zu erstellen, der vom rekursiven allgemeinen Tabellenausdruck zurückgegeben wird.A table is created and values inserted to establish the family genealogy returned by the recursive CTE.

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

K.K. Verwenden von Analysefunktionen in einem rekursiven allgemeinen TabellenausdruckUsing analytical functions in a recursive CTE

Im folgenden Beispiel wird ein Fehler gezeigt, der beim Verwenden einer Analyse- oder Aggregatfunktion im rekursiven Teil eines allgemeinen Tabellenausdrucks auftreten kann.The following example shows a pitfall that can occur when using an analytical or aggregate function in the recursive part of a CTE.

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.The following results are the expected results for the query.

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.The following results are the actual results for the query.

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.N returns 1 for each pass of the recursive part of the CTE because only the subset of data for that recursion level is passed to ROWNUMBER. Für jede Iteration des rekursiven Teils der Abfrage wird nur eine Zeile an ROWNUMBER übergeben.For each of the iterations of the recursive part of the query, only one row is passed to ROWNUMBER.

Beispiele: SQL Data WarehouseSQL Data Warehouse und Parallel Data WarehouseParallel Data Warehouse.Examples: SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

L.L. Verwenden eines allgemeinen Tabellenausdrucks in einer CTAS-AnweisungUsing a common table expression within a CTAS statement

Im folgenden Beispiel wird eine neue Tabelle mit der Gesamtanzahl der Verkäufe pro Jahr für jeden Vertriebsmitarbeiter von Adventure Works CyclesAdventure Works Cycles angezeigt.The following example creates a new table containing the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.

-- Uses AdventureWorks  

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  

M.M. Verwenden eines allgemeinen Tabellenausdrucks in einer CETAS-AnweisungUsing a common table expression within a CETAS statement

Im folgenden Beispiel wird eine neue externe Tabelle mit der Gesamtanzahl der Verkäufe pro Jahr für jeden Vertriebsmitarbeiter von Adventure Works CyclesAdventure Works Cycles angezeigt.The following example creates a new external table containing the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.

-- Uses AdventureWorks  

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  

N.N. Verwenden mehrerer, durch Kommas getrennter allgemeiner Tabellenausdrücke in einer AnweisungUsing multiple comma separated CTEs in a statement

Im folgenden Beispiel wird veranschaulicht, wie zwei allgemeine Tabellenausdrücke in eine einzige Anweisung eingeschlossen werden.The following example demonstrates including two CTEs in a single statement. Die allgemeinen Tabellenausdrücke dürfen nicht verschachtelt werden (keine Rekursion).The CTEs cannot be nested (no recursion).

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;  

Weitere Informationen finden Sie unterSee Also

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