Condividi tramite

WITH common_table_expression (Transact-SQL)

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure endpoint di analisi SQL di Azure Synapse AnalyticsPlatform System (PDW)in Microsoft FabricWarehouse nel database SQL di Microsoft Fabricin Microsoft Fabric

Indica un set di risultati denominato temporaneo, noto come espressione di tabella comune (CTE). Ciò è derivato da una query semplice e definito nell'ambito di esecuzione di un'unica SELECTistruzione , INSERT, UPDATEMERGE, o DELETE . Questa clausola può essere usata anche in un'istruzione CREATE VIEW come parte dell'istruzione di definizione SELECT . Un'espressione di tabella comune può includere riferimenti a se stessa. In questo caso viene indicata con il nome di espressione di tabella comune ricorsiva.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

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

Argomenti

expression_name

Un identificatore valido per l'espressione di tabella comune. 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. Tutti i riferimenti a expression_name nella query usano l'espressione di tabella comune e non l'oggetto di base.

column_name

Specifica un nome di colonna nell'espressione di tabella comune. Non sono consentiti nomi duplicati all'interno di una singola definizione CTE. Il numero dei nomi di colonna specificato deve corrispondere al numero delle colonne nel set di risultati di 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.

CTE_query_definition

Specifica un'istruzione SELECT il cui set di risultati popola l'espressione di tabella comune. L'istruzione SELECT per CTE_query_definition deve soddisfare gli stessi requisiti per la creazione di una vista, ad eccezione di un CTE non può definire un altro CTE. Per altre informazioni, vedere la sezione Osservazioni e CREATE VIEW.

Se sono definiti più CTE_query_definition, le definizioni di query devono essere unite da uno di questi operatori set: UNION ALL, UNIONEXCEPT, o INTERSECT.

Linee guida per l'utilizzo

Linee guida per le espressioni di tabella comuni non ricorsive

Annotazioni

Le linee guida seguenti sono valide per le espressioni di tabella comuni non ricorsive. Per linee guida applicabili alle espressioni di tabella comuni ricorsive, vedere Linee guida per espressioni di tabella comuni ricorsive.

Un CTE deve essere seguito da una singola SELECTistruzione , INSERT, UPDATE, MERGEo DELETE che fa riferimento ad alcune o a tutte le colonne CTE. Una CTE può anche essere specificata in un'istruzione CREATE VIEW come parte dell'istruzione di definizione SELECT della vista.

In una CTE non ricorsiva è possibile definire più query CTE. Le definizioni devono essere combinate da uno degli operatori sui set seguenti: UNION ALL, UNION, INTERSECT, o EXCEPT.

Una CTE può far riferimento a se stessa e alle CTE definite in precedenza nella stessa clausola WITH. Il riferimento in avanti non è consentito.

Non è consentito specificare più di una clausola WITH in una CTE. Ad esempio, se un CTE_query_definition contiene una sottoquery, tale sottoquery non può contenere una clausola nidificata WITH che definisce un'altra CTE.

Per altre informazioni sulle CTE annidate in Microsoft Fabric, vedere Nested Common Table Expression (CTE) in Fabric data warehousing (Transact-SQL).

I risultati delle query provenienti da espressioni di tabella comuni non vengono materializzati. Per ogni riferimento esterno al set di risultati denominato è necessario eseguire nuovamente la query definita. Per le query che richiedono più riferimenti al set di risultati denominato, prendere in considerazione l'uso di un oggetto temporaneo .

Non è possibile eseguire una stored procedure in un'espressione di tabella comune.

Le clausole seguenti non possono essere usate in CTE_query_definition:

  • ORDER BY (tranne quando viene specificata una TOP clausola o OFFSET/FETCH )
  • INTO
  • OPTION clausola con hint di query 1
  • FOR BROWSE

1 La OPTION clausola non può essere usata all'interno di una definizione CTE. Può essere usato solo nell'istruzione più esterna SELECT .

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.

Una query che fa riferimento a un'espressione CTE può essere utilizzata per definire un cursore.

L'espressione CTE può fare riferimento alle tabelle nei server remoti.

Quando si esegue un CTE, gli hint che fanno riferimento a un CTE possono essere in conflitto con altri hint individuati quando il CTE accede alle tabelle sottostanti, allo stesso modo degli hint che fanno riferimento alle viste nelle query. In questo caso, la query restituisce un errore.

Linee guida per le espressioni di tabella comuni ricorsive

Annotazioni

