Będziemy używać wspólnych wyrażeń tabelowych lub CTE programu SQL Server, aby ułatwić złożone łączenia i podzapytania. Umożliwia także wykonywanie zapytań o dane hierarchiczne, takie jak hierarchia organizacyjna. Ten artykuł zawiera pełny przegląd CTE, typów CTE, zalet i wad oraz sposobu ich używania w SQL Server.

Co to jest CTE w SQL Server?

CTE (Common Table Expression) to jednorazowy zestaw wyników, który istnieje tylko przez czas trwania zapytania . Pozwala nam odwoływać się do danych w ramach pojedynczego zakresu wykonania instrukcji SELECT, INSERT, UPDATE, DELETE, CREATE VIEW lub MERGE. Ma charakter tymczasowy, ponieważ jego wyniku nie można nigdzie zapisać i zostanie utracony zaraz po zakończeniu wykonywania zapytania. Po raz pierwszy pojawił się w wersji SQL Server 2005. Administrator danych zawsze wolał używać CTE jako alternatywy dla podzapytania/widoku. Są zgodne ze standardem ANSI SQL 99 i są zgodne z SQL.

Składnia CTE w SQL Server

Składnia CTE obejmuje nazwę CTE, opcjonalną listę kolumn oraz instrukcję/zapytanie definiujące wspólne wyrażenie tabelowe (CTE). Po zdefiniowaniu CTE możemy go użyć jako widoku w zapytaniach SELECT, INSERT, UPDATE, DELETE i MERGE.

Poniżej znajduje się podstawowa składnia CTE w SQL Server:

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

W tej składni:

• Najpierw określiliśmy nazwę CTE, do której będziemy się odwoływać w dalszej części zapytania.
• Następnym krokiem jest utworzenie listy kolumn oddzielonych przecinkami. Zapewnia to, że liczba kolumn w argumentach definicji CTE i liczba kolumn w zapytaniu muszą być takie same. Jeżeli nie zdefiniowaliśmy kolumn argumentów CTE, użyte zostaną kolumny zapytania definiujące CTE.
• Następnie użyjemy słowa kluczowego AS po nazwie wyrażenia, a następnie zdefiniujemy instrukcję SELECT, której zestaw wyników wypełni CTE.
• Na koniec użyjemy nazwy CTE w zapytaniu takim jak SELECT, INSERT, UPDATE, DELETE i MERGE.

Należy o tym pamiętać pisząc definicję zapytania CTE; nie możemy używać następujących klauzul:

1. ORDER BY, chyba że użyjesz również klauzuli TOP
2. DO
3. Klauzula OPTION ze wskazówkami dotyczącymi zapytań
4. DO PRZEGLĄDANIA

Poniższy obraz przedstawia definicję zapytania CTE.

W tym przypadku pierwszą częścią jest wyrażenie CTE zawierające zapytanie SQL, które można uruchomić niezależnie w języku SQL. Druga część to zapytanie, które używa CTE do wyświetlenia wyniku.

Przykład

Pozwól nam zrozumieć, jak CTE działa w SQL Server na różnych przykładach. Tutaj będziemy korzystać z tabeli ' klient 'na demonstrację. Załóżmy, że ta tabela zawiera następujące dane:

W tym przykładzie nazwa CTE to klienci_w_nowymorku , podzapytanie definiujące CTE zwraca trzy kolumny imię i nazwisko klienta, adres e-mail, I państwo . W rezultacie CTE Customers_in_newyork zwróci wszystkich klientów mieszkających w stanie Nowy Jork.

Po zdefiniowaniu CTE Customers_in_newyork, odwołaliśmy się do niego w pliku WYBIERAĆ oświadczenie, aby uzyskać szczegółowe informacje o klientach znajdujących się w Nowym Jorku.

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

Po wykonaniu powyższej instrukcji wyświetli się następujący wynik. Tutaj widzimy, że wynik zwraca tylko te informacje o klientach, którzy znajdują się w stanie Nowy Jork.

Wiele CTE

W niektórych przypadkach będziemy musieli utworzyć wiele zapytań CTE i połączyć je, aby zobaczyć wyniki. W tym scenariuszu możemy zastosować koncepcję wielu CTE. Musimy użyć operatora przecinka, aby utworzyć wiele zapytań CTE i połączyć je w jedną instrukcję. Operator przecinka „,” musi być poprzedzony nazwą CTE, aby możliwe było rozróżnienie wielu CTE.

Wiele CTE pomaga nam w upraszczaniu złożonych zapytań, które ostatecznie są łączone. Każdy złożony element miał swój własny CTE, do którego można było następnie odwoływać się i łączyć poza klauzulą ​​WITH.

