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

SI APPLICA A: sìSQL Server sìDatabase SQL di Azure sìAzure SQL Data Warehouse sìParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Indica un set di risultati denominato temporaneo, noto come espressione di tabella comune (CTE).Specifies a temporary named result set, known as a common table expression (CTE). Questa deriva da una query semplice e viene definita all'interno dell'ambito di esecuzione di una singola istruzione SELECT, INSERT, UPDATE, DELETE o MERGE.This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. Questa clausola può anche essere utilizzata in un'istruzione CREATE VIEW come parte dell'istruzione di definizione SELECT.This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. Un'espressione di tabella comune può includere riferimenti a se stessa.A common table expression can include references to itself. In questo caso viene indicata con il nome di espressione di tabella comune ricorsiva.This is referred to as a recursive common table expression.

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintassiSyntax

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

ArgomentiArguments

expression_nameexpression_name
Identificatore valido per l'espressione di tabella comune.Is a valid identifier for the common table expression. expression_name deve essere diverso dal nome di qualsiasi altra espressione di tabella comune definita nella stessa clausola WITH <common_table_expression>, ma expression_name può corrispondere al nome di una vista o tabella di base.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. Tutti i riferimenti a expression_name nella query usano l'espressione di tabella comune e non l'oggetto di base.Any reference to expression_name in the query uses the common table expression and not the base object.

column_namecolumn_name
Specifica un nome di colonna nell'espressione di tabella comune.Specifies a column name in the common table expression. Non sono consentiti nomi duplicati all'interno di una singola definizione CTE.Duplicate names within a single CTE definition are not allowed. Il numero dei nomi di colonna specificato deve corrispondere al numero delle colonne nel set di risultati di CTE_query_definition.The number of column names specified must match the number of columns in the result set of the CTE_query_definition. L'elenco dei nomi di colonna è facoltativo solo se i nomi distinti di tutte le colonne risultanti sono specificati nella definizione della query.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
Specifica un'istruzione SELECT il cui set di risultati popola l'espressione di tabella comune.Specifies a SELECT statement whose result set populates the common table expression. L'istruzione SELECT per CTE_query_definition deve soddisfare gli stessi requisiti necessari per creare una vista, con la differenza che una CTE non può definire un'altra CTE.The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE. Per altre informazioni, vedere la sezione Osservazioni e CREATE VIEW (Transact-SQL).For more information, see the Remarks section and CREATE VIEW (Transact-SQL).

Se si definiscono più elementi CTE_query_definition, è necessario creare un join delle definizioni di query in base a uno dei seguenti operatori sui set: UNION ALL, UNION, EXCEPT o 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

Linee guida per la creazione e l'utilizzo delle espressioni di tabella comuniGuidelines for Creating and Using Common Table Expressions

Le linee guida seguenti sono valide per le espressioni di tabella comuni non ricorsive.The following guidelines apply to nonrecursive common table expressions. Per le linee guida relative alle espressioni di tabella comuni ricorsive, vedere Linee guida per la definizione e l'utilizzo delle espressioni di tabella comuni ricorsive più avanti.For guidelines that apply to recursive common table expressions, see Guidelines for Defining and Using Recursive Common Table Expressions that follows.

  • Una CTE deve essere seguita da un'istruzione SELECT, INSERT, UPDATE o DELETE singola che faccia riferimento ad alcune o a tutte le colonne della CTE.A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. Una CTE può anche essere specificata in un'istruzione CREATE VIEW come parte dell'istruzione di definizione SELECT della vista.A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

  • In una CTE non ricorsiva è possibile definire più query CTE.Multiple CTE query definitions can be defined in a nonrecursive CTE. Le definizioni devono essere combinate da uno degli operatori sui set seguenti: UNION ALL, UNION, INTERSECT, o EXCEPT.The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.

  • Una CTE può far riferimento a se stessa e alle CTE definite in precedenza nella stessa clausola WITH.A CTE can reference itself and previously defined CTEs in the same WITH clause. Il riferimento in avanti non è consentito.Forward referencing is not allowed.

  • Non è consentito specificare più di una clausola WITH in una CTE.Specifying more than one WITH clause in a CTE is not allowed. Se ad esempio una definizione CTE_query_definition include una sottoquery, tale sottoquery non può includere una clausola WITH annidata che definisce un'altra CTE.For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • Le clausole seguenti non possono essere usate in CTE_query_definition:The following clauses cannot be used in the CTE_query_definition:

    • ORDER BY (tranne quando si specifica una clausola TOP)ORDER BY (except when a TOP clause is specified)

    • INTO

    • Clausola OPTION con hint per la queryOPTION clause with query hints

    • FOR BROWSE

  • Quando un'espressione CTE viene utilizzata in un'istruzione che fa parte di un batch, l'istruzione precedente deve essere seguita da un punto e virgola.When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • Una query che fa riferimento a un'espressione CTE può essere utilizzata per definire un cursore.A query referencing a CTE can be used to define a cursor.

  • L'espressione CTE può fare riferimento alle tabelle nei server remoti.Tables on remote servers can be referenced in the CTE.

  • Durante l'esecuzione di una CTE, tutti gli hint che fanno riferimento a una CTE possono entrare in conflitto con altri hint individuati quando la CTE accede alle tabelle sottostanti, allo stesso modo degli hint che fanno riferimento alle viste nelle query.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. In questo caso, la query restituisce un errore.When this occurs, the query returns an error.

