Uporabili bomo skupne tabelne izraze ali CTE strežnika SQL Server, da bi olajšali zapletene spoje in podpoizvedbe. Ponuja tudi način za poizvedovanje po hierarhičnih podatkih, kot je organizacijska hierarhija. Ta članek ponuja popoln pregled CTE, vrste CTE, prednosti, slabosti in kako jih uporabiti v SQL Server.

Kaj je CTE v SQL Server?

CTE (Common Table Expression) je enkratni nabor rezultatov, ki obstaja samo v času trajanja poizvedbe. . Omogoča nam, da se sklicujemo na podatke znotraj obsega izvedbe stavka SELECT, INSERT, UPDATE, DELETE, CREATE VIEW ali MERGE. Je začasen, ker njegovega rezultata ni mogoče nikamor shraniti in bo izgubljen takoj, ko bo izvedba poizvedbe končana. Prvič je prišel z različico SQL Server 2005. DBA je vedno raje uporabljal CTE kot alternativo podpoizvedbi/pogledu. Sledijo standardu ANSI SQL 99 in so združljivi s SQL.

Sintaksa CTE v strežniku SQL

Sintaksa CTE vključuje ime CTE, izbirni seznam stolpcev in stavek/poizvedbo, ki definira izraz skupne tabele (CTE). Ko definiramo CTE, ga lahko uporabimo kot pogled v poizvedbi SELECT, INSERT, UPDATE, DELETE in MERGE.

Sledi osnovna sintaksa CTE v SQL Server:

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

V tej sintaksi:

• Najprej smo določili ime CTE, na katerega se bomo pozneje sklicevali v poizvedbi.
• Naslednji korak je ustvariti seznam stolpcev, ločenih z vejicami. Zagotavlja, da mora biti število stolpcev v argumentih definicije CTE in število stolpcev v poizvedbi enako. Če nismo definirali stolpcev argumentov CTE, bo uporabil stolpce poizvedbe, ki definirajo CTE.
• Nato bomo uporabili ključno besedo AS za imenom izraza in nato definirali stavek SELECT, katerega niz rezultatov zapolni CTE.
• Končno bomo uporabili ime CTE v poizvedbi, kot so stavek SELECT, INSERT, UPDATE, DELETE in MERGE.

Med pisanjem definicije poizvedbe CTE je treba upoštevati; ne moremo uporabiti naslednjih klavzul:

1. ORDER BY, razen če uporabite tudi kot TOP klavzulo
2. INTO
3. Klavzula OPTION z namigi za poizvedbo
4. ZA PREGLED

Spodnja slika je predstavitev definicije poizvedbe CTE.

Tu je prvi del izraz CTE, ki vsebuje poizvedbo SQL, ki jo je mogoče izvajati neodvisno v SQL. In drugi del je poizvedba, ki uporablja CTE za prikaz rezultata.

Primer

Razumejmo, kako deluje CTE v strežniku SQL z uporabo različnih primerov. Tukaj bomo uporabili tabelo ' stranka ' za demonstracijo. Recimo, da ta tabela vsebuje naslednje podatke:

V tem primeru je ime CTE stranke_v_newyork , podpoizvedba, ki definira CTE, vrne tri stolpce ime stranke, e-pošta, in država . Posledično bo CTE customers_in_newyork vrnil vse stranke, ki živijo v državi New York.

Po definiranju CTE customer_in_newyork smo se nanj sklicevali v IZBERI izjavo za pridobitev podrobnosti tistih strank, ki se nahajajo v New Yorku.

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

Po izvedbi zgornjega stavka bo dal naslednji rezultat. Tukaj lahko vidimo, da rezultat vrne samo podatke o strankah, ki se nahajajo v zvezni državi New York.

Večkratni CTE

V nekaterih primerih bomo morali ustvariti več poizvedb CTE in jih združiti, da bomo videli rezultate. V tem scenariju lahko uporabimo koncept več CTE. Za ustvarjanje več poizvedb CTE in njihovo združitev v eno samo izjavo moramo uporabiti operator vejice. Pred operatorjem ',' mora biti ime CTE, da ločimo več CTE.

Več CTE-jev nam pomaga pri poenostavitvi kompleksnih poizvedb, ki se na koncu združijo. Vsak kompleksen kos je imel svoj CTE, na katerega se je nato mogoče sklicevati in združiti zunaj klavzule WITH.

