TechCodeview

Koristit ćemo uobičajene tablične izraze ili CTE-ove SQL Servera kako bismo olakšali složena spajanja i podupiti. Također pruža način postavljanja upita hijerarhijskim podacima, kao što je organizacijska hijerarhija. Ovaj članak daje potpuni pregled CTE-a, vrste CTE-a, prednosti, nedostatke i kako ih koristiti u SQL Serveru.

Što je CTE u SQL Serveru?

CTE (Common Table Expression) je jednokratni skup rezultata koji postoji samo tijekom trajanja upita . Omogućuje nam da se pozivamo na podatke unutar jednog opsega izvršenja naredbe SELECT, INSERT, UPDATE, DELETE, CREATE VIEW ili MERGE. Privremen je jer se njegov rezultat ne može nigdje pohraniti i bit će izgubljen čim se izvrši upit. Prvo je došao s verzijom SQL Servera 2005. DBA je uvijek preferirao korištenje CTE-a kao alternative podupitu/pregledu. Slijede standard ANSI SQL 99 i usklađeni su sa SQL-om.

CTE sintaksa u SQL Serveru

CTE sintaksa uključuje CTE naziv, izborni popis stupaca i naredbu/upit koji definira izraz zajedničke tablice (CTE). Nakon definiranja CTE-a, možemo ga koristiti kao prikaz u upitu SELECT, INSERT, UPDATE, DELETE i MERGE.

Slijedi osnovna sintaksa CTE-a u SQL Serveru:

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

U ovoj sintaksi:

• Prvo smo naveli CTE naziv koji će se kasnije spominjati u upitu.
• Sljedeći korak je stvaranje popisa stupaca odvojenih zarezima. Osigurava da broj stupaca u argumentima CTE definicije i broj stupaca u upitu moraju biti isti. Ako nismo definirali stupce CTE argumenata, koristit će se stupci upita koji definiraju CTE.
• Nakon toga ćemo upotrijebiti ključnu riječ AS nakon naziva izraza, a zatim definirati naredbu SELECT čiji skup rezultata popunjava CTE.
• Konačno, koristit ćemo CTE naziv u upitu kao što su SELECT, INSERT, UPDATE, DELETE i MERGE iskaz.

To treba imati na umu dok pišete definiciju CTE upita; ne možemo koristiti sljedeće klauzule:

1. ORDER BY osim ako ne koristite i kao TOP klauzulu
2. U
3. OPTION klauzula sa savjetima za upit
4. ZA PREGLED

Donja slika je prikaz definicije CTE upita.

Ovdje je prvi dio CTE izraz koji sadrži SQL upit koji se može pokrenuti neovisno u SQL-u. A drugi dio je upit koji koristi CTE za prikaz rezultata.

Primjer

Razmotrimo kako CTE radi u SQL Serveru koristeći razne primjere. Ovdje ćemo koristiti tablicu ' kupac ' za demonstraciju. Pretpostavimo da ova tablica sadrži sljedeće podatke:

U ovom primjeru naziv CTE je kupci_u_newyork-u , podupit koji definira CTE vraća tri stupca ime klijenta, email, i država . Kao rezultat toga, CTE customers_in_newyork vratit će sve kupce koji žive u državi New York.

Nakon definiranja CTE-a customers_in_newyork, referencirali smo ga u IZABERI izjavu kako biste dobili pojedinosti onih kupaca koji se nalaze u New Yorku.

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

Nakon izvršenja gornje naredbe, dat će sljedeći izlaz. Ovdje možemo vidjeti da rezultat vraća samo podatke o klijentima koji se nalaze u državi New York.

Višestruki CTE

U nekim slučajevima morat ćemo izraditi više CTE upita i spojiti ih kako bismo vidjeli rezultate. U ovom scenariju možemo koristiti višestruki CTE koncept. Moramo koristiti operator zarez za stvaranje višestrukih CTE upita i njihovo spajanje u jednu izjavu. Ispred operatora ',' mora stajati naziv CTE-a kako bi se razlikovalo više CTE-a.

Višestruki CTE-ovi pomažu nam u pojednostavljenju složenih upita koji se na kraju spajaju. Svaki složeni dio imao je vlastiti CTE, koji se zatim mogao referencirati i pridružiti izvan klauzule WITH.