Linee guida per la definizione e l'utilizzo delle espressioni di tabella comuni ricorsiveGuidelines for Defining and Using Recursive Common Table Expressions

Le linee guida seguenti sono valide per la definizione delle espressioni di tabella comuni ricorsive.The following guidelines apply to defining a recursive common table expression:

  • La definizione CTE ricorsiva deve contenere almeno due definizioni di query CTE, un membro non ricorsivo e un membro ricorsivo.The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. È possibile definire più membri ricorsivi e non ricorsivi, ma tutte le definizioni delle query dei membri non ricorsivi devono precedere la definizione del primo membro ricorsivo.Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. Tutte le definizioni delle query CTE sono membri non ricorsivi tranne nei casi un cui fanno riferimento all'espressione CTE stessa.All CTE query definitions are anchor members unless they reference the CTE itself.

  • I membri non ricorsivi devono essere combinati da uno degli operatori sui set seguenti: UNION ALL, UNION, INTERSECT o EXCEPT.Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL è l'unico operatore sui set consentito tra l'ultimo membro non ricorsivo e il primo membro ricorsivo, nonché durante la combinazione di più membri ricorsivi.UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.

  • Il numero delle colonne nei membri ricorsivi e non ricorsivi deve essere lo stesso.The number of columns in the anchor and recursive members must be the same.

  • Il tipo di dati di una colonna nel membro ricorsivo deve essere lo stesso del tipo di dati della colonna corrispondente nel membro non ricorsivo.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.

  • La clausola FROM di un membro ricorsivo deve fare riferimento solo una volta all'espressione CTE expression_name.The FROM clause of a recursive member must refer only one time to the CTE expression_name.

  • Gli elementi seguenti non sono consentiti nella definizione CTE_query_definition di un membro ricorsivo:The following items are not allowed in the CTE_query_definition of a recursive member:

