Udostępnij za pomocą

Zapytania cykliczne używające typowych wyrażeń tabeli (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Database w Microsoft Fabric

Wspólne wyrażenie tabeli (CTE) zapewnia znaczącą zaletę możliwości odwoływanie się do samego siebie, tworząc rekursywną metodę CTE. Rekursywny CTE jest jednym, w którym początkowy CTE jest wielokrotnie wykonywany w celu zwrócenia podzestawów danych do momentu uzyskania kompletnego zestawu wyników.

Zapytanie jest określane jako cykliczne zapytanie, gdy odwołuje się do cyklicznego CTE. Zwracanie danych hierarchicznych jest typowym zastosowaniem zapytań cyklicznych. Na przykład wyświetlanie pracowników na wykresie organizacyjnym lub dane w scenariuszu rozliczania materiałów, w którym produkt nadrzędny ma co najmniej jeden składnik, a te składniki mogą mieć podskładniki lub mogą być składnikami innych elementów nadrzędnych.

Rekursywna funkcja CTE może znacznie uprościć kod wymagany do uruchomienia zapytania cyklicznego w instrukcji SELECT, INSERT, UPDATE, DELETElub CREATE VIEW . We wcześniejszych wersjach programu SQL Server zapytanie cykliczne zwykle wymaga użycia tabel tymczasowych, kursorów i logiki w celu kontrolowania przepływu kroków cyklicznych. Aby uzyskać więcej informacji na temat typowych wyrażeń tabeli, zobacz WITH common_table_expression .

W usłudze Microsoft Fabric usługa Fabric Data Warehouse i punkt końcowy analizy SQL obsługują standardowe, sekwencyjne i zagnieżdżone wartości CTE, ale nie rekursywne wartości CTE.

Struktura cyklicznego CTE

Struktura cyklicznego CTE w Transact-SQL jest podobna do cyklicznych procedur w innych językach programowania. Mimo że cyklisywna rutyna w innych językach zwraca wartość skalarną, cyklicznego CTE może zwracać wiele wierszy.

Rekursywny CTE składa się z trzech elementów:

  1. Wywołanie rutyny.

    Pierwsze wywołanie rekursywnego CTE składa się z co najmniej jednej definicji zapytania CTE połączonej przez UNION ALLoperatory , UNION, EXCEPTlub INTERSECT . Ponieważ te definicje zapytań tworzą podstawowy zestaw wyników struktury CTE, są one określane jako elementy członkowskie kotwicy.

    Definicje zapytań CTE są traktowane jako elementy członkowskie kotwicy, chyba że odwołują się do samego CTE. Wszystkie definicje zapytań składowych kotwicy muszą być umieszczone przed pierwszą definicją cyklicznej składowej, a UNION ALL operator musi być używany do łączenia ostatniego elementu członkowskiego zakotwiczenia z pierwszym elementem cyklicznym.

  2. Rekursywne wywołanie procedury.

    Wywołanie cykliczne obejmuje co najmniej jedną definicję zapytania CTE połączoną przez UNION ALL operatory odwołujące się do samego CTE. Te definicje zapytań są określane jako rekursywne elementy członkowskie.

  3. Sprawdzanie zakończenia.

    Sprawdzanie zakończenia jest niejawne; rekursja zatrzymuje się, gdy nie są zwracane żadne wiersze z poprzedniego wywołania.

Note

Niepoprawnie skomponowana rekursywna pętla CTE może spowodować nieskończoną pętlę. Jeśli na przykład cykliczna definicja zapytania członkowskiego zwraca te same wartości zarówno dla kolumn nadrzędnych, jak i podrzędnych, zostanie utworzona nieskończona pętla. Podczas testowania wyników zapytania cyklicznego można ograniczyć liczbę poziomów rekursji dozwolonych dla określonej instrukcji przy użyciu MAXRECURSION wskazówki i wartości z zakresu od 0 do 32 767 w OPTION klauzuli INSERTUPDATE, , DELETElub SELECT instrukcji.

Aby uzyskać więcej informacji, zobacz:

Pseudokod i semantyka

Cykliczna struktura CTE musi zawierać co najmniej jeden element członkowski kotwicy i jeden element rekursywny. Poniższy pseudokod przedstawia składniki prostego rekursywnego CTE, który zawiera jeden element członkowski kotwicy i pojedynczy element rekursywny.

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 wykonywania cyklicznego jest następująca:

  1. Podziel wyrażenie CTE na składowe kotwice i rekursywne.
  2. Uruchom elementy członkowskie zakotwiczenia, tworząc pierwsze wywołanie lub podstawowy zestaw wyników (T0).
  3. Uruchom cykliczne elementy członkowskie Ti jako dane wejściowe i Ti + 1 jako dane wyjściowe.
  4. Powtórz krok 3, aż zostanie zwrócony pusty zestaw.
  5. Zwróć zestaw wyników. Jest to wartość typu UNION ALL do T0Tn .

Examples

W poniższym przykładzie pokazano semantyka cyklicznej struktury CTE, zwracając hierarchiczną listę pracowników, począwszy od najwyższego rangi AdventureWorks2025 pracownika w bazie danych. Przewodnik po wykonaniu kodu jest zgodny z przykładem.

Utwórz tabelę pracowników:

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)
);

Wypełnij tabelę wartościami:

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ładowy przewodnik po kodzie

Rekursywna funkcja CTE , DirectReportsdefiniuje jeden element członkowski kotwicy i jeden rekursywny element członkowski.

Element członkowski kotwicy zwraca podstawowy zestaw T0wyników . Jest to najwyższy rangą pracownik w firmie. Oznacza to, że pracownik, który nie zgłasza się do menedżera.

Oto zestaw wyników zwrócony przez element członkowski kotwicy:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer        0

Cykliczny element członkowski zwraca bezpośrednich podwładnych pracownika w zestawie wyników elementu członkowskiego zakotwiczenia. Jest to osiągane przez operację sprzężenia między tabelą Employee i DirectReports CTE. To odwołanie do samego obiektu CTE, które ustanawia cykliczne wywołanie. Na podstawie pracownika w CTE DirectReports jako danych wejściowych (Ti), sprzężenie (MyEmployees.ManagerID = DirectReports.EmployeeID) zwraca jako dane wyjściowe (Ti + 1), pracowników, którzy mają (Ti) jako kierownika.

W związku z tym pierwsza iteracja rekursywnego elementu członkowskiego zwraca ten zestaw wyników:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
1         273        Vice President of Sales       1

Cykliczny element członkowski jest wielokrotnie aktywowany. Druga iteracja elementu członkowskiego cyklicznego używa zestawu wyników z jednym wierszem w kroku 3 (zawierającego EmployeeID273element ) jako wartość wejściową i zwraca ten zestaw wyników:

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

Trzecia iteracja elementu członkowskiego cyklicznego używa poprzedniego zestawu wyników jako wartości wejściowej i zwraca ten zestaw wyników:

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

Końcowy zestaw wyników zwracany przez uruchomione zapytanie jest połączeniem wszystkich zestawów wyników generowanych przez zakotwiczenie i cyklicznych elementów członkowskich.

Oto zestaw wyników.

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

Treści powiązane

  • Last updated on