IL Espressioni di tabella comuni (CTE) sono stati introdotti nell'SQL standard per semplificare varie classi di query SQL per le quali una tabella derivata era semplicemente inadatta. CTE è stata introdotta in SQL Server 2005, l'espressione di tabella comune (CTE) è un set di risultati denominato temporaneo a cui è possibile fare riferimento all'interno di un SELEZIONARE , INSERIRE , AGGIORNAMENTO , O ELIMINARE dichiarazione. Puoi anche utilizzare un CTE in una vista CREATE, come parte della query SELECT della vista. Inoltre, a partire da SQL Server 2008, è possibile aggiungere una CTE alla nuova istruzione MERGE.

Perché abbiamo bisogno di CTE nel server SQL?

Una tabella comune [deprecata]CTE è potente SQL costrutto che aiuta a semplificare le query. Le CTE fungono da tabelle virtuali (con record e colonne) create durante l'esecuzione della query, utilizzate dalla query ed eliminate dopo l'esecuzione della query.

Utilizzando il CTE

Possiamo definire CTE aggiungendo una clausola WITH direttamente prima dell'istruzione SELECT, INSERT, UPDATE, DELETE o MERGE. La clausola WITH può includere uno o più CTE separati da virgole.

moltiplicazione di matrici in c

Sintassi:

[CON [, …]]

::=

nome_cte [(nome_colonna [, …])]

AS (cte_query)

Discussione

1. Nome dell'espressione: Un identificatore valido per un'espressione di tabella comune. Il nome_espressione deve essere diverso dai nomi di altre espressioni di tabella comuni definite nella stessa clausola WITH, ma il nome_espressione può essere uguale al nome della tabella o vista di base. Tutti i riferimenti a nome_espressione nella query utilizzano l'espressione di tabella comune anziché l'oggetto di base.
2. Nome della colonna: Specifica un nome di colonna in un'espressione di tabella comune. Non sono consentiti nomi duplicati all'interno di una singola definizione CTE. Il numero di nomi di colonna deve corrispondere al numero di colonne nel set di risultati di CTE_query_definition. Un elenco di nomi di colonna è facoltativo solo se tutte le colonne risultanti nella definizione della query hanno nomi diversi.
3. CTE_QueryDefinition: Specifica un'istruzione SELECT il cui set di risultati soddisfa un'espressione di tabella comune. L'istruzione SELECT per CTE_query_defining deve soddisfare gli stessi requisiti della creazione di una vista, tranne per il fatto che una CTE non può definire un'altra CTE. Per ulteriori informazioni, vedere la sezione Osservazioni e CREATE VIEW (Transact-SQL). Se vengono definiti più CTE_query_settings, le definizioni della query devono essere combinate con uno degli operatori di set UNION ALL, UNION, EXCEPT o INTERSECT.
   

Regole per la definizione e l'uso delle espressioni di tabella comuni ricorsive

Le seguenti linee guida si applicano alla definizione di espressioni di tabella comuni ricorsive:

1. Una definizione CTE ricorsiva deve contenere almeno due definizioni di query CTE, un membro di ancoraggio e un membro ricorsivo. È possibile definire più membri di ancoraggio e ricorsivi. Tuttavia, tutte le definizioni di query dei membri di ancoraggio devono essere inserite prima della prima definizione di membro ricorsivo. Tutte le definizioni delle query CTE sono membri di ancoraggio a meno che non facciano riferimento alla CTE stessa.
2. Gli elementi di ancoraggio devono essere combinati con uno dei seguenti operatori di insieme: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL è l'unico operatore di insieme valido tra l'ultimo membro di ancoraggio e il primo membro ricorsivo quando si uniscono più membri ricorsivi. Gli elementi di ancoraggio e ricorsivi devono avere lo stesso numero di colonne.
3. Il tipo di dati delle colonne del membro ricorsivo deve essere uguale al tipo di dati della colonna corrispondente del membro di ancoraggio.
4. I seguenti elementi non sono consentiti nella sua CTE_query_definition per i membri ricorsivi:

