Recursive Queries Using Common Table Expressions
Typowe wyrażenie tabela (CTE) zapewnia znaczne korzyści można odwołać, powodując cyklicznej CTE.Cykliczne CTE jest jeden, w którym CTE początkowy jest wielokrotnie wykonywane zwraca podzbiór danych, aż do uzyskania zestaw wyników pełne.
Kwerendy jest określany jako cykliczne, kwerendę, gdy odwołuje się cyklicznej CTE.Zwracanie danych hierarchii jest powszechnym zastosowaniem kwerendy cykliczne, na przykład: Wyświetlanie pracowników schemat organizacyjny lub dane w BOM w scenariuszu materiałów produktu nadrzędnego zawiera jeden lub więcej składników i te składniki mogą z kolei mieć składniki podrzędne lub mogą być składnikami innych elementów nadrzędnych.
Cykliczne CTE może znacznie uprościć kodu wymaganych do uruchomienia kwerendy cyklicznej w instrukcja SELECT, INSERT, UPDATE, DELETE lub CREATE VIEW.We wcześniejszych wersjach SQL Server, zazwyczaj wymaga kwerendy cyklicznej za pomocą tabel tymczasowych, kursory i logikę do sterowania przepływem kroki cyklicznych. Aby uzyskać więcej informacji na temat typowych wyrażeń tabela zobacz Przy użyciu znanych wyrażenia tabela.
Struktura CTE cykliczne
Struktury rekurencyjne CTE w języku Transact-SQL jest podobna do procedury cyklicznych w innych językach programowania.Mimo że procedury cyklicznych w innych językach, zwraca wartość wartość skalarna, cykliczne CTE może zwracać wiele wierszy.
Cykliczne CTE składa się z trzech elementów:
Wywołanie ponownego.
Pierwsze uruchomienie cyklicznej CTE składa się z jednego lub więcej CTE_query_definitions połączone przez UNION ALL, UNION, z wyjątkiem lub przecinają się operatorów. Ponieważ tych definicji kwerendy tworzą zestaw wyników podstawowej struktury CTE, one są określane jako członkowie zakotwiczenia.
CTE_query_definitions są uważane za członków zakotwiczenia, chyba że odwołują CTE, sam.Wszystkich elementów członkowskich zakotwiczenia definicji kwerendy musi być umieszczony przed pierwszą definicji elementów członkowskich cykliczne, a następnie należy użyć operator UNION ALL do ostatniego zakotwiczony element członkowski z pierwszym składnikiem cyklicznych.
Wywołanie ponownego cykliczne.
Wywołanie cykliczne zawiera jeden lub więcej CTE_query_definitions połączone przez UNION ALL operatorów, które odwołują się CTE, sam. Definicje te kwerendy są określane jako elementy cykliczne.
Zakończenie wyboru.
Sprawdzenie wygaśnięcia jest niejawna; zatrzymuje rekursji, gdy wiersze nie są zwracane z poprzedniego wywołania.
Uwaga
Niepoprawnie wykonane cyklicznej CTE może spowodować, że nieskończoną pętlę.Na przykład jeśli definicja kwerendy rekurencyjne element członkowski członkowski zwraca te same wartości dla kolumn nadrzędnych i podrzędność, jest tworzony nieskończoną pętlę.Podczas testowania wyniki kwerendy cyklicznej, można ograniczyć liczbę poziomów rekursji dozwolona dla określonego sprawozdania przy użyciu wskazówka MAXRECURSION i wartość z przedziału od 0 do 32 767 w klauzula INSERT, UPDATE, DELETE lub instrukcja SELECT OPTION.Aby uzyskać więcej informacji zobacz Query Hints (Transact-SQL) i WITH common_table_expression (Transact-SQL).
Pseudocode i semantyka
Struktury rekurencyjne CTE musi zawierać co najmniej jeden zakotwiczony element członkowski z jednym elementem cyklicznych.Następujące pseudocode zawiera składniki proste cyklicznej CTE zawierający zakotwiczenia pojedynczego element członkowski członkowski i cyklicznych jednego element członkowski członkowski.
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
Semantyka wykonywanie cyklicznych jest następująca:
Podziel wyrażenie CTE do kotwicy i cyklicznych.
Uruchom zakotwiczony element członkowski tworzenia pierwszego wywołania lub podstawowy zestaw wyników (T0).
Uruchom członków cykliczną z TI jak dane wejściowe i Ti + 1 jako dane wyjściowe.
Powtórz krok 3, dopóki nie zostanie zwrócona pustego zestaw.
Należy zwrócić zestaw wyników.Jest to UNION ALL z T0 to Tn.
Przykład
W poniższym przykładzie pokazano przywracając hierarchiczną listę pracowników, począwszy od najwyższego pracownika klasyfikacji, w semantyce struktury rekurencyjnej CTE Adventure Works Cycles Firma. Instrukcja, która wykonuje CTE ogranicza zestaw wyników do pracowników w badania i rozwój grupy.Przegląd wykonywania kodu następującego przykładu.
Przykład kodu przewodnik
Cykliczne CTE, DirectReports, definiuje jedną zakotwiczony element członkowski z jednym elementem cyklicznych.
zakotwiczony element członkowski zwraca zestaw wyników podstawowej T0.Jest to najwyższy pracownika klasyfikacji w firmie; oznacza to, pracownik, który nie zgłasza się do menedżera.
W tym polu jest zestaw wyników zwróconych przez zakotwiczony element członkowski:
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ NULL 109 Chief Executive Officer 0element członkowski członkowski cyklicznej zwraca bezpośredniego subordinate(s) pracownika zestaw wyników zakotwiczenia.Można to osiągnąć przez operację łączyć między Employee Tabela i DirectReports CTE. Jest to odwołanie do CTE sam która ustanawia wywołania cyklicznych.Oparte na pracownika w CTE DirectReports jako dane wejściowe (TI), the łączyć (Employee.ManagerID = DirectReports.EmployeeID) zwraca jako dane wyjściowe (Ti + 1), pracowników, którzy mają (TI) jako menedżera.W związku z tym pierwsze powtórzenie element członkowski członkowski cyklicznych zwraca zestaw wyników, to:
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 109 12 Vice President of Engineering 1element członkowski członkowski cyklicznych została uaktywniona wielokrotnie.The second iteration of the recursive member uses the single-row result set in step 3 (containing EmployeeID12) as the input value, and returns this result set:
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 12 3 Engineering Manager 2The third iteration of the recursive member uses the single-row result set above (containing EmployeeID3) as the input value, and returns this result set:
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 3 4 Senior Tool Designer 3 3 9 Design Engineer 3 3 11 Design Engineer 3 3 158 Research and Development Manager 3 3 263 Senior Tool Designer 3 3 267 Senior Design Engineer 3 3 270 Design Engineer 3Czwarty iteracji element członkowski członkowski cyklicznej korzysta z poprzedniego zestaw wierszy EmployeeID wartości 4, 9, 11, 158, 263, 267, a 270 jako wartości wejściowej.
Proces ten powtarza się, dopóki element członkowski członkowski cyklicznej zwraca zestaw wyników puste.
Zestaw wyników końcowych zwracane przez kwerendy bieżących jest sumę wszystkich zestawów wyników wygenerowany przez zakotwiczenie i cyklicznych.
W tym polu jest zestaw wyników pełną zwróconych w przykładzie:
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ NULL 109 Chief Executive Officer 0 109 12 Vice President of Engineering 1 12 3 Engineering Manager 2 3 4 Senior Tool Designer 3 3 9 Design Engineer 3 3 11 Design Engineer 3 3 158 Research and Development Manager 3 3 263 Senior Tool Designer 3 3 267 Senior Design Engineer 3 3 270 Design Engineer 3 263 5 Tool Designer 4 263 265 Tool Designer 4 158 79 Research and Development Engineer 4 158 114 Research and Development Engineer 4 158 217 Research and Development Manager 4 (15 row(s) affected)