Redgate Hub (Polski)

wprowadzone w SQL Server 2005 wyrażenie common table (CTE) jest tymczasowym zestawem wyników o nazwie, do którego można odwoływać się w instrukcji SELECT, INSERT, UPDATE lub DELETE. Można również użyć CTE w instrukcji CREATE VIEW jako części zapytania SELECT widoku. Ponadto, począwszy od SQL Server 2008, możesz dodać CTE do nowej instrukcji MERGE.

SQL Server obsługuje dwa typy CTE-rekurencyjny i nieekursywny. W tym artykule wyjaśniam, jak tworzyć oba typy., Podane przeze mnie przykłady są oparte na lokalnej instancji SQL Server 2008 i pobierają dane z przykładowej bazy danych AdventureWorks2008.

pracując z typowymi wyrażeniami tabel

definiujesz CTE, dodając klauzulę WITH bezpośrednio przed instrukcją SELECT, INSERT, UPDATE, DELETE lub MERGE.,”>

1 2 3 4 5

] <common_table_expression>::= cte_name )] AS (cte_query)

…which can be represented like this…

As you can see, if you include more than one CTE in your WITH clause, you must separate them with commas., Ponadto dla każdego CTE należy podać nazwę, słowo kluczowe AS I instrukcję SELECT. Możesz również podać nazwy kolumn (oddzielone przecinkami), o ile liczba nazw odpowiada liczbie kolumn zwróconych przez zestaw wyników.

instrukcja SELECT w zapytaniu CTE musi spełniać te same wymagania, które są używane do tworzenia widoku. Aby uzyskać szczegółowe informacje na temat tych wymagań, zobacz temat „Utwórz widok (Transact-SQL)” w książkach SQL Server online. Więcej informacji na temat CTE w ogóle można znaleźć w temacie ” WITH common_table_expression (Transact-SQL).,”

Po zdefiniowaniu klauzuli WITH z niezbędnymi CTE, możesz odwoływać się do tych CTE, jak do każdej innej tabeli. Możesz jednak odwołać się do CTE tylko w zakresie wykonania oświadczenia, które następuje bezpośrednio po klauzuli WITH. Po uruchomieniu instrukcji zestaw wyników CTE nie jest dostępny dla innych instrukcji.

Tworzenie Nierekursywnego wyrażenia tabel wspólnych

nierekursywny CTE to taki, który nie odwołuje się do siebie w CTE. Nieekursywne CTE są prostsze niż rekurencyjne CTE, dlatego zaczynam od tego typu.,cteTotalSales:

po określeniu nazwy CTE podaję dwie nazwy kolumn, SalesPersonID i NetSales, które są zamknięte w nawiasach i oddzielone przecinkiem. Oznacza to, że zestaw wyników zwracany przez zapytanie CTE musi zwracać dwie kolumny.

następnie podaję słowo kluczowe AS, a następnie zestaw nawiasów, które zawierają zapytanie CTE. W takim przypadku polecenie SELECT zwraca całkowitą sprzedaż dla każdej osoby sprzedającej (całkowita sprzedaż pogrupowana według ID sprzedawcy)., Jak widać, zapytanie CTE może zawierać funkcje Transact-SQL, klauzule GROUP BY lub dowolne elementy, które może zawierać instrukcja SELECT w definicji widoku.

mogę teraz odwołać się do cteTotalSales w oświadczeniu, które natychmiast następuje. W tym przykładzie Utwórz instrukcję SELECT, która dołącza do sprzedaży.widok vSalesPerson do cteTotalSales, na podstawie identyfikatora sprzedawcy. Następnie wyciągam nazwy i lokalizacje z widoku, a sprzedaż netto z CTE. Poniższa tabela przedstawia wyniki zwrócone przez to polecenie.,

jak zauważyłeś wcześniej w składni, możesz zawierać wiele CTE w klauzuli WITH.,

pierwszy CTE-cteTotalSales-jest podobny do poprzedniego przykładu, z tym wyjątkiem, że klauzula WHERE została zakwalifikowana do włączenia sprzedaży dopiero od 2003 roku. Po zdefiniowaniu cteTotalSales dodaję przecinek, a następnie definiuję ctetargetdiff, który oblicza różnicę między sumą sprzedaży a kwotą sprzedaży.