NAPOMENA: Definicija višestrukog CTE-a može se definirati korištenjem UNION, UNION ALL, JOIN, INTERSECT ili EXCEPT.

Donja sintaksa to jasnije objašnjava:

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

Primjer

Hajde da shvatimo kako višestruki CTE radi u SQL Serveru. Ovdje ćemo upotrijebiti gornji ' kupac ' stol za demonstraciju.

U ovom smo primjeru definirali dva CTE naziva kupci_u_newyork-u i kupci_u_kaliforniji . Tada skup rezultata podupita ovih CTE-ova popunjava CTE. Konačno, koristit ćemo CTE nazive u upitu koji će vratiti sve klijente koji se nalaze u New York i 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 i država Kalifornija.

Zašto nam je potreban CTE?

Poput prikaza baze podataka i izvedenih tablica, CTE-ovi mogu olakšati pisanje složenih upita i upravljanje njima čineći ih čitljivijima i jednostavnijima. Ovu karakteristiku možemo postići rastavljanjem složenih upita u jednostavne blokove koji se mogu ponovno koristiti u ponovnom pisanju upita.

Neki od slučajeva njegove upotrebe navedeni su u nastavku:

• Korisno je kada trebamo definirati izvedenu tablicu više puta unutar jednog upita.
• Korisno je kada trebamo stvoriti alternativu pogledu u bazi podataka.
• Korisno je kada trebamo izvesti isti izračun više puta na više komponenti upita istovremeno.
• Korisno je kada trebamo koristiti funkcije rangiranja kao što su ROW_NUMBER(), RANK() i NTILE().

Neke od njegovih prednosti navedene su u nastavku:

java regex za

• CTE olakšava održavanje koda.
• CTE povećava čitljivost koda.
• Povećava izvedbu upita.
• CTE omogućuje jednostavnu implementaciju rekurzivnih upita.

Vrste CTE u SQL Serveru

SQL Server dijeli CTE (Common Table Expressions) u dvije široke kategorije:

1. Rekurzivni CTE
2. Nerekurzivni CTE

Rekurzivni CTE

Uobičajeni tablični izraz poznat je kao rekurzivni CTE koji referencira sam sebe. Njegov se koncept temelji na rekurziji, koja se definira kao ' primjena rekurzivnog procesa ili definicije opetovano .' Kada izvršavamo rekurzivni upit, on opetovano ponavlja podskup podataka. Jednostavno se definira kao upit koji sam sebe poziva. Postoji krajnji uvjet u nekom trenutku, tako da se ne poziva beskonačno.

Rekurzivni CTE mora imati a UNIJA SVE naredbu i drugu definiciju upita koja referencira sam CTE kako bi bila rekurzivna.

Primjer

Hajde da shvatimo kako rekurzivni CTE radi u SQL Serveru. Razmotrite donju izjavu koja generira niz od prvih pet neparnih brojeva:

 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>

Ovaj CTE će dati sljedeći rezultat gdje možemo vidjeti hijerarhiju podataka o zaposlenicima:

Nerekurzivni CTE

Uobičajeni tablični izraz koji se ne referira sam na sebe poznat je kao nerekurzivni CTE. Nerekurzivni CTE je jednostavan i lakši za razumijevanje jer ne koristi koncept rekurzije. Prema CTE sintaksi, svaki CTE upit započet će s ' S ' klauzula iza koje slijedi CTE naziv i popis stupaca, zatim AS sa zagradama.

Nedostaci CTE

Sljedeća su ograničenja korištenja CTE-a u SQL Serveru:

• Članovi CTE-a ne mogu koristiti klauzule ključnih riječi kao što su Distinct, Group By, Having, Top, Joins itd.
• Rekurzivni član može referencirati CTE samo jednom.
• Ne možemo koristiti varijable tablice i CTE kao parametre u pohranjenim procedurama.
• Već znamo da se CTE može koristiti umjesto pogleda, ali CTE ne može biti ugniježđen, dok pogledi mogu.
• Budući da je to samo prečac za upit ili podupit, ne može se ponovno upotrijebiti u drugom upitu.
• Broj stupaca u CTE argumentima i broj stupaca u upitu moraju biti isti.