TechCodeview

Mēs izmantosim SQL Server kopējās tabulu izteiksmes jeb CTE, lai atvieglotu sarežģītus savienojumus un apakšvaicājumus. Tas nodrošina arī veidu, kā vaicāt hierarhiskus datus, piemēram, organizācijas hierarhiju. Šajā rakstā ir sniegts pilnīgs pārskats par CTE, CTE veidiem, priekšrocībām, trūkumiem un to izmantošanu SQL Server.

Kas ir CTE SQL serverī?

CTE (Common Table Expression) ir vienreizēja rezultātu kopa, kas pastāv tikai vaicājuma laikā. . Tas ļauj mums atsaukties uz datiem viena SELECT, INSERT, UPDATE, DELETE, CREATE VIEW vai MERGE priekšraksta izpildes tvērumā. Tas ir īslaicīgs, jo tā rezultātu nevar nekur saglabāt un tas tiks zaudēts, tiklīdz vaicājuma izpilde tiks pabeigta. Vispirms tas tika piegādāts ar SQL Server 2005 versiju. DBA vienmēr deva priekšroku CTE, lai to izmantotu kā apakšvaicājuma/skata alternatīvu. Tie atbilst ANSI SQL 99 standartam un ir saderīgi ar SQL.

CTE sintakse SQL serverī

CTE sintakse ietver CTE nosaukumu, neobligātu kolonnu sarakstu un paziņojumu/vaicājumu, kas definē kopējo tabulas izteiksmi (CTE). Pēc CTE definēšanas mēs varam to izmantot kā skatu SELECT, INSERT, UPDATE, DELETE un MERGE vaicājumā.

Tālāk ir norādīta CTE pamata sintakse SQL Server:

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name; 

Šajā sintaksē:

• Vispirms esam norādījuši CTE nosaukumu, uz kuru tiks atsaukta vēlāk vaicājumā.
• Nākamais solis ir izveidot ar komatu atdalītu kolonnu sarakstu. Tas nodrošina, ka kolonnu skaitam CTE definīcijas argumentos un kolonnu skaitam vaicājumā ir jābūt vienādam. Ja mēs neesam definējuši CTE argumentu kolonnas, tas izmantos vaicājuma kolonnas, kas definē CTE.
• Pēc tam mēs izmantosim atslēgvārdu AS aiz izteiksmes nosaukuma un pēc tam definēsim SELECT priekšrakstu, kura rezultātu kopa aizpilda CTE.
• Visbeidzot, mēs izmantosim CTE nosaukumu tādos vaicājumos kā SELECT, INSERT, UPDATE, DELETE un MERGE.

Tas jāpatur prātā, rakstot CTE vaicājuma definīciju; mēs nevaram izmantot šādas klauzulas:

1. PASŪTĪT PĒC, ja vien neizmantojat arī kā TOP klauzulu
2. INTO
3. OPTION klauzula ar vaicājuma padomiem
4. PĀRKLĀŠANAI

Tālāk redzamajā attēlā ir attēlota CTE vaicājuma definīcija.

Šeit pirmā daļa ir CTE izteiksme, kas satur SQL vaicājumu, ko var palaist neatkarīgi SQL. Un otrā daļa ir vaicājums, kas izmanto CTE, lai parādītu rezultātu.

Piemērs

Ļaujiet mums saprast, kā CTE darbojas SQL Server, izmantojot dažādus piemērus. Šeit mēs izmantosim tabulu klientu ' par demonstrāciju. Pieņemsim, ka šajā tabulā ir šādi dati:

Šajā piemērā CTE nosaukums ir klienti_njujorkā , apakšvaicājums, kas definē CTE, atgriež trīs kolonnas klienta vārds, e-pasts, un Valsts . Rezultātā CTE customers_in_newyork atgriezīs visus klientus, kas dzīvo Ņujorkas štatā.

