Sdílet prostřednictvím

Rekurzivní dotazy využívající běžné výrazy tabulek (Transact-SQL)

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)databáze SQL v Microsoft Fabric

Společný výraz tabulky (CTE) poskytuje významnou výhodu schopnosti odkazovat sám na sebe, a tím vytváří rekurzivní CTE. Rekurzivní CTE je ten, ve kterém se opakovaně spouští počáteční CTE, aby vracela podmnožiny dat, dokud se nezískala úplná sada výsledků.

Dotaz se označuje jako rekurzivní dotaz, když odkazuje na rekurzivní CTE. Návrat hierarchických dat je běžným využitím rekurzivních dotazů. Například zobrazení zaměstnanců v organizačním diagramu nebo data ve scénáři faktury k materiálům, ve kterém má nadřazený produkt jednu nebo více součástí a tyto komponenty můžou mít dílčí součásti nebo můžou být součástí jiných rodičů.

Rekurzivní CTE může výrazně zjednodušit kód potřebný ke spuštění rekurzivního dotazu v rámci příkazu SELECT, , INSERT, UPDATEDELETEnebo CREATE VIEW příkazu. V dřívějších verzích SQL Serveru rekurzivní dotaz obvykle vyžaduje použití dočasných tabulek, kurzorů a logiky k řízení toku rekurzivních kroků. Další informace o běžných výrazech tabulky najdete v tématu WITH common_table_expression.

V Microsoft Fabric, Fabric Data Warehouse a koncovém bodu SQL Analytics podporují standardní, sekvenční i vnořené cte, ale ne rekurzivní CTE.

Struktura rekurzivního CTE

Struktura rekurzivního CTE v Transact-SQL se podobá rekurzivním rutinám v jiných programovacích jazycích. I když rekurzivní rutina v jiných jazycích vrací skalární hodnotu, rekurzivní CTE může vrátit více řádků.

Rekurzivní CTE se skládá ze tří prvků:

  1. Vyvolání rutiny.

    První vyvolání rekurzivního CTE se skládá z jedné nebo více definic dotazů CTE spojených operátory UNION ALL, UNION, EXCEPTnebo INTERSECT operátory. Vzhledem k tomu, že tyto definice dotazů tvoří základní sadu výsledků struktury CTE, označují se jako členy ukotvení.

    Definice dotazů CTE se považují za ukotvené členy, pokud se na samotné CTE nedají odkazovat. Všechny definice dotazu členů ukotvení musí být umístěny před první rekurzivní definici členu a UNION ALL operátor musí být použit pro spojení posledního členu ukotvení s prvním rekurzivním členem.

  2. Rekurzivní vyvolání rutiny.

    Rekurzivní vyvolání zahrnuje jednu nebo více definic dotazů CTE spojených operátory UNION ALL , které odkazují na samotný CTE. Tyto definice dotazů se označují jako rekurzivní členy.

  3. Kontrola ukončení.

    Kontrola ukončení je implicitní; rekurze se zastaví, pokud se z předchozího vyvolání nevrátí žádné řádky.

Note

Nesprávně složený rekurzivní CTE může způsobit nekonečnou smyčku. Pokud například definice rekurzivního dotazu člena vrátí stejné hodnoty pro nadřazené i podřízené sloupce, vytvoří se nekonečná smyčka. Při testování výsledků rekurzivního dotazu můžete omezit počet úrovní rekurze povolených pro konkrétní příkaz pomocí MAXRECURSION nápovědy a hodnoty mezi 0 a 32 767 v OPTION klauzuli , INSERTUPDATE, DELETEnebo SELECT příkazu.

Další informace najdete tady:

Pseudokód a sémantika

Rekurzivní struktura CTE musí obsahovat alespoň jeden člen ukotvení a jeden rekurzivní člen. Následující pseudokód ukazuje komponenty jednoduché rekurzivní CTE, která obsahuje jeden člen ukotvení a jeden rekurzivní člen.

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

Sémantika rekurzivního provádění je následující:

  1. Rozdělte výraz CTE na ukotvení a rekurzivní členy.
  2. Spusťte členy ukotvení, které vytvoří první vyvolání nebo základní sadu výsledků (T0).
  3. Spusťte rekurzivní členy Ti jako vstup a Ti + 1 jako výstup.
  4. Opakujte krok 3, dokud se nevrátí prázdná sada.
  5. Vrátí sadu výsledků. To je o UNION ALLT0 tom, že Tn.

Examples

Následující příklad ukazuje sémantiku rekurzivní struktury CTE vrácením hierarchického seznamu zaměstnanců počínaje nejvyšším zaměstnancem v AdventureWorks2025 databázi. Návod ke spuštění kódu následuje v příkladu.

Vytvoření tabulky zaměstnanců:

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

Naplňte tabulku hodnotami:

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

Názorný postup ukázky kódu

Rekurzivní CTE DirectReportsdefinuje jeden člen ukotvení a jeden rekurzivní člen.

Člen ukotvení vrátí základní sadu T0výsledků . Toto je nejvyšší hodnocení zaměstnanců ve společnosti. To znamená, že zaměstnanec, který nehlásí manažerovi.

Tady je sada výsledků vrácená členem ukotvení:

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

Rekurzivní člen vrátí přímé podřízené zaměstnance v sadě výsledků členů ukotvení. Toho dosáhnete operací spojení mezi tabulkou Zaměstnanec a DirectReports CTE. Jedná se o odkaz na samotný CTE, který stanoví rekurzivní vyvolání. Na základě zaměstnance v CTE DirectReports jako vstup (Ti), spojení (MyEmployees.ManagerID = DirectReports.EmployeeID) vrátí jako výstup (Ti + 1), zaměstnanci, kteří mají (Ti) jako svého nadřízený .

Proto první iterace rekurzivního členu vrátí tuto sadu výsledků:

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

Rekurzivní člen je aktivován opakovaně. Druhá iterace rekurzivního členu jako vstupní hodnotu používá sadu výsledků s jedním řádkem v kroku 3 (obsahující hodnotu EmployeeID273) a vrátí tuto sadu výsledků:

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

Třetí iterace rekurzivního členu používá jako vstupní hodnotu předchozí sadu výsledků a vrátí tuto sadu výsledků:

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

Konečná sada výsledků vrácená spuštěným dotazem je sjednocení všech sad výsledků vygenerovaných ukotvením a rekurzivními členy.

Tady je soubor výsledků.

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

Související obsah

  • Last updated on