TechCodeview

Naudosime SQL serverio bendrąsias lentelės išraiškas arba CTE, kad palengvintume sudėtingus sujungimus ir antrines užklausas. Tai taip pat yra būdas pateikti užklausą hierarchiniams duomenims, pvz., organizacijos hierarchijai. Šiame straipsnyje pateikiama išsami CTE apžvalga, CTE tipai, privalumai, trūkumai ir kaip juos naudoti SQL serveryje.

Kas yra CTE SQL serveryje?

CTE (bendra lentelės išraiška) yra vienkartinis rezultatų rinkinys, kuris egzistuoja tik užklausos metu . Tai leidžia mums nurodyti duomenis vienoje SELECT, INSERT, UPDATE, DELETE, CREATE VIEW arba MERGE sakinio vykdymo srityje. Tai laikina, nes jo rezultato niekur negalima saugoti ir jis bus prarastas, kai tik bus įvykdyta užklausa. Pirmą kartą jis buvo su SQL Server 2005 versija. DBA visada pirmenybę teikė CTE naudoti kaip papildomos užklausos / rodinio alternatyvą. Jie atitinka ANSI SQL 99 standartą ir yra suderinami su SQL.

CTE sintaksė SQL serveryje

CTE sintaksėje yra CTE pavadinimas, pasirenkamas stulpelių sąrašas ir teiginys / užklausa, apibrėžianti bendrąją lentelės išraišką (CTE). Apibrėžę CTE, galime naudoti jį kaip rodinį SELECT, INSERT, UPDATE, DELETE ir MERGE užklausose.

Toliau pateikiama pagrindinė CTE sintaksė SQL serveryje:

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

Šioje sintaksėje:

• Pirmiausia nurodėme CTE pavadinimą, kuris bus nurodytas vėliau užklausoje.
• Kitas žingsnis – sukurti kableliais atskirtų stulpelių sąrašą. Tai užtikrina, kad stulpelių skaičius CTE apibrėžimo argumentuose ir stulpelių skaičius užklausoje turi būti vienodi. Jei neapibrėžėme CTE argumentų stulpelių, jis naudos užklausos stulpelius, kurie apibrėžia CTE.
• Po to po išraiškos pavadinimo naudosime raktinį žodį AS ir tada apibrėžsime SELECT teiginį, kurio rezultatų rinkinys užpildo CTE.
• Galiausiai CTE pavadinimą naudosime tokiose užklausose kaip SELECT, INSERT, UPDATE, DELETE ir MERGE.

Reikėtų to nepamiršti rašant CTE užklausos apibrėžimą; negalime naudoti šių sąlygų:

1. UŽSAKYTI PAGAL, ​​nebent taip pat naudojate kaip TOP sąlygą
2. Į
3. OPTION sąlyga su užklausos patarimais
4. NARŠYTI

Toliau pateiktame paveikslėlyje pavaizduotas CTE užklausos apibrėžimas.

Čia pirmoji dalis yra CTE išraiška, kurioje yra SQL užklausa, kurią galima paleisti nepriklausomai SQL. Antroji dalis yra užklausa, kuri naudoja CTE rezultatui rodyti.

Pavyzdys

Leiskite mums suprasti, kaip CTE veikia SQL serveryje, naudodami įvairius pavyzdžius. Čia mes naudosime lentelę ' klientas ' demonstracijai. Tarkime, kad šioje lentelėje yra šie duomenys:

Šiame pavyzdyje CTE pavadinimas yra klientai_niujorke , antrinė užklausa, apibrėžianti CTE, pateikia tris stulpelius kliento vardas, el. ir valstybė . Dėl to CTE customers_in_newyork grąžins visus Niujorko valstijoje gyvenančius klientus.

Apibrėžę CTE customers_in_newyork, mes jį nurodėme PASIRINKTI pareiškimą, kad gautumėte išsamią informaciją apie tuos klientus, kurie yra Niujorke.

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

Įvykdęs aukščiau pateiktą teiginį, jis duos tokią išvestį. Čia matome, kad rezultatas pateikia tik tą klientų informaciją, kurie yra Niujorko valstijoje.

Keli CTE

Kai kuriais atvejais turėsime sukurti kelias CTE užklausas ir jas sujungti, kad pamatytume rezultatus. Šiame scenarijuje galime naudoti kelių CTE koncepciją. Norėdami sukurti kelias CTE užklausas ir sujungti jas į vieną teiginį, turime naudoti kablelio operatorių. Prieš kablelio operatorių „,“ turi būti nurodytas CTE pavadinimas, kad būtų galima atskirti kelis CTE.

Keli CTE padeda mums supaprastinti sudėtingas užklausas, kurios galiausiai sujungiamos. Kiekvienas sudėtingas gabalas turėjo savo CTE, kurį vėliau buvo galima nurodyti ir sujungti ne WITH sąlygoje.

