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:
- ORDER BY, chyba że użyjesz również klauzuli TOP
- DO
- Klauzula OPTION ze wskazówkami dotyczącymi zapytań
- 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:
- Rekurencyjne CTE
- 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 ' <strong>jtp_employees</strong> ' 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'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 + ' ' + LastName AS FullName, EmpLevel, (SELECT FirstName + ' ' + 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'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 ' <strong>With</strong> ' 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's just a shortcut for a query or subquery, it can'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.