The Običajni tabelarni izrazi (CTE) so bili uvedeni v standardni SQL, da bi poenostavili različne razrede poizvedb SQL, za katere je bila izpeljana tabela preprosto neprimerna. CTE je bil predstavljen v SQL Server 2005, izraz skupne tabele (CTE) je začasen poimenovan niz rezultatov, na katerega se lahko sklicujete znotraj IZBERI , VSTAVI , NADGRADNJA , oz IZBRIŠI izjava. CTE lahko uporabite tudi v pogledu CREATE kot del poizvedbe SELECT v pogledu. Poleg tega lahko od SQL Server 2008 novemu stavku MERGE dodate CTE.

Zakaj potrebujemo CTE v strežniku SQL?

Skupna tabela [zastarelo]CTE je zmogljiva SQL konstrukt, ki pomaga poenostaviti poizvedbe. CTE delujejo kot navidezne tabele (z zapisi in stolpci), ki so ustvarjene med izvajanjem poizvedbe, uporabljene v poizvedbi in izbrisane po izvedbi poizvedbe.

Uporaba CTE

CTE lahko definiramo tako, da dodamo stavek WITH neposredno pred stavek SELECT, INSERT, UPDATE, DELETE ali MERGE. Klavzula WITH lahko vključuje enega ali več CTE-jev, ločenih z vejicami.

java typeof spremenljivka

Sintaksa:

[Z [, …]]

::=

velikost pisave iz lateksa

ime_cte [(ime_stolpca [, …])]

AS (cte_query)

Prepir

1. Ime izraza: Veljaven identifikator za izraz skupne tabele. Ime_izraza se mora razlikovati od imen drugih običajnih izrazov tabele, definiranih v istem členu WITH, vendar je lahko ime_izraza enako imenu osnovne tabele ali pogleda. Vse reference na ime_izraza v poizvedbi uporabljajo izraz skupne tabele namesto osnovnega objekta.
2. Ime stolpca: Podaja ime stolpca v skupnem izrazu tabele. Podvojena imena znotraj ene same definicije CTE niso dovoljena. Število imen stolpcev se mora ujemati s številom stolpcev v nizu rezultatov CTE_query_definition. Seznam imen stolpcev ni obvezen le, če imajo vsi nastali stolpci v definiciji poizvedbe različna imena.
3. CTE_QueryDefinition: Podaja stavek SELECT, katerega niz rezultatov ustreza skupnemu izrazu tabele. Stavek SELECT za CTE_query_defining mora izpolnjevati enake zahteve kot ustvarjanje pogleda, le da CTE ne more definirati drugega CTE. Za več informacij glejte razdelek Opombe in CREATE VIEW (Transact-SQL). Če je definiranih več CTE_query_settings, morajo biti definicije poizvedbe kombinirane z enim od nastavljenih operatorjev UNION ALL, UNION, EXCEPT ali INTERSECT.
   

Pravila za definiranje in uporabo rekurzivnih skupnih tabelnih izrazov

Naslednje smernice veljajo za definiranje izrazov rekurzivne skupne tabele:

1. Rekurzivna definicija CTE mora vsebovati vsaj dve definiciji poizvedbe CTE, sidrnega člana in rekurzivnega člana. Definirate lahko več sidrnih in rekurzivnih članov. Vendar morajo biti vse definicije poizvedbe sidrnega člana postavljene pred prvo definicijo rekurzivnega člana. Vse definicije poizvedb CTE so sidrni člani, razen če se sklicujejo na sam CTE.
2. Sidrne elemente je treba kombinirati z enim od naslednjih operatorjev niza: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL je edini veljaven operator niza med zadnjim sidrnim članom in prvim rekurzivnim članom pri združevanju več rekurzivnih članov. Sidrni in rekurzivni elementi morajo imeti enako število stolpcev.
3. Podatkovni tip stolpcev rekurzivnega člana mora biti enak podatkovnemu tipu ustreznega stolpca sidrnega člana.
4. Naslednji elementi niso dovoljeni v njeni CTE_query_definiciji za rekurzivne člane:

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

Ustvarjanje izraza rekurzivne skupne tabele

Rekurzivni CTE je tisti, ki se sklicuje na sebe znotraj tega CTE. Rekurzivni CTE je uporaben pri delu s hierarhičnimi podatki, saj se CTE nadaljuje, dokler poizvedba ne vrne celotne hierarhije.

Tipičen primer hierarhičnih podatkov je tabela, ki vključuje seznam zaposlenih. Za vsakega zaposlenega je v tabeli naveden sklic na vodjo te osebe. Ta sklic je sam ID zaposlenega v isti tabeli. Za prikaz hierarhije podatkov o zaposlenih lahko uporabite rekurzivni CTE.

Če je CTE ustvarjen nepravilno, lahko vstopi v neskončno zanko. Da bi to preprečili, lahko dodate namig MAXRECURSION v klavzulo OPTION primarnega stavka SELECT, INSERT, UPDATE, DELETE ali MERGE.

Ustvari se tabela:

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

Ko je tabela Employees ustvarjena, je ustvarjen naslednji stavek SELECT, pred katerim je stavek WITH, ki vključuje CTE z imenom cteReports:

seznam java

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>

Tako so CTE lahko uporabni, ko morate ustvariti začasne nize rezultatov, do katerih lahko dostopate v stavku SELECT, INSERT, UPDATE, DELETE ali MERGE.

Funkcije in omejitve običajnih tabelnih izrazov v Azure

Trenutna implementacija CTE-jev v storitvi Azure Synapse Analytics in Analytics Platform System (PDW) ima naslednje funkcije in omejitve:

1. CTE je mogoče podati samo v stavku SELECT.
2. CTE je mogoče podati le v stavku CREATE VIEW.
3. CTE je mogoče podati samo v stavku CREATE TABLE AS SELECT (CTAS).
4. CTE je mogoče podati samo v stavku CREATE REMOTE TABLE AS SELECT (CRTAS).
5. CTE je mogoče podati le v stavku CREATE EXTERNAL TABLE AS SELECT (CETAS).