UWAGA: Definicję wielokrotnego CTE można zdefiniować za pomocą UNION, UNION ALL, JOIN, INTERSECT lub EXCEPT.

Poniższa składnia wyjaśnia to jaśniej:

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

Przykład

Pozwól nam zrozumieć, jak działa wiele CTE w SQL Server. Tutaj użyjemy powyższego ' klient stół do demonstracji.

W tym przykładzie zdefiniowaliśmy dwie nazwy CTE klienci_w_nowymorku I klienci_w_kalifornii . Następnie zestaw wyników podzapytań tych CTE wypełnia CTE. Na koniec użyjemy nazw CTE w zapytaniu, które zwróci wszystkich klientów znajdujących się w Nowy Jork I Stan Kalifornia .

 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; 

Stan Nowy Jork i Kalifornia.

Dlaczego potrzebujemy CTE?

Podobnie jak widoki baz danych i tabele pochodne, CTE mogą ułatwić pisanie złożonych zapytań i zarządzanie nimi, czyniąc je bardziej czytelnymi i prostymi. Możemy osiągnąć tę cechę, dzieląc złożone zapytania na proste bloki, które można ponownie wykorzystać podczas przepisywania zapytania.

Niektóre z jego przypadków użycia podano poniżej:

• Jest to przydatne, gdy musimy wielokrotnie zdefiniować tabelę pochodną w ramach jednego zapytania.
• Przydaje się, gdy potrzebujemy stworzyć alternatywę dla widoku w bazie danych.
• Jest to przydatne, gdy musimy wykonać te same obliczenia wiele razy jednocześnie na wielu komponentach zapytania.
• Jest to przydatne, gdy musimy użyć funkcji rankingowych, takich jak ROW_NUMBER(), RANK() i NTILE().

Niektóre z jego zalet podano poniżej:

sterta i sortowanie po stercie

• CTE ułatwia konserwację kodu.
• CTE zwiększa czytelność kodu.
• Zwiększa wydajność zapytania.
• CTE umożliwia łatwą implementację zapytań rekurencyjnych.

Rodzaje CTE w SQL Server

SQL Server dzieli CTE (Common Table Expressions) na dwie szerokie kategorie:

1. Rekurencyjne CTE
2. Nierekurencyjny CTE

Rekurencyjne CTE

Powszechnie stosowanym wyrażeniem tabelowym jest rekurencyjne CTE, które odwołuje się do samego siebie. Jego koncepcja opiera się na rekurencji, którą definiuje się jako „ wielokrotne stosowanie procesu rekurencyjnego lub definicji .' Kiedy wykonujemy zapytanie rekurencyjne, wielokrotnie iteruje ono po podzbiorze danych. Definiuje się je po prostu jako zapytanie, które wywołuje samo siebie. W pewnym momencie istnieje warunek końcowy, więc nie nazywa się on nieskończonym.

Rekurencyjny CTE musi mieć UNIA WSZYSTKICH instrukcja i druga definicja zapytania, która odwołuje się do samego CTE, aby była rekurencyjna.

Przykład

Pozwól nam zrozumieć, jak działa rekurencyjny CTE w SQL Server. Rozważ poniższe stwierdzenie, które generuje serię pierwszych pięciu liczb nieparzystych:

 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>

Ten CTE da następujący wynik, w którym możemy zobaczyć hierarchię danych pracowników:

Nierekurencyjny CTE

Typowe wyrażenie tabelowe, które nie odwołuje się do samego siebie, jest znane jako nierekurencyjne CTE. Nierekurencyjny CTE jest prosty i łatwiejszy do zrozumienia, ponieważ nie wykorzystuje koncepcji rekurencji. Zgodnie ze składnią CTE każde zapytanie CTE będzie zaczynać się od „ Z ' klauzula, po której następuje nazwa CTE i lista kolumn, a następnie AS z nawiasem.

Wady CTE

Poniżej przedstawiono ograniczenia używania CTE w SQL Server:

• Członkowie CTE nie mogą używać klauzul kluczowych, takich jak Distinct, Group By, Have, Top, Joins itp.
• Element rekurencyjny może odwoływać się do CTE tylko raz.
• Nie możemy używać zmiennych tabeli i CTE jako parametrów w procedurach przechowywanych.
• Wiemy już, że CTE można zastosować zamiast widoku, ale CTE nie można zagnieżdżać, w przeciwieństwie do widoków.
• Ponieważ jest to tylko skrót do zapytania lub podzapytania, nie można go ponownie użyć w innym zapytaniu.
• Liczba kolumn w argumentach CTE i liczba kolumn w zapytaniu muszą być takie same.