TechCodeview

Käytämme SQL Serverin yleisiä taulukkolausekkeita tai CTE:itä monimutkaisten liitosten ja alikyselyjen helpottamiseksi. Se tarjoaa myös tavan tehdä kyselyjä hierarkkisista tiedoista, kuten organisaatiohierarkiasta. Tämä artikkeli antaa täydellisen yleiskatsauksen CTE:stä, CTE-tyypeistä, eduista, haitoista ja niiden käytöstä SQL Serverissä.

Mikä on CTE SQL Serverissä?

CTE (Common Table Expression) on kertaluonteinen tulosjoukko, joka on olemassa vain kyselyn ajan . Sen avulla voimme viitata tietoihin yhden SELECT-, INSERT-, UPDATE-, DELETE-, CREATE VIEW- tai MERGE-käskyn suoritusalueella. Se on väliaikainen, koska sen tulosta ei voida tallentaa minnekään ja se katoaa heti, kun kyselyn suoritus on valmis. Se tuli ensin SQL Server 2005 -version kanssa. DBA halusi aina käyttää CTE:tä vaihtoehtona alikyselylle/näkymälle. Ne noudattavat ANSI SQL 99 -standardia ja ovat SQL-yhteensopivia.

CTE-syntaksi SQL Serverissä

CTE-syntaksi sisältää CTE-nimen, valinnaisen sarakeluettelon ja lausunnon/kyselyn, joka määrittää yhteisen taulukkolausekkeen (CTE). Kun CTE on määritetty, voimme käyttää sitä näkymänä SELECT-, INSERT-, UPDATE-, DELETE- ja MERGE-kyselyssä.

Seuraava on CTE:n perussyntaksi SQL Serverissä:

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

Tässä syntaksissa:

• Olemme ensin määrittäneet CTE-nimen, johon viitataan myöhemmin kyselyssä.
• Seuraava vaihe on luoda luettelo pilkuilla erotetuista sarakkeista. Se varmistaa, että CTE-määrittelyargumenttien sarakkeiden lukumäärän ja kyselyn sarakkeiden määrän on oltava sama. Jos emme ole määrittäneet CTE-argumenttien sarakkeita, se käyttää kyselysarakkeita, jotka määrittävät CTE:n.
• Tämän jälkeen käytämme AS-avainsanaa lausekkeen nimen jälkeen ja määritämme sitten SELECT-lauseen, jonka tulosjoukko täyttää CTE:n.
• Lopuksi käytämme CTE-nimeä kyselyssä, kuten SELECT, INSERT, UPDATE, DELETE ja MERGE.

Se tulee pitää mielessä, kun kirjoitat CTE-kyselyn määritelmää; emme voi käyttää seuraavia lausekkeita:

1. TILAA, ellet käytä sitä myös TOP-lausekkeena
2. INTO
3. OPTION-lause kyselyvinkkeineen
4. SELAAMISEKSI

Alla oleva kuva on esitys CTE-kyselyn määritelmästä.

Tässä ensimmäinen osa on CTE-lauseke, joka sisältää SQL-kyselyn, joka voidaan suorittaa itsenäisesti SQL:ssä. Ja toinen osa on kysely, joka käyttää CTE:tä tuloksen näyttämiseen.

Esimerkki

Ymmärrämme, kuinka CTE toimii SQL Serverissä erilaisten esimerkkien avulla. Tässä aiomme käyttää taulukkoa asiakas 'mielenosoitusta varten. Oletetaan, että tämä taulukko sisältää seuraavat tiedot:

Tässä esimerkissä CTE-nimi on asiakkaat_newyorkissa , CTE:n määrittelevä alikysely palauttaa kolme saraketta asiakkaan nimi, sähköpostiosoite, ja osavaltio . Tämän seurauksena CTE customers_in_newyork palauttaa kaikki New Yorkin osavaltiossa asuvat asiakkaat.

Kun olet määritellyt CTE-asiakkaat_in_newyorkissa, olemme viitanneet siihen kohdassa VALITSE lausunto saadaksesi tiedot niistä asiakkaista, jotka sijaitsevat New Yorkissa.

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

Kun yllä oleva lause on suoritettu, se antaa seuraavan tulosteen. Täällä voimme nähdä, että tulos palauttaa vain ne asiakastiedot, jotka sijaitsevat New Yorkin osavaltiossa.

Useita CTE

Joissakin tapauksissa meidän on luotava useita CTE-kyselyitä ja yhdistettävä ne yhteen nähdäksemme tulokset. Saatamme käyttää useita CTE-konsepteja tässä skenaariossa. Meidän on käytettävä pilkkuoperaattoria useiden CTE-kyselyjen luomiseen ja yhdistämiseen yhdeksi lauseeksi. Pilkuoperaattoria ',' edeltää CTE-nimi, jotta useat CTE:t voidaan erottaa.

Useat CTE:t auttavat meitä yksinkertaistamaan monimutkaisia ​​kyselyitä, jotka lopulta liitetään yhteen. Jokaisella monimutkaisella kappaleella oli oma CTE, johon voitiin sitten viitata ja liittää WITH-lauseen ulkopuolella.