nowa definicja CTE określa trzy kolumny dla zestawu wyników: SalesPersonID, salesquota i QuotaDiff., Jak można się spodziewać, zapytanie CTE zwraca trzy kolumny. Pierwszym z nich jest identyfikator sprzedawcy. Drugi to kwota sprzedaży. Jednak, ponieważ kwota sprzedaży nie jest zdefiniowana dla niektórych sprzedawców używam instrukcji przypadku. Jeśli wartość jest null, wartość ta jest ustawiona na 0, w przeciwnym razie zostanie użyta rzeczywista wartość salesquota.

zwracana końcowa kolumna to różnica między kwotą sprzedaży netto a kwotą sprzedaży. Ponownie, używam Oświadczenia sprawy. Jeśli wartość SalesQuota jest równa null, wtedy zostanie użyta wartość NetSales, w przeciwnym razie kwota sprzedaży jest odejmowana od sprzedaży netto, aby uzyskać różnicę.,

coś ciekawego na temat drugiego zapytania CTE jest to, że dołączyłem do sprzedaży.Tabela handlowca do pierwszego CTE-cteTotalSales – więc mogłem obliczyć różnicę między całkowitą sprzedażą a kwotą sprzedaży. Za każdym razem, gdy definiujesz wiele CTE w pojedynczej klauzuli WITH, możesz odwoływać się do poprzedzających CTE (ale nie na odwrót).

Po zdefiniowaniu moich CTE, mogę odwołać się do nich w pierwszym oświadczeniu, które następuje po CTE, jak widziałeś w poprzednim przykładzie. W tym przypadku dołączam się do sprzedaży.,widok vSalesPerson do cteTotalSales, a następnie dołącz do cteTargetDiff, wszystko na podstawie identyfikatora sprzedawcy. Moja lista SELECT zawiera wtedy kolumny ze wszystkich trzech źródeł. Instrukcja zwraca wyniki pokazane w poniższej tabeli.

jak widać, dane dotyczące sprzedaży są dostarczane dla wszystkich sprzedawców, w tym Miasta, w którym mieszkają, ich sprzedaży netto, ich kwoty sprzedaży i obliczonej różnicy między tymi dwoma liczbami. W tym przypadku każdy znacznie przekracza kwotę, w przypadku gdy kwota została określona.,

Tworzenie rekurencyjnego wyrażenia wspólnej tabeli

rekurencyjny CTE to taki, który odwołuje się do siebie w tym CTE. Rekurencyjny CTE jest przydatny podczas pracy z danymi hierarchicznymi, ponieważ CTE kontynuuje wykonywanie, dopóki zapytanie nie zwróci całej hierarchii.

typowym przykładem danych hierarchicznych jest tabela zawierająca listę pracowników. Dla każdego pracownika tabela zawiera odniesienie do menedżera tej osoby. To samo odniesienie jest identyfikatorem pracownika w tej samej tabeli., Możesz użyć rekurencyjnego CTE, aby wyświetlić hierarchię danych pracowników, tak jak pojawiałyby się one na wykresie organizacyjnym.

zauważ, że CTE utworzone nieprawidłowo może wejść w nieskończoną pętlę. Aby temu zapobiec, możesz dołączyć podpowiedź MAXRECURSION w klauzuli OPTION podstawowej instrukcji SELECT, INSERT, UPDATE, DELETE lub MERGE. Aby uzyskać informacje na temat korzystania z podpowiedzi do zapytań, zobacz temat „podpowiedzi do zapytań (Transact-SQL)” w książkach SQL Server online.,

aby zademonstrować, jak działa rekurencyjne CTE, użyłem następujących poleceń Transact-SQL, aby utworzyć i wypełnić tabelę pracowników w bazie danych AdventureWorks2008:

jak możesz sobie uświadomić, baza danych AdventureWorks2008 zawiera już Zasoby ludzkie.Stolik dla pracowników. Jednak ta tabela używa teraz typu danych hierarchyid do przechowywania danych hierarchicznych, co wprowadzałoby niepotrzebną złożoność podczas próby zademonstrowania rekurencyjnego CTE. Z tego powodu stworzyłem własny stół., Jeśli jednak chcesz wypróbować rekurencyjny CTE bez tworzenia i wypełniania nowej tabeli, możesz użyć przykładowej bazy danych AdventureWorks dostarczonej wraz z SQL Server 2005. Zasoby Ludzkie.Tabela pracowników w tej bazie danych przechowuje dane w sposób podobny do tabeli, którą tworzę powyżej.,div>2

