TechCodeview

Az SQL Server Common Table Expressions vagy CTE-jeit fogjuk használni az összetett csatlakozások és részlekérdezések megkönnyítésére. Lehetőséget biztosít a hierarchikus adatok, például a szervezeti hierarchia lekérdezésére is. Ez a cikk teljes áttekintést ad a CTE-ről, a CTE típusairól, előnyeiről, hátrányairól és ezek használatáról az SQL Serverben.

Mi az a CTE az SQL Serverben?

A CTE (Common Table Expression) egy egyszeri eredményhalmaz, amely csak a lekérdezés időtartama alatt létezik. . Lehetővé teszi, hogy egyetlen SELECT, INSERT, UPDATE, DELETE, CREATE VIEW vagy MERGE utasítás végrehajtási hatókörén belüli adatokra hivatkozzunk. Ideiglenes, mert az eredménye nem tárolható sehol, és a lekérdezés végrehajtása után azonnal elveszik. Először az SQL Server 2005 verziójával érkezett. A DBA mindig a CTE-t részesítette előnyben az Allekérdezés/nézet alternatívájaként. Követik az ANSI SQL 99 szabványt és SQL-kompatibilisek.

CTE szintaxis az SQL Serverben

A CTE szintaxis tartalmaz egy CTE nevet, egy opcionális oszloplistát és egy utasítást/lekérdezést, amely meghatározza a közös táblakifejezést (CTE). A CTE meghatározása után nézetként használhatjuk a SELECT, INSERT, UPDATE, DELETE és MERGE lekérdezésekben.

A következő a CTE alapvető szintaxisa az SQL Serverben:

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

Ebben a szintaxisban:

• Először a CTE nevet adtuk meg, amelyre később egy lekérdezésben hivatkozni fogunk.
• A következő lépés a vesszővel elválasztott oszlopok listájának létrehozása. Biztosítja, hogy a CTE-definíciós argumentumokban és a lekérdezésben lévő oszlopok számának azonosnak kell lennie. Ha nem definiáltuk a CTE argumentumok oszlopait, akkor a CTE-t meghatározó lekérdezési oszlopokat fogja használni.
• Ezt követően a kifejezés neve után az AS kulcsszót használjuk, majd meghatározunk egy SELECT utasítást, amelynek eredménykészlete kitölti a CTE-t.
• Végül a CTE nevet fogjuk használni olyan lekérdezésekben, mint a SELECT, INSERT, UPDATE, DELETE és MERGE utasítás.

Ezt szem előtt kell tartani a CTE lekérdezés definíciójának írásakor; nem használhatjuk a következő kitételeket:

1. ORDER BY, hacsak nem használja TOP záradékként is
2. BA
3. OPTION záradék lekérdezési tippekkel
4. Böngészésre

Az alábbi kép a CTE lekérdezés definícióját mutatja be.

Itt az első rész egy CTE kifejezés, amely egy SQL lekérdezést tartalmaz, amely függetlenül futtatható SQL-ben. A második rész pedig a lekérdezés, amely a CTE-t használja az eredmény megjelenítéséhez.

Példa

Különböző példákon keresztül értsük meg, hogyan működik a CTE az SQL Serverben. Itt egy táblázatot fogunk használni. vevő 'tüntetésre. Tegyük fel, hogy ez a táblázat a következő adatokat tartalmazza:

Ebben a példában a CTE neve a következő vásárlók_newyorkban , a CTE-t meghatározó részlekérdezés a három oszlopot adja vissza ügyfél neve, email címe, és állapot . Ennek eredményeként a CTE customers_in_newyork visszaadja az összes New York államban élő ügyfelet.

A CTE customers_in_newyork definíciója után hivatkoztunk rá a KIVÁLASZTÁS nyilatkozatot, hogy megkapja azon ügyfelek adatait, akik New Yorkban találhatók.

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

A fenti utasítás végrehajtása után a következő kimenetet adja. Itt láthatjuk, hogy az eredmény csak a New York államban található ügyféladatokat adja vissza.

Több CTE

Bizonyos esetekben több CTE-lekérdezést kell létrehoznunk, és egyesíteni kell őket az eredmények megtekintéséhez. Ebben a forgatókönyvben több CTE koncepciót is használhatunk. A vessző operátort kell használnunk több CTE lekérdezés létrehozásához és egyetlen utasításba való egyesítéséhez. A ',' vessző operátort a CTE nevének kell megelőznie a több CTE megkülönböztetéséhez.

A több CTE segít nekünk az összetett lekérdezések egyszerűsítésében, amelyeket végül összekapcsolunk. Minden összetett darabnak megvolt a saját CTE-je, amelyre azután hivatkozni lehetett, és a WITH záradékon kívül össze lehetett kapcsolni.

