logo

TechCodeview

CTE SQL

The Kopējās tabulu izteiksmes (CTE) tika ieviesti standarta SQL, lai vienkāršotu dažādas SQL vaicājumu klases, kurām atvasinātā tabula vienkārši nebija piemērota. CTE tika ieviesta SQL Server 2005, kopējā tabulas izteiksme (CTE) ir pagaidu nosaukta rezultātu kopa, uz kuru varat atsaukties ATLASĪT , IEVIETOT , ATJAUNINĀT , vai DZĒST paziņojums, apgalvojums. Varat arī izmantot CTE skatā CREATE kā daļu no skata SELECT vaicājuma. Turklāt, sākot ar SQL Server 2008, varat pievienot CTE jaunajam MERGE priekšrakstam.

Kāpēc mums ir nepieciešams CTE SQL serverī?

Kopējā tabula [novecojis]CTE ir spēcīgs SQL konstrukcija, kas palīdz vienkāršot vaicājumus. CTE darbojas kā virtuālas tabulas (ar ierakstiem un kolonnām), kas tiek izveidotas vaicājuma izpildes laikā, tiek izmantotas vaicājumā un tiek izdzēstas pēc vaicājuma izpildes.

Izmantojot CTE

Mēs varam definēt CTE, pievienojot klauzulu WITH tieši pirms priekšraksta SELECT, INSERT, UPDATE, DELETE vai MERGE. WITH klauzula var ietvert vienu vai vairākus CTE, kas atdalīti ar komatiem.



Linux īsceļi

Sintakse:

[AR [, …]]

::=

kolekcija java

cte_name [(kolonnas_nosaukums [, …])]

AS (cte_query)

Arguments

  1. Izteiksmes nosaukums: Derīgs identifikators kopējai tabulas izteiksmei. Izteiksmei_nosaukums ir jāatšķiras no citu parasto tabulu izteiksmju nosaukumiem, kas definēti tajā pašā klauzulā WITH, taču izteiksmes_nosaukums var būt tāds pats kā bāzes tabulas vai skata nosaukums. Visas atsauces uz izteiksmes_nosaukums vaicājumā izmanto parasto tabulas izteiksmi, nevis pamata objektu.
  2. Kolonnas nosaukums: Norāda kolonnas nosaukumu kopējā tabulas izteiksmē. Dublēti nosaukumi vienā CTE definīcijā nav atļauti. Kolonnu nosaukumu skaitam ir jāatbilst kolonnu skaitam CTE_query_definition rezultātu kopā. Kolonnu nosaukumu saraksts nav obligāts tikai tad, ja vaicājuma definīcijā visām iegūtajām kolonnām ir atšķirīgi nosaukumi.
  3. CTE_QueryDefinition: Norāda SELECT priekšrakstu, kura rezultātu kopa atbilst kopējai tabulas izteiksmei. CTE_query_defining priekšrakstam SELECT ir jāatbilst tādām pašām prasībām kā skata izveidei, izņemot to, ka CTE nevar definēt citu CTE. Lai iegūtu papildinformāciju, skatiet sadaļu Piezīmes un IZVEIDOT SKATU (Transact-SQL). Ja ir definēti vairāki CTE_query_settings, vaicājuma definīcijas ir jāapvieno ar vienu no kopas operatoriem UNION ALL, UNION, EXCEPT vai INTERSECT.

Rekursīvo kopējo tabulu izteiksmju definēšanas un lietošanas noteikumi

Šīs vadlīnijas attiecas uz rekursīvu kopīgu tabulu izteiksmju definēšanu:

  1. Rekursīvajā CTE definīcijā ir jāietver vismaz divas CTE vaicājuma definīcijas, enkura elements un rekursīvs elements. Varat definēt vairākus enkura un rekursīvos dalībniekus. Tomēr visas enkura dalībnieku vaicājuma definīcijas ir jāievieto pirms pirmās rekursīvās dalībnieka definīcijas. Visas CTE vaicājumu definīcijas ir enkura dalībnieki, ja vien tās neatsaucas uz pašu CTE.
  2. Enkura elementi jāapvieno ar vienu no šiem kopas operatoriem: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL ir vienīgais derīgais kopas operators starp pēdējo enkura locekli un pirmo rekursīvo dalībnieku, savienojot vairākus rekursīvos dalībniekus. Enkura un rekursīvajiem elementiem jābūt vienādam kolonnu skaitam.
  3. Rekursīvā elementa kolonnu datu tipam ir jābūt tādam pašam kā enkura elementa atbilstošās kolonnas datu tipam.
  4. Viņas CTE_query_definition rekursīviem dalībniekiem nav atļauti šādi vienumi:
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.>

Rekursīvas kopējas tabulas izteiksmes izveide

Rekursīvs CTE ir tāds, kas atsaucas uz sevi šajā CTE. Rekursīvais CTE ir noderīgs, strādājot ar hierarhiskiem datiem, jo ​​CTE turpina izpildīt, līdz vaicājums atgriež visu hierarhiju.

Tipisks hierarhijas datu piemērs ir tabula, kurā ir iekļauts darbinieku saraksts. Katram darbiniekam tabulā ir sniegta atsauce uz šīs personas vadītāju. Šī atsauce pati par sevi ir darbinieka ID tajā pašā tabulā. Varat izmantot rekursīvo CTE, lai parādītu darbinieku datu hierarhiju.

Ja CTE ir izveidots nepareizi, tas var iekļūt bezgalīgā cilpā. Lai to novērstu, MAXRECURSION mājienu var pievienot primārā priekšraksta SELECT, INSERT, UPDATE, DELETE vai MERGE klauzulā OPTION.

Tiek izveidota tabula: 

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

Pēc tabulas Darbinieki izveidošanas tiek izveidots šāds SELECT priekšraksts, pirms kura ir WITH klauzula, kas ietver CTE ar nosaukumu cteReports:

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

Tādējādi CTE var būt noderīgi, ja nepieciešams ģenerēt pagaidu rezultātu kopas, kurām var piekļūt, izmantojot priekšrakstu SELECT, INSERT, UPDATE, DELETE vai MERGE.

Azure parasto tabulu izteiksmju līdzekļi un ierobežojumi

Pašreizējai CTE ieviešanai Azure Synapse Analytics un Analytics platformas sistēmā (PDW) ir šādas funkcijas un ierobežojumi.

  1. CTE var norādīt tikai priekšrakstā SELECT.
  2. CTE var norādīt tikai priekšrakstā CREATE VIEW.
  3. CTE var norādīt tikai priekšrakstā CREATE TABLE AS SELECT (CTAS).
  4. CTE var norādīt tikai priekšrakstā CREATE REMOTE TABLE AS SELECT (CRTAS).
  5. CTE var norādīt tikai priekšrakstā CREATE EXTERNAL TABLE AS SELECT (CETAS).