Le linee guida seguenti si applicano alla definizione di un'espressione di tabella comune ricorsiva. Per linee guida applicabili alle CTE non ricorsive, vedere Linee guida per espressioni di tabella comuni non ricorsive.

La definizione CTE ricorsiva deve contenere almeno due definizioni di query CTE, un membro non ricorsivo e un membro ricorsivo. È 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. Tutte le definizioni delle query CTE sono membri non ricorsivi tranne nei casi un cui fanno riferimento all'espressione CTE stessa.

I membri di ancoraggio devono essere combinati da uno di questi operatori set: UNION ALL, UNION, INTERSECTo EXCEPT. UNION ALL è l'unico operatore set consentito tra l'ultimo membro di ancoraggio e il primo membro ricorsivo e quando si combinano più membri ricorsivi.

Il numero delle colonne nei membri ricorsivi e non ricorsivi deve essere lo stesso.

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.

La clausola FROM di un membro ricorsivo deve fare riferimento solo una volta all'espressione CTE expression_name.

Gli elementi seguenti non sono consentiti nella definizione CTE_query_definition di un membro ricorsivo:

  • SELECT DISTINCT
  • GROUP BY
  • PIVOT 1
  • HAVING
  • Aggregazione scalare
  • TOP
  • LEFT, RIGHT, OUTER JOIN (INNER JOIN è consentita)
  • Subqueries (Sottoquery)
  • Hint applicato a un riferimento ricorsivo a un'espressione CTE all'interno di una definizione CTE_query_definition.

1 Quando il livello di compatibilità del database è 110 o superiore. Vedere Modifiche di rilievo apportate alle funzionalità del motore di database in SQL Server 2016.

Le linee guida seguenti sono valide per l'utilizzo delle espressioni di tabella comuni ricorsive.

  • Tutte le colonne restituite dalla CTE ricorsiva ammettono valori Null a prescindere dal supporto dei valori Null delle colonne restituite dalle istruzioni SELECT coinvolte.

  • Un CTE ricorsivo composto in modo errato può causare un ciclo infinito. 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. Per evitare un ciclo infinito, è possibile limitare il numero di livelli di ricorsione consentiti per una determinata istruzione usando l'hint MAXRECURSION e un valore tra 0 e 32767 nella OPTION clausola dell'istruzione INSERT, UPDATE, DELETEo SELECT . Ciò consente di controllare l'esecuzione dell'istruzione fino a quando non viene risolto il problema relativo al codice che sta creando il ciclo. Il valore predefinito per l'intero server è 100. Se è specificato 0, non viene applicato alcun limite. È possibile specificare solo un valore MAXRECURSION per istruzione. Per ulteriori informazioni, vedere i suggerimenti di query .

  • Non è possibile usare una vista che contiene un'espressione di tabella comune ricorsiva per aggiornare i dati.

  • I cursori possono essere definiti nelle query che usano le TTE. La CTE corrisponde all'argomento select_statement che definisce il set di risultati del cursore. Sono consentiti solo i cursori fast forward only e statici (snapshot) per le CTE ricorsive. Se viene specificato un altro tipo di cursore in una CTE ricorsiva, il tipo di cursore viene convertito in statico.

  • L'espressione CTE può fare riferimento alle tabelle nei server remoti. 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. Se si tratta di una query CTE, gli Spool di indice/Spool lazy vengono visualizzati nel piano di query e avranno il predicato aggiuntivo WITH STACK . Questo è uno dei modi utilizzati per confermare una ricorsione appropriata.

  • 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. 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. Per altre informazioni, vedere l'esempio I, Uso di funzioni analitiche in un'espressione CTE ricorsiva, più avanti.

Espressioni di tabella comuni in Azure Synapse Analytics e piattaforma di analisi (PDW)