Le linee guida seguenti sono valide per l'utilizzo delle espressioni di tabella comuni ricorsive.The following guidelines apply to using a recursive common table expression:

  • Tutte le colonne restituite dalla CTE ricorsiva ammettono valori Null a prescindere dal supporto dei valori Null delle colonne restituite dalle istruzioni SELECT coinvolte.All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.

  • Una CTE ricorsiva formulata in modo non corretto può provocare un ciclo infinito.An incorrectly composed recursive CTE may cause an infinite loop. Ad esempio, se la definizione della query del membro ricorsivo restituisce gli stessi valori per entrambe le colonne padre e figlio, si crea un ciclo infinito.For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. Per evitare un ciclo infinito, è possibile limitare il numero di livelli di ricorsione consentito per una particolare espressione usando l'hint MAXRECURSION e un valore compreso tra 0 e 32.767 nella clausola OPTION dell'istruzione INSERT, UPDATE, DELETE, o SELECT.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. Ciò consente di controllare l'esecuzione dell'istruzione fino a quando non viene risolto il problema relativo al codice che sta creando il ciclo.This lets you control the execution of the statement until you resolve the code problem that is creating the loop. Il valore predefinito per l'intero server è 100.The server-wide default is 100. Se è specificato 0, non viene applicato alcun limite.When 0 is specified, no limit is applied. È possibile specificare solo un valore MAXRECURSION per istruzione.Only one MAXRECURSION value can be specified per statement. Per altre informazioni, vedere Hint per la query (Transact-SQL).For more information, see Query Hints (Transact-SQL).

  • Non è possibile utilizzare una vista che contiene un'espressione di tabella comune ricorsiva per aggiornare i dati.A view that contains a recursive common table expression cannot be used to update data.

  • I cursori possono essere definiti sulle query tramite CTE.Cursors may be defined on queries using CTEs. La CTE corrisponde all'argomento select_statement che definisce il set di risultati del cursore.The CTE is the select_statement argument that defines the result set of the cursor. Sono consentiti solo i cursori fast forward only e statici (snapshot) per le CTE ricorsive.Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. Se viene specificato un altro tipo di cursore in una CTE ricorsiva, il tipo di cursore viene convertito in statico.If another cursor type is specified in a recursive CTE, the cursor type is converted to static.

  • Nelle CTE è possibile far riferimento alle tabelle nei server remoti.Tables on remote servers may be referenced in the CTE. Se nel membro ricorsivo della CTE si fa riferimento al server remoto, viene creato uno spool per ogni tabella remota in maniera che si possa accedere alle tabelle in modo locale ripetutamente.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. Se la query è di tipo CTE, Index Spool o Lazy Spool viene visualizzato nel piano di query con il predicato aggiuntivo WITH STACK associato.If it is a CTE query, Index Spool/Lazy Spools is displayed in the query plan and will have the additional WITH STACK predicate. Questo è uno dei modi utilizzati per confermare una ricorsione appropriata.This is one way to confirm proper recursion.

  • Le funzioni analitiche e di aggregazione nella parte ricorsiva dell'espressione CTE vengono applicate al set per il livello di ricorsione corrente, non al set per l'espressione CTE.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. Le funzioni come ROW_NUMBER funzionano solo nel subset di dati passato dal livello di ricorsione corrente e non nell'intero set di dati passato alla parte ricorsiva della CTE.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 passed to the recursive part of the CTE. Per altre informazioni, vedere l'esempio K, Utilizzo di funzioni analitiche in un'espressione CTE ricorsiva, più avanti.For more information, see example K. Using analytical functions in a recursive CTE that follows.

Funzionalità e limitazioni delle espressioni di tabella comuni in SQL Data WarehouseSQL Data Warehouse e Parallel Data WarehouseParallel Data WarehouseFeatures and Limitations of Common Table Expressions in SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