OPOMBA: Definicijo več CTE lahko definirate z uporabo UNION, UNION ALL, JOIN, INTERSECT ali EXCEPT.

Spodnja sintaksa to bolj jasno razloži:

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

Primer

Razumejmo, kako deluje več CTE v SQL Serverju. Tukaj bomo uporabili zgornji ' stranka ' miza za predstavitev.

V tem primeru smo definirali dve imeni CTE stranke_v_newyork in stranke_v_kaliforniji . Nato nabor rezultatov podpoizvedb teh CTE poseli CTE. Končno bomo uporabili imena CTE v poizvedbi, ki bo vrnila vse stranke, ki se nahajajo v New York in zvezna država Kalifornija .

 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; 

New York in država Kalifornija.

Zakaj potrebujemo CTE?

Tako kot pogledi baze podatkov in izpeljane tabele lahko CTE olajšajo pisanje in upravljanje zapletenih poizvedb, tako da postanejo bolj berljive in preproste. To lastnost lahko dosežemo tako, da kompleksne poizvedbe razčlenimo na preproste bloke, ki jih lahko ponovno uporabimo pri ponovnem pisanju poizvedbe.

Nekaj ​​primerov njegove uporabe je navedenih spodaj:

• Uporaben je, ko moramo izpeljano tabelo definirati večkrat znotraj ene same poizvedbe.
• Uporaben je, ko moramo ustvariti alternativo pogledu v bazi podatkov.
• Uporaben je, ko moramo isti izračun izvesti večkrat na več komponentah poizvedbe hkrati.
• Uporaben je, ko moramo uporabiti funkcije za rangiranje, kot so ROW_NUMBER(), RANK() in NTILE().

Nekaj ​​njegovih prednosti je navedenih spodaj:

sončni del

• CTE olajša vzdrževanje kode.
• CTE poveča berljivost kode.
• Poveča zmogljivost poizvedbe.
• CTE omogoča enostavno izvajanje rekurzivnih poizvedb.

Vrste CTE v SQL Server

SQL Server razdeli CTE (Common Table Expressions) v dve široki kategoriji:

1. Rekurzivni CTE
2. Nerekurzivni CTE

Rekurzivni CTE

Izraz skupne tabele je znan kot rekurzivni CTE, ki se sklicuje na samega sebe. Njegov koncept temelji na rekurziji, ki je definirana kot ' večkratna uporaba rekurzivnega procesa ali definicije .' Ko izvedemo rekurzivno poizvedbo, ta vedno znova pregleda podmnožico podatkov. Preprosto je definirana kot poizvedba, ki kliče samo sebe. Na neki točki obstaja končni pogoj, zato se ne kliče neskončno.

Rekurzivni CTE mora imeti a UNIJA VSE stavek in drugo definicijo poizvedbe, ki se sklicuje na sam CTE, da je rekurziven.

Primer

Razumejmo, kako rekurzivni CTE deluje v SQL Serverju. Razmislite o spodnji izjavi, ki ustvari niz prvih petih lihih števil:

 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>

Ta CTE bo dal naslednje rezultate, kjer lahko vidimo hierarhijo podatkov o zaposlenih:

Nerekurzivni CTE

Običajen izraz tabele, ki se ne sklicuje sam nase, je znan kot nerekurzivni CTE. Nerekurzivni CTE je preprost in lažje razumljiv, ker ne uporablja koncepta rekurzije. V skladu s sintakso CTE se bo vsaka poizvedba CTE začela z ' z ', ki mu sledi ime CTE in seznam stolpcev, nato pa AS z oklepajem.

Slabosti CTE

Omejitve uporabe CTE v strežniku SQL so naslednje:

• Člani CTE ne morejo uporabljati ključnih klavzul, kot so Distinct, Group By, Having, Top, Joins itd.
• Rekurzivni član se lahko na CTE sklicuje samo enkrat.
• Spremenljivk tabele in CTE-jev ne moremo uporabiti kot parametre v shranjenih procedurah.
• Vemo že, da bi lahko CTE uporabili namesto pogleda, vendar CTE ni mogoče ugnezditi, pogledi pa lahko.
• Ker je to le bližnjica do poizvedbe ali podpoizvedbe, je ni mogoče ponovno uporabiti v drugi poizvedbi.
• Število stolpcev v argumentih CTE in število stolpcev v poizvedbi mora biti enako.