div>

jak widać, CTE zwraca pięć kolumn: EmpID, FirstName, LastName, MgrID i EmpLevel. Kolumna EmpLevel odnosi się do poziomu w hierarchii, w którym mieszczą się pracownicy. Najwyższy poziom hierarchii to 1, następny poziom to 2, następnie 3 i tak dalej.

zapytanie CTE składa się z dwóch instrukcji SELECT, połączonych z operatorem UNION ALL., Rekurencyjne zapytanie CTE musi zawierać co najmniej dwa elementy (wyrażenia), połączone operatorem UNION ALL, UNION, INTERSECT lub EXCEPT. W tym przykładzie pierwsza instrukcja SELECT jest członkiem kotwicy, a druga instrukcja jest członkiem rekurencyjnym. Wszystkie elementy kotwiące muszą poprzedzać elementy rekurencyjne, a tylko elementy rekurencyjne mogą odwoływać się do samego CTE. Ponadto wszyscy członkowie muszą zwrócić tę samą liczbę kolumn z odpowiednimi typami danych.

teraz przyjrzyjmy się bliżej samym wypowiedziom., Pierwsza instrukcja, członek kotwicy, pobiera identyfikator pracownika, imię, nazwisko i identyfikator menedżera z tabeli pracownicy, gdzie ID menedżera jest null. To byłby pracownik na szczycie hierarchii, co oznacza, że ta osoba nie zgłasza się do nikogo. W związku z tym wartość ID menedżera jest null. Aby odzwierciedlić, że ta osoba jest na szczycie hierarchii, przypisuję wartość 1 do kolumny EmpLevel.

druga instrukcja w zapytaniu CTE – rekurencyjny członek-pobiera również identyfikator pracownika, imię, nazwisko i identyfikator menedżera dla pracowników w tabeli pracownicy., Zauważ jednak, że dołączam do stołu pracowników do samego CTE. Ponadto join opiera się na identyfikatorze menedżera w tabeli pracowników i identyfikatorze pracownika w CTE. W ten sposób CTE będzie w pętli przez tabelę pracowników, dopóki nie zwróci całej hierarchii.

jeszcze jedną rzeczą, którą należy zwrócić uwagę na drugą instrukcję jest to, że dla kolumny EmpLevel dodaję wartość 1 do wartości EmpLevel, tak jak pojawia się ona w CTE. W ten sposób za każdym razem, gdy deklaracja przechodzi przez hierarchię, następny poprawny poziom jest stosowany do pracowników na poziomie.,

Po zdefiniowaniu klauzuli my WITH, tworzę instrukcję SELECT, która pobiera dane z CTE. Należy jednak pamiętać, że w kolumnie Menedżer pobieram imię i nazwisko pracownika powiązanego z identyfikatorem menedżera w CTE. Dzięki temu mogę wyświetlić pełne imię i nazwisko menedżera dla każdego pracownika. Poniższa tabela przedstawia zestaw wyników zwracany przez instrukcję SELECT i jej CTE.,

jak widać, CTE, rekurencyjne lub nieekursywne, może być użytecznym narzędziem, gdy trzeba wygenerować tymczasowe zestawy wyników, do których można uzyskać dostęp za pomocą instrukcji SELECT, INSERT, UPDATE, DELETE lub MERGE. W pewnym sensie CTE jest jak pochodna tabela: nie jest przechowywany jako obiekt i jest ważny tylko podczas wykonywania instrukcji primary. Jednak w przeciwieństwie do tabeli pochodnej, CTE może być odwoływane wiele razy w zapytaniu i może być samo-odwołujące się. A co najlepsze, CTE są stosunkowo łatwe do wdrożenia.,

zauważyłeś, że Bob używa AdventureWorks2008 zamiast AdventureWorks. Jeśli wolisz uruchamiać te przykłady z bazą danych AdventureWorks, a nie z bazą danych AdventureWorks2008, powinieneś zmienić kolumnę Business Entityid na kolumnę SalesPersonID.