logo

TechCodeview

CTE SQL

The Bendrosios lentelės išraiškos (CTE) buvo įtrauktos į standartinį SQL, siekiant supaprastinti įvairias SQL užklausų klases, kurioms išvestinė lentelė buvo tiesiog netinkama. CTE buvo pristatyta SQL Server 2005, bendroji lentelės išraiška (CTE) yra laikinas pavadintas rezultatų rinkinys, kurį galite nurodyti PASIRINKTI , ĮDĖTI , ATNAUJINTI , arba IŠTRINTI pareiškimas. Taip pat galite naudoti CTE rodinyje CREATE, kaip rodinio SELECT užklausos dalį. Be to, nuo SQL Server 2008 galite pridėti CTE prie naujojo MERGE teiginio.

Kodėl mums reikia CTE SQL serveryje?

Įprasta lentelė [nebenaudojama]CTE yra galinga SQL konstrukcija, padedanti supaprastinti užklausas. CTE veikia kaip virtualios lentelės (su įrašais ir stulpeliais), kurios sukuriamos vykdant užklausą, naudojamos užklausoje ir ištrinamos įvykdžius užklausą.

Naudojant CTE

CTE galime apibrėžti pridėdami WITH sąlygą tiesiai prieš sakinį SELECT, INSERT, UPDATE, DELETE arba MERGE. WITH sąlyga gali apimti vieną ar daugiau CTE, atskirtų kableliais.



veikimo testavimas

Sintaksė:

[SU [, …]]

::=

cte_name [(stulpelio_pavadinimas [, …])]

AS (cte_query)

Argumentas

  1. Išraiškos pavadinimas: Tinkamas bendros lentelės išraiškos identifikatorius. Išraiškos_pavadinimas turi skirtis nuo kitų įprastų lentelės išraiškų, apibrėžtų tame pačiame WITH sakinyje, pavadinimų, tačiau išraiškos_pavadinimas gali būti toks pat kaip pagrindinės lentelės arba rodinio pavadinimas. Visose nuorodose į išraiškos_vardas užklausoje naudojama bendroji lentelės išraiška, o ne pagrindinis objektas.
  2. Stulpelio pavadinimas: Nurodo stulpelio pavadinimą bendroje lentelės išraiškoje. Pasikartojantys pavadinimai vienoje CTE apibrėžime neleidžiami. Stulpelių pavadinimų skaičius turi atitikti stulpelių skaičių CTE_query_definition rezultatų rinkinyje. Stulpelių pavadinimų sąrašas yra neprivalomas, tik jei visi užklausos apibrėžimo stulpeliai turi skirtingus pavadinimus.
  3. CTE_QueryDefinition: Nurodo SELECT sakinį, kurio rezultatų rinkinys atitinka bendrą lentelės išraišką. CTE_query_defining SELECT teiginys turi atitikti tuos pačius reikalavimus kaip ir kuriant rodinį, išskyrus tai, kad CTE negali apibrėžti kito CTE. Norėdami gauti daugiau informacijos, žr. skyrių Pastabos ir KURTI RODINĮ (Transact-SQL). Jei apibrėžti keli CTE_query_settings, užklausos apibrėžimai turi būti derinami su vienu iš rinkinio operatorių UNION ALL, UNION, EXCEPT arba INTERSECT.

Rekursinių bendrųjų lentelės išraiškų apibrėžimo ir naudojimo taisyklės

Šios rekomendacijos taikomos apibrėžiant rekursines bendrąsias lentelės išraiškas:

rekha indėnė
  1. Rekursyviniame CTE apibrėžime turi būti bent du CTE užklausos apibrėžimai: inkarinis narys ir rekursyvus narys. Galite apibrėžti kelis inkarinius ir rekursinius narius. Tačiau visi inkaro nario užklausos apibrėžimai turi būti pateikti prieš pirmąjį rekursinį nario apibrėžimą. Visi CTE užklausos apibrėžimai yra inkaro nariai, nebent jie nurodo patį CTE.
  2. Inkaro elementai turi būti derinami su vienu iš šių rinkinio operatorių: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL yra vienintelis galiojantis rinkinio operatorius tarp paskutinio inkarinio nario ir pirmojo rekursinio nario, kai sujungiami keli rekursiniai nariai. Inkariniai ir rekursiniai elementai turi turėti tiek pat stulpelių.
  3. Rekursyvaus nario stulpelių duomenų tipas turi sutapti su atitinkamo inkaro nario stulpelio duomenų tipas.
  4. Jos CTE_query_definition rekursyviems nariams neleidžiami šie elementai:
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.>

Rekursinės bendros lentelės išraiškos kūrimas

Rekursyvinis CTE yra tas, kuris nurodo save tame CTE. Rekursyvus CTE yra naudingas dirbant su hierarchiniais duomenimis, nes CTE ir toliau vykdoma tol, kol užklausa grąžina visą hierarchiją.

Tipiškas hierarchinių duomenų pavyzdys yra lentelė, kurioje yra darbuotojų sąrašas. Kiekvienam darbuotojui lentelėje pateikiama nuoroda į to asmens vadovą. Ta nuoroda pati yra darbuotojo ID toje pačioje lentelėje. Norėdami parodyti darbuotojų duomenų hierarchiją, galite naudoti rekursinį CTE.

Linux gamintojas

Jei CTE sukurtas neteisingai, jis gali patekti į begalinį ciklą. Norėdami to išvengti, užuomina MAXRECURSION gali būti įtraukta į pirminio teiginio SELECT, INSERT, UPDATE, DELETE arba MERGE sąlygą OPTION.

Sukuriama lentelė: 

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

Sukūrus lentelę Darbuotojai, sukuriamas šis SELECT sakinys, prieš kurį yra WITH sąlyga, apimanti CTE, pavadintą 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>

Taigi, CTE gali būti naudingi, kai reikia generuoti laikinus rezultatų rinkinius, kuriuos galima pasiekti naudojant teiginį SELECT, INSERT, UPDATE, DELETE arba MERGE.

„Azure“ bendrųjų lentelės išraiškų funkcijos ir apribojimai

Dabartinis CTE diegimas „Azure Synapse Analytics“ ir „Analytics Platform System“ (PDW) turi šias funkcijas ir apribojimus:

  1. CTE galima nurodyti tik SELECT sakinyje.
  2. CTE galima nurodyti tik CREATE VIEW sakinyje.
  3. CTE galima nurodyti tik CREATE TABLE AS SELECT (CTAS) sakinyje.
  4. CTE galima nurodyti tik CREATE REMOTE TABLE AS SELECT (CRTAS) sakinyje.
  5. CTE galima nurodyti tik CREATE EXTERNAL TABLE AS SELECT (CETAS) sakinyje.