Pēc CTE customers_in_newyork definēšanas mēs uz to atsaucāmies sadaļā ATLASĪT paziņojumu, lai iegūtu informāciju par klientiem, kuri atrodas Ņujorkā.

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork; 

Pēc iepriekš minētā paziņojuma izpildes tas sniegs šādu izvadi. Šeit mēs redzam, ka rezultāts atgriež tikai to klientu informāciju, kas atrodas Ņujorkas štatā.

Vairāki CTE

Dažos gadījumos mums būs jāizveido vairāki CTE vaicājumi un tie jāapvieno, lai redzētu rezultātus. Šajā scenārijā mēs varam izmantot vairāku CTE koncepciju. Mums ir jāizmanto komatu operators, lai izveidotu vairākus CTE vaicājumus un apvienotu tos vienā priekšrakstā. Pirms komata operatora ',' jāieraksta CTE nosaukums, lai atšķirtu vairākus CTE.

Vairāki CTE palīdz mums vienkāršot sarežģītus vaicājumus, kas galu galā tiek apvienoti. Katram sarežģītajam gabalam bija savs CTE, uz kuru pēc tam varēja atsaukties un pievienoties ārpus WITH klauzulas.

PIEZĪME. Vairāku CTE definīciju var definēt, izmantojot UNION, UNION ALL, JOIN, INTERSECT vai EXCEPT.

Tālāk sniegtā sintakse to izskaidro skaidrāk:

 WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name; 

Piemērs

Ļaujiet mums saprast, kā vairākas CTE darbojas SQL Server. Šeit mēs izmantosim iepriekš minēto klientu ' galds demonstrācijai.

Šajā piemērā mēs esam definējuši divus CTE nosaukumus klienti_njujorkā un klienti_kalifornijā . Pēc tam šo CTE apakšvaicājumu rezultātu kopa aizpilda CTE. Visbeidzot, mēs izmantosim CTE nosaukumus vaicājumā, kas atgriezīs visus klientus, kuri atrodas Ņujorka un Kalifornijas štats .

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York'), customers_in_California AS (SELECT * FROM customer WHERE state = 'California') SELECT c_name, email, state FROM customers_in_NewYork UNION ALL SELECT c_name, email, state FROM customers_in_California; 

Ņujorka un Kalifornijas štats.

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

Tāpat kā datu bāzes skati un atvasinātās tabulas, CTE var atvieglot sarežģītu vaicājumu rakstīšanu un pārvaldību, padarot tos lasāmākus un vienkāršākus. Mēs varam sasniegt šo raksturlielumu, sadalot sarežģītos vaicājumus vienkāršos blokos, kurus var atkārtoti izmantot vaicājuma pārrakstīšanai.

Daži no tā lietošanas gadījumiem ir norādīti zemāk:

• Tas ir noderīgi, ja mums ir jādefinē atvasināta tabula vairākas reizes vienā vaicājumā.
• Tas ir noderīgi, ja mums ir jāizveido alternatīva skatam datu bāzē.
• Tas ir noderīgi, ja mums ir nepieciešams veikt vienu un to pašu aprēķinu vairākas reizes vairākiem vaicājuma komponentiem vienlaikus.
• Tas ir noderīgi, ja mums ir jāizmanto ranžēšanas funkcijas, piemēram, ROW_NUMBER(), RANK() un NTILE().

Dažas no tā priekšrocībām ir norādītas zemāk:

lateksa galds

• CTE atvieglo koda apkopi.
• CTE palielina koda lasāmību.
• Tas palielina vaicājuma veiktspēju.
• CTE ļauj viegli ieviest rekursīvus vaicājumus.

CTE veidi SQL serverī

SQL Server sadala CTE (Common Table Expressions) divās plašās kategorijās:

1. Rekursīvs CTE
2. Nerekursīvs CTE

Rekursīvs CTE

