The Expresii comune de tabel (CTE) au fost introduse în SQL standard pentru a simplifica diferite clase de interogări SQL pentru care un tabel derivat nu era potrivit. CTE a fost introdus în SQL Server 2005, expresia de tabel comună (CTE) este un set de rezultate temporar numit la care puteți face referire într-un SELECTAȚI , INTRODUCE , ACTUALIZAȚI , sau ȘTERGE afirmație. De asemenea, puteți utiliza un CTE într-o vizualizare CREATE, ca parte a interogării SELECT a vizualizării. În plus, începând cu SQL Server 2008, puteți adăuga un CTE la noua instrucțiune MERGE.

De ce avem nevoie de CTE în serverul SQL?

Un tabel obișnuit [depreciat] CTE este un puternic SQL construcție care ajută la simplificarea interogărilor. CTE-urile acționează ca tabele virtuale (cu înregistrări și coloane) care sunt create în timpul execuției interogării, utilizate de interogare și șterse după executarea interogării.

Folosind CTE

Putem defini CTE-uri adăugând o clauză WITH direct înainte de instrucțiunea SELECT, INSERT, UPDATE, DELETE sau MERGE. Clauza WITH poate include unul sau mai multe CTE separate prin virgule.

ce inseamna xd

Sintaxă:

[CU [, …]]

::=

nume_cte [(nume_coloană [, …])]

AS (cte_query)

convențiile de denumire java

Argument

1. Numele expresiei: Un identificator valid pentru o expresie de tabel comună. Numele_expresiei trebuie să fie diferit de numele altor expresii de tabel comune definite în aceeași clauză WITH, dar numele_expresiei poate fi același cu numele tabelului sau vizualizării de bază. Toate referințele la expression_name din interogare folosesc expresia tabelului comun în loc de obiectul de bază.
2. Numele coloanei: Specifică un nume de coloană într-o expresie de tabel comună. Nu sunt permise nume duplicate într-o singură definiție CTE. Numărul de nume de coloane trebuie să se potrivească cu numărul de coloane din setul de rezultate CTE_query_definition. O listă de nume de coloane este opțională numai dacă toate coloanele rezultate din definiția interogării au nume diferite.
3. CTE_QueryDefinition: Specifică o instrucțiune SELECT al cărei set de rezultate satisface o expresie de tabel comună. Instrucțiunea SELECT pentru CTE_query_defining trebuie să îndeplinească aceleași cerințe ca și crearea unei vizualizări, cu excepția faptului că un CTE nu poate defini un alt CTE. Pentru mai multe informații, consultați secțiunea Observații și CREATE VIEW (Transact-SQL). Dacă sunt definite mai multe CTE_query_settings, definițiile de interogare trebuie să fie combinate cu unul dintre operatorii setați UNION ALL, UNION, EXCEPT sau INTERSECT.
   

Reguli pentru definirea și utilizarea expresiilor recursive comune de tabel

Următoarele instrucțiuni se aplică pentru definirea expresiilor recursive comune de tabel:

1. O definiție recursivă CTE trebuie să conțină cel puțin două definiții de interogare CTE, un membru ancoră și un membru recursiv. Puteți defini mai mulți membri ancoră și recursivi. Cu toate acestea, toate definițiile de interogare a membrului ancoră trebuie plasate înaintea definiției primului membru recursiv. Toate definițiile de interogare CTE sunt membri ancoră, cu excepția cazului în care fac referire la CTE însuși.
2. Elementele de ancorare trebuie combinate cu unul dintre următorii operatori de set: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL este singurul operator de set valid între ultimul membru ancoră și primul membru recursiv atunci când se unesc mai mulți membri recursivi. Elementele ancora și recursive trebuie să aibă același număr de coloane.
3. Tipul de date al coloanelor membrului recursiv trebuie să fie același cu tipul de date al coloanei corespunzătoare a membrului ancora.
4. Următoarele elemente nu sunt permise în CTE_query_definition pentru membri recursivi:

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

Crearea unei expresii de tabel comune recursive

Un CTE recursiv este unul care se referă la el însuși în acel CTE. CTE recursiv este util atunci când se lucrează cu date ierarhice, deoarece CTE continuă să se execute până când interogarea returnează întreaga ierarhie.

Un exemplu tipic de date ierarhice este un tabel care include o listă de angajați. Pentru fiecare angajat, tabelul oferă o referință la managerul persoanei respective. Această referință este ea însăși un ID de angajat în cadrul aceluiași tabel. Puteți utiliza un CTE recursiv pentru a afișa ierarhia datelor angajaților.

Dacă un CTE este creat incorect, acesta poate intra într-o buclă infinită. Pentru a preveni acest lucru, indicația MAXRECURSION poate fi adăugată în clauza OPTION a instrucțiunii primare SELECT, INSERT, UPDATE, DELETE sau MERGE.

excursie dar

Se creează un tabel:

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

După ce tabelul Employees este creat, se creează următoarea instrucțiune SELECT, care este precedată de o clauză WITH care include un CTE numit 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>

Astfel, CTE-urile pot fi utile atunci când trebuie să generați seturi de rezultate temporare care pot fi accesate într-o instrucțiune SELECT, INSERT, UPDATE, DELETE sau MERGE.

Caracteristici și limitări ale expresiilor comune de tabel în Azure

Implementarea actuală a CTE-urilor în Azure Synapse Analytics and Analytics Platform System (PDW) are următoarele caracteristici și limitări:

1. Un CTE poate fi specificat doar într-o instrucțiune SELECT.
2. Un CTE poate fi specificat doar într-o instrucțiune CREATE VIEW.
3. Un CTE poate fi specificat doar într-o instrucțiune CREATE TABLE AS SELECT (CTAS).
4. Un CTE poate fi specificat doar într-o instrucțiune CREATE REMOTE TABLE AS SELECT (CRTAS).
5. Un CTE poate fi specificat doar într-o instrucțiune CREATE EXTERNAL TABLE AS SELECT (CETAS).