HUOMAA: Usean CTE-määrityksen voi määrittää käyttämällä UNION, UNION ALL, JOIN, INTERSECT tai EXCEPT.

Alla oleva syntaksi selittää sen selkeämmin:

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

Esimerkki

Ymmärrämme kuinka useat CTE:t toimivat SQL Serverissä. Tässä aiomme käyttää yllä olevaa ' asiakas ' pöytä esittelyyn.

Tässä esimerkissä olemme määrittäneet kaksi CTE-nimeä asiakkaat_newyorkissa ja asiakkaat_kaliforniassa . Sitten näiden CTE:iden alikyselyiden tulosjoukko täyttää CTE:n. Lopuksi käytämme CTE-nimiä kyselyssä, joka palauttaa kaikki asiakkaat, jotka sijaitsevat alueella New York ja Kalifornian osavaltio .

 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 ja Kalifornian osavaltio.

Miksi tarvitsemme CTE:tä?

Kuten tietokantanäkymät ja johdetut taulukot, CTE:t voivat helpottaa monimutkaisten kyselyjen kirjoittamista ja hallintaa tekemällä niistä luettavampia ja yksinkertaisempia. Voimme saavuttaa tämän ominaisuuden jakamalla monimutkaiset kyselyt yksinkertaisiksi lohkoiksi, joita voidaan käyttää uudelleen kyselyn kirjoittamisessa.

Jotkut sen käyttötapauksista on esitetty alla:

• Se on hyödyllinen, kun meidän on määritettävä johdettu taulukko useita kertoja yhden kyselyn sisällä.
• Siitä on hyötyä, kun meidän on luotava vaihtoehto tietokannan näkymälle.
• Se on hyödyllinen, kun meidän on suoritettava sama laskutoimitus useita kertoja useille kyselykomponenteille samanaikaisesti.
• Se on hyödyllinen, kun meidän on käytettävä sijoitusfunktioita, kuten ROW_NUMBER(), RANK() ja NTILE().

Jotkut sen eduista on esitetty alla:

Linuxin hakemiston uudelleennimeäminen

• CTE helpottaa koodin ylläpitoa.
• CTE lisää koodin luettavuutta.
• Se lisää kyselyn suorituskykyä.
• CTE mahdollistaa rekursiivisten kyselyjen toteuttamisen helposti.

CTE-tyypit SQL Serverissä

SQL Server jakaa CTE:n (Common Table Expressions) kahteen laajaan luokkaan:

1. Rekursiivinen CTE
2. Ei-rekursiivinen CTE

Rekursiivinen CTE

Yleinen taulukkolauseke tunnetaan nimellä rekursiivinen CTE, joka viittaa itseensä. Sen konsepti perustuu rekursioon, joka määritellään ' rekursiivisen prosessin tai määritelmän soveltaminen toistuvasti .' Kun suoritamme rekursiivisen kyselyn, se toistuu toistuvasti tietojen osajoukossa. Se määritellään yksinkertaisesti kyselyksi, joka kutsuu itseään. Jossain vaiheessa on loppuehto, joten se ei kutsu itseään loputtomiin.

Rekursiivisella CTE:llä on oltava a UNIONI KAIKKI lauseke ja toinen kyselymääritelmä, joka viittaa itse CTE:hen ollakseen rekursiivinen.

Esimerkki

Ymmärrämme kuinka rekursiivinen CTE toimii SQL Serverissä. Harkitse alla olevaa lausuntoa, joka luo sarjan viidestä ensimmäisestä parittomasta luvusta:

 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>

Tämä CTE antaa seuraavan tulosteen, jossa voimme nähdä työntekijätietojen hierarkian:

Ei-rekursiivinen CTE

Yleinen taulukkolauseke, joka ei viittaa itseensä, tunnetaan ei-rekursiivisena CTE:nä. Ei-rekursiivinen CTE on yksinkertainen ja helpompi ymmärtää, koska siinä ei käytetä rekursion käsitettä. CTE-syntaksin mukaan jokainen CTE-kysely alkaa ' Kanssa '-lause, jota seuraa CTE-nimi ja sarakeluettelo, sitten AS suluissa.

CTE:n haitat

Seuraavat ovat CTE:n käytön rajoituksia SQL Serverissä:

• CTE-jäsenet eivät voi käyttää avainsanalauseita, kuten Distinct, Group By, Having, Top, Joins jne.
• Rekursiivinen jäsen voi viitata CTE:hen vain kerran.
• Emme voi käyttää taulukkomuuttujia ja CTE:itä parametreina tallennetuissa proseduureissa.
• Tiedämme jo, että CTE:tä voidaan käyttää näkymän sijasta, mutta CTE:tä ei voi sisäkkäin, kun taas Views voi.
• Koska se on vain kyselyn tai alikyselyn pikakuvake, sitä ei voi käyttää uudelleen toisessa kyselyssä.
• CTE-argumenttien sarakkeiden lukumäärän ja kyselyn sarakkeiden määrän on oltava sama.