Parasta tabulas izteiksme ir pazīstama kā rekursīvs CTE, kas atsaucas uz sevi. Tās koncepcijas pamatā ir rekursija, kas tiek definēta kā ' atkārtota rekursīva procesa vai definīcijas piemērošana .' Kad mēs izpildām rekursīvu vaicājumu, tas atkārtoti atkārtojas datu apakškopā. Tas ir vienkārši definēts kā vaicājums, kas izsauc sevi. Kādā brīdī ir beigu nosacījums, tāpēc tas sevi nesauc bezgalīgi.

Rekursīvajam CTE jābūt a SAVIENĪBA VISU paziņojumu un otru vaicājuma definīciju, kas atsaucas uz pašu CTE, lai tā būtu rekursīva.

Piemērs

Ļaujiet mums saprast, kā rekursīvais CTE darbojas SQL Server. Apsveriet tālāk sniegto paziņojumu, kas ģenerē pirmo piecu nepāra skaitļu sēriju:

 WITH odd_num_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>When we execute this recursive CTE, we will see the output as below:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-5.webp" alt="CTE in SQL Server"> <p>The below example is the more advanced recursive CTE. Here, we are going to use the &apos; <strong>jtp_employees</strong> &apos; table for a demonstration that contains the below data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-6.webp" alt="CTE in SQL Server"> <p>This example will display the hierarchy of employee data. Here table provides a reference to that person&apos;s manager for each employee. The reference is itself an employee id within the same table.</p> <pre> WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM jtp_employees WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1 FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID ) SELECT FirstName + &apos; &apos; + LastName AS FullName, EmpLevel, (SELECT FirstName + &apos; &apos; + LastName FROM jtp_employees WHERE EmployeeID = cte_recursion.MgrID) AS Manager FROM cte_recursion ORDER BY EmpLevel, MgrID </pre> <p>This CTE will give the following output where we can see the hierarchy of employee data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-7.webp" alt="CTE in SQL Server"> <h3>Non-Recursive CTE</h3> <p>A common table expression that doesn&apos;t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a &apos; <strong>With</strong> &apos; clause followed by the CTE name and column list, then AS with parenthesis.</p> <h2>Disadvantages of CTE</h2> <p>The following are the limitations of using CTE in SQL Server:</p> <ul> <li>CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.</li> <li>The CTE can only be referenced once by the Recursive member.</li> <li>We cannot use the table variables and CTEs as parameters in stored procedures.</li> <li>We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.</li> <li>Since it&apos;s just a shortcut for a query or subquery, it can&apos;t be reused in another query.</li> <li>The number of columns in the CTE arguments and the number of columns in the query must be the same.</li> </ul> <hr></5>

Šis CTE sniegs šādu izvadi, kurā mēs varam redzēt darbinieku datu hierarhiju:

Nerekursīvs CTE

Parasta tabulas izteiksme, kas neatsaucas uz sevi, ir pazīstama kā nerekursīva CTE. Nerekursīvs CTE ir vienkāršs un vieglāk saprotams, jo tajā netiek izmantots rekursijas jēdziens. Saskaņā ar CTE sintakse katrs CTE vaicājums sāksies ar ' Ar klauzula, kam seko CTE nosaukums un kolonnu saraksts, pēc tam AS ar iekavām.

CTE trūkumi

Tālāk ir norādīti CTE lietošanas ierobežojumi SQL Server:

• CTE dalībnieki nevar izmantot atslēgvārdu klauzulas, piemēram, Distinct, Group By, Have, Top, Joins utt.
• Rekursīvais dalībnieks uz CTE var atsaukties tikai vienu reizi.
• Mēs nevaram izmantot tabulas mainīgos un CTE kā parametrus saglabātajās procedūrās.
• Mēs jau zinām, ka CTE var izmantot skata vietā, taču CTE nevar ligzdot, savukārt Views var.
• Tā kā tas ir tikai vaicājuma vai apakšvaicājuma saīsne, to nevar atkārtoti izmantot citā vaicājumā.
• Kolonnu skaitam CTE argumentos un kolonnu skaitam vaicājumā ir jābūt vienādam.