L'implementazione corrente delle CTE in SQL Data WarehouseSQL Data Warehouse e Parallel Data WarehouseParallel Data Warehouse presenta le caratteristiche e le limitazioni seguenti:The current implementation of CTEs in SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse have the following features and limitations:

  • Una CTE può essere specificata in un'istruzione SELECT.A CTE can be specified in a SELECT statement.

  • Una CTE può essere specificata in un'istruzione CREATE VIEW.A CTE can be specified in a CREATE VIEW statement.

  • Una CTE può essere specificata in un'istruzione CREATE TABLE AS SELECT (CTAS).A CTE can be specified in a CREATE TABLE AS SELECT (CTAS) statement.

  • Una CTE può essere specificata in un'istruzione CREATE REMOTE TABLE AS SELECT (CRTAS).A CTE can be specified in a CREATE REMOTE TABLE AS SELECT (CRTAS) statement.

  • Una CTE può essere specificata in un'istruzione CREATE EXTERNAL TABLE AS SELECT (CETAS).A CTE can be specified in a CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.

  • Una CTE può fare riferimento a una tabella remota.A remote table can be referenced from a CTE.

  • Una CTE può fare riferimento a una tabella esterna.An external table can be referenced from a CTE.

  • In una CTE è possibile definire più query CTE.Multiple CTE query definitions can be defined in a CTE.

  • Una CTE deve essere seguita da un'unica istruzione SELECT.A CTE must be followed by a single SELECT statement. Le istruzioni INSERT, UPDATE, DELETE e MERGE non sono supportate.INSERT, UPDATE, DELETE, and MERGE statements are not supported.

  • Le espressioni di tabella comuni che includono riferimenti a se stesse (espressioni di tabella comuni ricorsive) non sono supportate.A common table expression that includes references to itself (a recursive common table expression) is not supported.

  • Non è consentito specificare più di una clausola WITH in una CTE.Specifying more than one WITH clause in a CTE is not allowed. Se ad esempio la definizione di una query CTE include una sottoquery, tale sottoquery non può includere una clausola WITH annidata che definisce un'altra CTE.For example, if a CTE query definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • Non è possibile usare una clausola ORDER BY in una definizione CTE_query_definition, tranne quando è specificata una clausola TOP.An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.

  • Quando un'espressione CTE viene utilizzata in un'istruzione che fa parte di un batch, l'istruzione precedente deve essere seguita da un punto e virgola.When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • Se usate all'interno di istruzioni preparate da sp_prepare, le CTE si comportano allo stesso modo delle altre istruzioni SELECT in PDW.When used in statements prepared by sp_prepare, CTEs will behave the same way as other SELECT statements in PDW. Se tuttavia le CTE vengono usate all'interno di CETAS preparate da sp_prepare, il comportamento può differire da quello di SQL ServerSQL Server e di altre istruzioni PDW a causa della modalità di implementazione dell'associazione per sp_prepare.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. Se l'istruzione SELECT che fa riferimento a una CTE usa una colonna non corretta che non esiste nella CTE, l'errore non viene rilevato durante l'esecuzione di sp_prepare, ma viene generato durante l'esecuzione di sp_execute.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.

EsempiExamples

A.A. Creazione di un'espressione di tabella comune sempliceCreating a simple common table expression

Nell'esempio seguente viene illustrato il numero totale di ordini di vendita all'anno per tutti i venditori di Adventure Works CyclesAdventure Works Cycles.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;  

b.B. Utilizzo di un'espressione di tabella comune per limitare il numero medio di ordiniUsing a common table expression to limit counts and report averages

Nell'esempio seguente viene illustrato il numero medio di ordini di vendita all'anno per i venditori.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;  

C.C. Utilizzo di più definizioni CTE in una singola queryUsing multiple CTE definitions in a single query

Nell'esempio seguente viene illustrato come definire più di una CTE in una singola query.The following example shows how to define more than one CTE in a single query. Si noti che per separare le definizioni di query CTE è utilizzata una virgola.Notice that a comma is used to separate the CTE query definitions. La funzione FORMAT, utilizzata per visualizzare gli importi monetari in un formato di valuta, è disponibile in SQL Server 2012 e versioni successive.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;    

Set di risultati parziale: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. Utilizzo di un'espressione di tabella comune ricorsiva per visualizzare più livelli di ricorsioneUsing a recursive common table expression to display multiple levels of recursion

Nell'esempio seguente viene illustrato l'elenco gerarchico dei responsabili e dei dipendenti a loro subordinati.The following example shows the hierarchical list of managers and the employees who report to them. L'esempio inizia con la creazione e il popolamento della tabella dbo.MyEmployees.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;   

E.E. Utilizzo di un'espressione di tabella comune ricorsiva per visualizzare due livelli di ricorsioneUsing a recursive common table expression to display two levels of recursion

Nell'esempio seguente vengono illustrati i responsabili e i dipendenti che sono loro subordinati.The following example shows managers and the employees reporting to them. Il numero di livelli restituiti è limitato a due.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 ;  

F.F. Utilizzo di un'espressione di tabella comune ricorsiva per visualizzare un elenco gerarchicoUsing a recursive common table expression to display a hierarchical list

Nell'esempio seguente viene utilizzato come base l'esempio D aggiungendo i nomi del responsabile e dei dipendenti e i loro rispettivi titoli.The following example builds on Example D by adding the names of the manager and employees, and their respective titles. La gerarchia dei responsabili e dei dipendenti viene inoltre evidenziata rientrando ogni livello.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;  