MEGJEGYZÉS: A többszörös CTE definíció az UNION, UNION ALL, JOIN, INTERSECT vagy EXCEPT paraméterekkel határozható meg.

Az alábbi szintaxis érthetőbben magyarázza meg:

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

Példa

Megértjük, hogyan működik több CTE az SQL Serverben. Itt a fentieket fogjuk használni vevő ' asztal egy bemutatóhoz.

Ebben a példában definiáltuk a két CTE nevet vásárlók_newyorkban és ügyfelek_kaliforniában . Ezután ezeknek a CTE-knek az eredményhalmaza feltölti a CTE-t. Végül a CTE-neveket fogjuk használni egy olyan lekérdezésben, amely minden országban tartózkodó ügyfelet visszaad New York és Kalifornia állam .

 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 és Kalifornia állam.

Miért van szükségünk CTE-re?

Az adatbázisnézetekhez és a származtatott táblákhoz hasonlóan a CTE-k is könnyebbé tehetik az összetett lekérdezések írását és kezelését azáltal, hogy olvashatóbbá és egyszerűbbé teszik azokat. Ezt a jellemzőt úgy érhetjük el, hogy az összetett lekérdezéseket egyszerű blokkokra bontjuk, amelyek újra felhasználhatók a lekérdezés újraírásában.

Az alábbiakban bemutatunk néhány felhasználási esetet:

• Akkor hasznos, ha egy lekérdezésben többször is meg kell határoznunk egy származtatott táblát.
• Akkor hasznos, ha alternatívát kell létrehoznunk egy nézethez az adatbázisban.
• Akkor hasznos, ha ugyanazt a számítást többször kell végrehajtanunk több lekérdezési komponensen egyszerre.
• Akkor hasznos, ha olyan rangsoroló függvényeket kell használnunk, mint a ROW_NUMBER(), RANK() és NTILE().

Néhány előnye az alábbiakban található:

szkenner scan java

• A CTE megkönnyíti a kód karbantartását.
• A CTE növeli a kód olvashatóságát.
• Növeli a lekérdezés teljesítményét.
• A CTE lehetővé teszi a rekurzív lekérdezések egyszerű megvalósítását.

A CTE típusai az SQL Serverben

Az SQL Server a CTE-t (Common Table Expressions) két nagy csoportra osztja:

1. Rekurzív CTE
2. Nem rekurzív CTE

Rekurzív CTE

Egy általános táblakifejezés rekurzív CTE néven ismert, amely önmagára hivatkozik. Koncepciója a rekurzión alapul, amelyet a következőképpen határoznak meg: rekurzív folyamat vagy definíció ismételt alkalmazása .' Amikor rekurzív lekérdezést hajtunk végre, az ismételten iterál az adatok egy részhalmazán. Egyszerűen úgy definiálható, mint egy lekérdezés, amely meghívja magát. Valamikor létezik egy végfeltétel, ezért nem nevezi magát végtelenül.

A rekurzív CTE-nek rendelkeznie kell a UNION ALL utasítást és egy második lekérdezésdefiníciót, amely magára a CTE-re hivatkozik, hogy rekurzív legyen.

Példa

Megértjük, hogyan működik a rekurzív CTE az SQL Serverben. Tekintsük az alábbi állítást, amely generálja az első öt páratlan szám sorozatát:

 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>

Ez a CTE a következő kimenetet adja, ahol láthatjuk az alkalmazottak adatainak hierarchiáját:

Nem rekurzív CTE

Egy általános táblakifejezés, amely önmagára nem hivatkozik, nem rekurzív CTE-nek nevezik. A nem rekurzív CTE egyszerű és könnyebben érthető, mivel nem használja a rekurzió fogalmát. A CTE szintaxis szerint minden CTE lekérdezés egy '' karakterrel kezdődik Val vel ' záradék, majd a CTE neve és oszloplista, majd az AS zárójellel.

A CTE hátrányai

A CTE SQL Serverben való használatának korlátozásai a következők:

• A CTE-tagok nem használhatják az olyan kulcsszavakat, mint például a megkülönböztető, csoportosított, birtoklás, felső, csatlakozások stb.
• A CTE-re csak egyszer hivatkozhat a Rekurzív tag.
• A táblaváltozókat és a CTE-ket nem használhatjuk paraméterként a tárolt eljárásokban.
• Azt már tudjuk, hogy a CTE használható nézet helyett, de a CTE nem ágyazható be, míg a Views igen.
• Mivel ez csak egy lekérdezés vagy segédlekérdezés parancsikonja, nem használható fel újra egy másik lekérdezésben.
• A CTE argumentumokban lévő oszlopok számának és a lekérdezés oszlopainak számának meg kell egyeznie.