Artykuł
07/22/2011

Typowe wyrażenia tabeli za pomocą kwerend cyklicznych

Typowe wyrażenie tabela (CTE) zapewnia znaczne korzyści możliwość odwołania, powodując cykliczne CTE.Cykliczne CTE jest w którym początkowe CTE jest wielokrotnie wykonywane zwraca podzbiór danych, aż do uzyskania zestaw wyników pełną.

Kwerenda jest określana jako cykliczne kwerendy, kiedy odwołania cykliczne CTE.Zwracanie danych hierarchiczne jest wspólne korzystanie z kwerend rekurencyjnych na przykład: Wyświetlanie pracowników w schemacie organizacyjnym danych w BOM materiałów scenariusz, w którym produkt nadrzędny zawiera jeden lub więcej składników i te składniki mogą z kolei podskładniki mają lub mogą być składniki innych rodziców.

Cykliczne CTE można znacznie uprościć kod wymagany do uruchamiania kwerend cyklicznych w instrukcja SELECT, INSERT, aktualizacji, usuń lub tworzenie WIDOKU.W starszych wersjach SQL Server, zazwyczaj kwerendy cyklicznej wymaga przy użyciu tabel tymczasowych, kursory i logikę służącą do sterowania przepływem kroki cykliczne.Aby uzyskać informacje dotyczące typowych wyrażeń tabela, zobacz Przy użyciu typowych wyrażeń tabeli.

Struktura CTE cykliczne

Struktury rekurencyjne CTE w języku Transact-SQL jest podobna do procedury cyklicznych w innych językach programowania.Chociaż procedura 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 rutynowych.

Pierwsze wywołanie rekurencyjne CTE składa się z jednego lub więcej CTE_query_definitions przyłączony UNION ALL, Unia, oprócz, lub PRZECIĘCIA operatorów.Ponieważ definicje te 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ą się CTE, sam.Wszystkich definicji kwerendy Członkowskie kontrolnych musi być umieszczony przed pierwszą definicję składnika cykliczne, a operator UNION ALL musi być używane do łączyć ostatniego elementu kontrolnego z pierwszego element członkowski cykliczne.
Wywołanie rekurencyjne rutynowych.

Wywołanie cykliczne zawiera jeden lub więcej CTE_query_definitions połączonych operatorami UNION ALL, odwołujące się do CTE, sam.Definicje te kwerendy są określane jako członkowie cykliczne.
Zakończenie wyboru.

Sprawdź zakończenie jest niejawne; wiersze nie są zwracane z poprzedniego wywołania zatrzymanie rekursji.

Ostrzeżenie

Niepoprawnie składa cykliczne CTE może spowodować nieskończoną pętlę.Na przykład jeśli cykliczne element członkowski definicji kwerendy zwraca te same wartości nadrzędne i podrzędne kolumny, utworzyć nieskończoną pętlę.Podczas testowania wyniki kwerendy cyklicznej, można ograniczyć liczbę poziomów rekursji dla określonych instrukcja przy użyciu wskazówka MAXRECURSION i wartość z przedziału od 0 do 32 767 w klauzula INSERT, UPDATE opcjiUsuń lub wybierz instrukcja.Aby uzyskać więcej informacji, zobacz Wskazówki kwerendy (Transact-SQL) i Z common_table_expression (języka Transact-SQL).

Pseudocode i semantykę

Struktury rekurencyjne CTE musi zawierać co najmniej jeden zakotwiczony element członkowski i jednego członka cykliczne.Pseudocode następujące zawiera składniki proste cykliczne CTE, zawierający pojedynczy zakotwiczony element członkowski i pojedynczego rekurencyjna.

WITH cte_name ( column_name [,... n] )

(

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 wykonanie cykliczne jest następująca:

Podziel wyrażenie CTE do kotwicy i cykliczne członków.
Uruchom element(y) członkowski(e) zakotwiczenia Tworzenie pierwszego wywołania lub podstawowy zestaw wyników (T0).
Cykliczne uruchamianie element członkowski(s) z ti jako dane wejściowe i ti + 1 jako dane wyjściowe.
Powtórz krok 3 zwróciła pusty zestaw.
Zwraca zestaw wyników.Jest to Unii wszystkich z t0 tn.

Przykład

W poniższym przykładzie pokazano semantyka struktury rekurencyjnej CTE przez zwrócenie hierarchiczną listę pracowników, począwszy od najwyższego pracownika klasyfikacji w Adventure Works Cycles firmy.Instruktaż wykonanie kodu wynika przykładu.

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
    EmployeeID smallint NOT NULL,
    FirstName nvarchar(30)  NOT NULL,
    LastName  nvarchar(40) NOT NULL,
    Title nvarchar(50) NOT NULL,
    DeptID smallint NOT NULL,
    ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

USE AdventureWorks2008R2;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
        0 AS Level
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        Level + 1
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO

Przykład kodu Instruktaż

Cykliczne CTE, DirectReports, definiuje jeden zakotwiczony element członkowski i jednego członka cykliczne.
zakotwiczony element członkowski zwraca podstawy zestaw wyników t0.Jest to najwyższy Klasyfikacja pracownika w spółce; oznacza to, że pracownik nie zgłasza się do menedżera.

Oto zestaw wyników zwrócone przez członka kontrolnych:
```
ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer        0
```
Członek cykliczne zwraca bezpośredniego subordinate(s) pracownika w zakotwiczony element członkowski zestaw wyników.Jest to osiągane przez operację łączyć między Employee tabela i DirectReports CTE.Jest to odwołanie do CTE sobie, ustanawia wywołanie rekurencyjne.Oparte na pracownika w CTE DirectReports jako dane wejściowe (Ti), łączyć (MyEmployees.ManagerID = DirectReports.EmployeeID) zwraca jako produkcja globalna (Ti + 1), pracowników, którzy mają (Ti) jako menedżera.Dlatego pierwszej iteracji Członkowskie cykliczne zwraca to zestaw wyników:
```
ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
1         273        Vice President of Sales       1
```

element członkowski cykliczne jest uaktywniany wielokrotnie.The second iteration of the recursive member uses the single-row result set in step 3 (containing EmployeeID273) as the input value, and returns this result set:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2

Iteracja trzeciego element członkowski cykliczne używa zestaw wyników powyżej jako wartości wejściowej i zwraca wynik:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

Ostatni zestaw wyników zwracanych przez kwerendę uruchomionych jest Unii wszystkich zestaw wynikóws generowane przez członków zakotwiczenie i cykliczne.

Oto pełne zestaw wyników zwróconych w przykładzie:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer       0
1         273        Vice President of Sales       1
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3