G.G. Utilizzo di MAXRECURSION per annullare un'istruzioneUsing MAXRECURSION to cancel a statement

È possibile utilizzare MAXRECURSION per impedire che una CTE ricorsiva non corretta provochi un ciclo infinito.MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. Nell'esempio seguente viene creato intenzionalmente un ciclo infinito e viene utilizzato l'hint MAXRECURSION per limitare a due il numero di livelli di ricorsione.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);  

Dopo la correzione dell'errore del codice, MAXRECURSION non è più necessario.After the coding error is corrected, MAXRECURSION is no longer required. Nell'esempio seguente viene illustrato il codice corretto.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;  

H.H. Utilizzo di un'espressione di tabella comune per analizzare in maniera selettiva una relazione ricorsiva in un'istruzione SELECTUsing a common table expression to selectively step through a recursive relationship in a SELECT statement

Nell'esempio seguente viene illustrata la gerarchia di assembly e componenti del prodotto che sono necessari per costruire la bicicletta per ProductAssemblyID = 800.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;  

I.I. Utilizzo di una CTE ricorsiva in un'istruzione UPDATEUsing a recursive CTE in an UPDATE statement

L'esempio seguente aggiorna il valore PerAssemblyQty per tutte le parti usate per costruire il prodotto 'Road-550-W Yellow, 44' (ProductAssemblyID``800).The following example updates the PerAssemblyQty value for all parts that are used to build the product 'Road-550-W Yellow, 44' (ProductAssemblyID``800). L'espressione di tabella comune restituisce un elenco gerarchico di parti utilizzate per compilare ProductAssemblyID 800, i componenti utilizzati per creare tali parti e così via.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. Vengono modificate solo le righe restituite dall'espressione di tabella comune.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. Utilizzo di più membri ricorsivi e non ricorsiviUsing multiple anchor and recursive members

Nell'esempio seguente vengono utilizzati più membri ricorsivi e non ricorsivi per restituire tutti gli antenati di una specifica persona.The following example uses multiple anchor and recursive members to return all the ancestors of a specified person. Viene creata una tabella e vengono inseriti i valori per stabilire l'albero genealogico restituito dalla CTE ricorsiva.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. Utilizzo di funzioni analitiche in un'espressione CTE ricorsivaUsing analytical functions in a recursive CTE

Nell'esempio seguente viene illustrata una trappola in cui si può cadere quando si utilizza una funzione analitica o di aggregazione nella parte ricorsiva di un'espressione CTE.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;  

Di seguito vengono riportati i risultati previsti per la query.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  

Di seguito vengono riportati i risultati effettivi per la query.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 restituisce 1 per ogni sessione della parte ricorsiva dell'espressione CTE perché solo il subset di dati per tale livello di ricorsione viene passato a ROWNUMBER.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. Per ognuna delle iterazioni della parte ricorsiva della query, viene passata solo una riga a ROWNUMBER.For each of the iterations of the recursive part of the query, only one row is passed to ROWNUMBER.

Esempi: SQL Data WarehouseSQL Data Warehouse e Parallel Data WarehouseParallel Data WarehouseExamples: SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

L.L. Uso di un'espressione di tabella comune all'interno di un'istruzione CTASUsing a common table expression within a CTAS statement

L'esempio seguente crea una nuova tabella contenente il numero totale di ordini di vendita all'anno per tutti i venditori di Adventure Works CyclesAdventure Works Cycles.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.

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

M.M. Uso di un'espressione di tabella comune all'interno di un'istruzione CETASUsing a common table expression within a CETAS statement

L'esempio seguente crea una nuova tabella esterna contenente il numero totale di ordini di vendita all'anno per tutti i venditori di Adventure Works CyclesAdventure Works Cycles.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.

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

N.N. Uso di più CTE delimitate da virgole in un'istruzioneUsing multiple comma separated CTEs in a statement

L'esempio seguente illustra come includere due CTE all'interno di un'unica istruzione.The following example demonstrates including two CTEs in a single statement. Le CTE non possono essere annidate (la ricorsione non è consentita).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;  

Vedere ancheSee Also

CREATE VIEW (Transact-SQL) CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
EXCEPT e 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)