PASTABA: kelių CTE apibrėžimą galima apibrėžti naudojant UNION, UNION ALL, JOIN, INTERSECT arba EXCEPT.

Toliau pateikta sintaksė tai paaiškina aiškiau:

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

Pavyzdys

Leiskite mums suprasti, kaip keli CTE veikia SQL serveryje. Čia mes naudosime aukščiau pateiktą klientas “ stalas demonstracijai.

Šiame pavyzdyje apibrėžėme du CTE pavadinimus klientai_niujorke ir klientai_kalifornijoje . Tada šių CTE antrinių užklausų rezultatų rinkinys užpildo CTE. Galiausiai, mes naudosime CTE pavadinimus užklausoje, kuri pateiks visus klientus, esančius šalyje Niujorkas ir Kalifornijos valstija .

 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; 

Niujorkas ir Kalifornijos valstija.

Kodėl mums reikia CTE?

Kaip ir duomenų bazės rodiniai ir išvestinės lentelės, CTE gali palengvinti sudėtingų užklausų rašymą ir tvarkymą, nes jos tampa lengviau skaitomos ir paprastesnės. Šią savybę galime pasiekti suskaidydami sudėtingas užklausas į paprastus blokus, kuriuos galima pakartotinai panaudoti perrašant užklausą.

Kai kurie jo naudojimo atvejai pateikiami žemiau:

• Tai naudinga, kai reikia kelis kartus apibrėžti išvestinę lentelę vienoje užklausoje.
• Tai naudinga, kai duomenų bazėje reikia sukurti alternatyvą rodiniui.
• Tai naudinga, kai tą patį skaičiavimą reikia atlikti kelis kartus keliuose užklausos komponentuose vienu metu.
• Tai naudinga, kai reikia naudoti tokias reitingavimo funkcijas kaip ROW_NUMBER(), RANK() ir NTILE().

Kai kurie jo pranašumai pateikiami žemiau:

šrifto dydis lateksas

• CTE palengvina kodo priežiūrą.
• CTE padidina kodo skaitomumą.
• Tai padidina užklausos našumą.
• CTE leidžia lengvai įgyvendinti rekursines užklausas.

CTE tipai SQL serveryje

SQL serveris padalija CTE (bendrąsias lentelės išraiškas) į dvi plačias kategorijas:

1. Rekursinis CTE
2. Nerekursinis CTE

Rekursyvus CTE

Įprasta lentelės išraiška yra žinoma kaip rekursinis CTE, kuri nurodo save. Jo koncepcija pagrįsta rekursija, kuri apibrėžiama kaip ' pakartotinis rekursinio proceso ar apibrėžimo taikymas .' Kai vykdome rekursinę užklausą, ji pakartotinai kartojama per duomenų poaibį. Tai tiesiog apibrėžiama kaip užklausa, kuri iškviečia save. Tam tikru momentu yra pabaigos sąlyga, todėl ji nevadina savęs be galo.

Rekursyvus CTE turi turėti a SĄJUNGOS VISI teiginį ir antrą užklausos apibrėžimą, kuris nurodo patį CTE, kad būtų rekursyvus.

Pavyzdys

Leiskite mums suprasti, kaip rekursinis CTE veikia SQL serveryje. Apsvarstykite toliau pateiktą teiginį, kuris generuoja pirmųjų penkių nelyginių skaičių seriją:

 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 pateiks tokią išvestį, kurioje pamatysime darbuotojų duomenų hierarchiją:

Nerekursinis CTE

Įprasta lentelės išraiška, kuri nenurodo savęs, yra žinoma kaip nerekursyvus CTE. Nerekursyvus CTE yra paprastas ir lengviau suprantamas, nes jame nenaudojama rekursijos sąvoka. Pagal CTE sintaksę kiekviena CTE užklausa prasidės „ Su ' sąlyga, po kurios nurodomas CTE pavadinimas ir stulpelių sąrašas, tada AS su skliaustais.

CTE trūkumai

Toliau pateikiami CTE naudojimo SQL serveryje apribojimai:

• CTE nariai negali naudoti raktinių žodžių, pvz., Išskirtinis, Grupė, Turėjimas, Top, Prisijungimai ir kt.
• Rekursyvusis narys CTE gali nurodyti tik vieną kartą.
• Negalime naudoti lentelės kintamųjų ir CTE kaip saugomų procedūrų parametrų.
• Jau žinome, kad CTE gali būti naudojamas vietoje rodinio, tačiau CTE negalima įdėti, o rodiniai gali būti.
• Kadangi tai tik užklausos arba antrinės užklausos nuoroda, jos negalima pakartotinai naudoti kitoje užklausoje.
• Stulpelių skaičius CTE argumentuose ir stulpelių skaičius užklausoje turi būti vienodi.