Please choose a different grouping PIVOT (For database compatibility level 110 and above.  See Breaking Changes to Database Engine Features in SQL Server 2016. Offal Scalar aggregation Up LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) subquery A hint applied to recursive references to CTEs within a CTE_query_definition.>

Creazione di un'espressione di tabella comune ricorsiva

Una CTE ricorsiva è una CTE che fa riferimento a se stessa all'interno di quella CTE. La CTE ricorsiva è utile quando si lavora con dati gerarchici poiché la CTE continua l'esecuzione finché la query non restituisce l'intera gerarchia.

convenzioni di denominazione Java

Un tipico esempio di dati gerarchici è una tabella che include un elenco di dipendenti. Per ogni dipendente, la tabella fornisce un riferimento al manager di quella persona. Tale riferimento è esso stesso un ID dipendente all'interno della stessa tabella. È possibile utilizzare una CTE ricorsiva per visualizzare la gerarchia dei dati dei dipendenti.

Se una CTE viene creata in modo errato può entrare in un ciclo infinito. Per evitare ciò, è possibile aggiungere l'hint MAXRECURSION nella clausola OPTION dell'istruzione primaria SELECT, INSERT, UPDATE, DELETE o MERGE.

Viene creata una tabella:

CREATE TABLE Employees (  EmployeeID int NOT NULL PRIMARY KEY,  FirstName varchar(50) NOT NULL,  LastName varchar(50) NOT NULL,  ManagerID int NULL )  INSERT INTO Employees VALUES (1, 'Ken', 'Thompson', NULL) INSERT INTO Employees VALUES (2, 'Terri', 'Ryan', 1) INSERT INTO Employees VALUES (3, 'Robert', 'Durello', 1) INSERT INTO Employees VALUES (4, 'Rob', 'Bailey', 2) INSERT INTO Employees VALUES (5, 'Kent', 'Erickson', 2) INSERT INTO Employees VALUES (6, 'Bill', 'Goldberg', 3) INSERT INTO Employees VALUES (7, 'Ryan', 'Miller', 3) INSERT INTO Employees VALUES (8, 'Dane', 'Mark', 5) INSERT INTO Employees VALUES (9, 'Charles', 'Matthew', 6) INSERT INTO Employees VALUES (10, 'Michael', 'Jhonson', 6)>

Dopo aver creato la tabella Employees, viene creata la seguente istruzione SELECT, preceduta da una clausola WITH che include un CTE denominato cteReports:

WITH  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)  AS  (  SELECT EmployeeID, FirstName, LastName, ManagerID, 1  FROM Employees  WHERE ManagerID IS NULL  UNION ALL  SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,   r.EmpLevel + 1  FROM Employees e  INNER JOIN cteReports r  ON e.ManagerID = r.EmpID  ) SELECT  FirstName + ' ' + LastName AS FullName,   EmpLevel,  (SELECT FirstName + ' ' + LastName FROM Employees   WHERE EmployeeID = cteReports.MgrID) AS Manager FROM cteReports  ORDER BY EmpLevel, MgrID>

Pertanto, le CTE possono essere utili quando è necessario generare set di risultati temporanei a cui è possibile accedere in un'istruzione SELECT, INSERT, UPDATE, DELETE o MERGE.

potatura a-b

Funzionalità e limitazioni delle espressioni di tabella comuni in Azure

L'attuale implementazione di CTE in Azure Synapse Analytics and Analytics Platform System (PDW) presenta le seguenti funzionalità e limitazioni:

1. Una CTE può essere specificata solo in un'istruzione SELECT.
2. Una CTE può essere specificata solo in un'istruzione CREATE VIEW.
3. Una CTE può essere specificata solo in un'istruzione CREATE TABLE AS SELECT (CTAS).
4. Una CTE può essere specificata solo in un'istruzione CREATE REMOTE TABLE AS SELECT (CRTAS).
5. Una CTE può essere specificata solo in un'istruzione CREATE EXTERNAL TABLE AS SELECT (CETAS).