L'implementazione corrente delle CTE in Azure Synapse Analytics e nel sistema della piattaforma di analisi (PDW) presenta le funzionalità e i requisiti seguenti:

  • Una CTE può essere specificata in un'istruzione SELECT.

  • Una CTE può essere specificata in un'istruzione CREATE VIEW.

  • Una CTE può essere specificata in un'istruzione CREATE TABLE AS SELECT (CTAS).

  • Una CTE può essere specificata in un'istruzione CREATE REMOTE TABLE AS SELECT (CRTAS).

  • Una CTE può essere specificata in un'istruzione CREATE EXTERNAL TABLE AS SELECT (CETAS).

  • Una CTE può fare riferimento a una tabella remota.

  • Una CTE può fare riferimento a una tabella esterna.

  • In una CTE è possibile definire più query CTE.

  • Un CTE può essere seguito da SELECTistruzioni , INSERTUPDATE, DELETE, o MERGE .

  • Le espressioni di tabella comuni che includono riferimenti a se stesse (espressioni di tabella comuni ricorsive) non sono supportate.

  • Non è consentito specificare più di una clausola WITH in una CTE. 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.

  • Non è possibile usare una ORDER BY clausola nel CTE_query_definition, tranne quando viene specificata una TOP clausola .

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

  • Se usato nelle istruzioni preparate da sp_prepare, le CTE si comportano allo stesso modo di altre SELECT istruzioni in APS PDW. Tuttavia, se le CTE vengono usate come parte di CETAS preparate da sp_prepare, il comportamento può rinviare da SQL Server e altre istruzioni PDW APS a causa del modo in cui l'associazione viene implementata per sp_prepare. Se SELECT il CTE fa riferimento a una colonna errata che non esiste in CTE, passa sp_prepare senza rilevare l'errore, ma l'errore viene generato durante sp_execute .

Esempi

R. Creare un'espressione di tabella comune

Nell'esempio seguente viene illustrato il numero totale di ordini di vendita all'anno per tutti i venditori di Adventure 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. Usare un'espressione di tabella comune per limitare il numero medio di ordini

Nell'esempio seguente viene illustrato il numero medio di ordini di vendita all'anno per i venditori.

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. Usare più definizioni CTE in una singola query

Nell'esempio seguente viene illustrato come definire più di una CTE in una singola query. Viene usata una virgola per separare le definizioni di query CTE. La FORMAT funzione, usata per visualizzare gli importi monetari in un formato di valuta, è stata introdotta in SQL Server 2012 (11,x).

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;

Di seguito è riportato un set di risultati parziale.

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. Usare un'espressione di tabella comune ricorsiva per visualizzare più livelli di ricorsione

Nell'esempio seguente viene illustrato l'elenco gerarchico dei responsabili e dei dipendenti a loro subordinati. L'esempio inizia con la creazione e il popolamento della tabella dbo.MyEmployees.

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

Usare un'espressione di tabella comune ricorsiva per visualizzare due livelli di ricorsione

Nell'esempio seguente vengono illustrati i responsabili e i dipendenti che sono loro subordinati. Il numero di livelli restituiti è limitato a due.

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;

Usare un'espressione di tabella comune ricorsiva per visualizzare un elenco gerarchico

Nell'esempio seguente vengono aggiunti i nomi del responsabile e dei dipendenti e i loro rispettivi titoli. La gerarchia dei responsabili e dei dipendenti viene inoltre evidenziata rientrando ogni livello.

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;

Usare MAXRECURSION per annullare un'istruzione

È possibile utilizzare MAXRECURSION per impedire che una CTE ricorsiva non corretta provochi un ciclo infinito. 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.

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

Dopo aver corretto l'errore di codifica, MAXRECURSION non è più necessario. Nell'esempio seguente viene illustrato il codice corretto.

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. Usare un'espressione di tabella comune per analizzare in maniera selettiva una relazione ricorsiva in un'istruzione SELECT

Nell'esempio seguente viene illustrata la gerarchia di assembly e componenti del prodotto che sono necessari per costruire la bicicletta per ProductAssemblyID = 800.

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. Usare una CTE ricorsiva in un'istruzione UPDATE

Nell'esempio seguente viene aggiornato il PerAssemblyQty valore per tutte le parti usate per compilare il prodotto '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. Vengono modificate solo le righe restituite dall'espressione di tabella comune.

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. Usare più membri ricorsivi e non ricorsivi

Nell'esempio seguente vengono utilizzati più membri ricorsivi e non ricorsivi per restituire tutti gli antenati di una specifica persona. Viene creata una tabella e vengono inseriti i valori per stabilire l'albero genealogico restituito dalla CTE ricorsiva.

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

Io. Usare funzioni analitiche in un'espressione CTE ricorsiva

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.

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;

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

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. Per ognuna delle iterazioni della parte ricorsiva della query, viene passata solo una riga a ROWNUMBER.

Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)

J. Usare un'espressione di tabella comune all'interno di un'istruzione CTAS

L'esempio seguente crea una nuova tabella contenente il numero totale di ordini di vendita all'anno per tutti i venditori di Adventure Works Cycles.

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

Okay. Usare un'espressione di tabella comune all'interno di un'istruzione CETAS

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

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 Usare più CTE delimitate da virgole in un'istruzione

L'esempio seguente illustra come includere due CTE all'interno di un'unica istruzione. Le CTE non possono essere annidate (la ricorsione non è consentita).

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;

Contenuto correlato