Megosztás a következőn keresztül:

Rekurzív lekérdezések gyakori táblakifejezésekkel (Transact-SQL)

Vonatkozik a következőkre:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalitikai Platform System (PDW)SQL adatbázis a Microsoft Fabric-ben

A közös táblakifejezés (CTE) jelentős előnye, hogy képes önmagára hivatkozni, így rekurzív CTE-t hoz létre. A rekurzív CTE olyan, amelyben a kezdeti CTE-t a rendszer ismételten végrehajtja az adatok részhalmazainak visszaadásához, amíg a teljes eredményhalmaz be nem fejeződik.

A lekérdezéseket rekurzív lekérdezésnek nevezzük, ha rekurzív CTE-re hivatkozik. A hierarchikus adatok visszaadása a rekurzív lekérdezések gyakori használata. Például egy szervezeti diagramon megjelenítheti az alkalmazottakat, vagy adatokat jeleníthet meg egy anyagjegyzék-forgatókönyvben, amelyben egy szülőtermék egy vagy több összetevővel rendelkezik, és ezek az összetevők más szülők alösszetevőivel vagy összetevőivel is rendelkezhetnek.

A rekurzív CTE jelentősen leegyszerűsítheti a rekurzív lekérdezések futtatásához szükséges kódot egy SELECT, INSERT, , UPDATE, DELETEvagy CREATE VIEW utasításon belül. Az SQL Server korábbi verzióiban a rekurzív lekérdezésekhez általában ideiglenes táblák, kurzorok és logika szükséges a rekurzív lépések folyamatának szabályozásához. A gyakori táblakifejezésekről további információt a WITH common_table_expression című témakörben talál.

A Microsoft Fabricben a Fabric Data Warehouse és az SQL Analytics-végpont egyaránt támogatja a standard, a szekvenciális és a beágyazott CTE-ket, de a rekurzív CT-ket nem.

Rekurzív CTE felépítése

A rekurzív CTE struktúrája Transact-SQL hasonló más programozási nyelvek rekurzív rutinjaihoz. Bár más nyelvek rekurzív rutinja skaláris értéket ad vissza, a rekurzív CTE több sort is visszaadhat.

A rekurzív CTE három elemből áll:

  1. A rutin meghívása.

    A rekurzív CTE első meghívása egy vagy több CTE-lekérdezésdefinícióból áll, amelyekhez UNION ALL, UNION, EXCEPTvagy INTERSECT operátorok csatlakoznak. Mivel ezek a lekérdezésdefiníciók alkotják a CTE-struktúra alaperedmény-készletét, horgonytagoknak nevezzük őket.

    A CTE-lekérdezésdefiníciók horgonytagoknak minősülnek, kivéve, ha magukra a CTE-ra hivatkoznak. Minden horgonytag-lekérdezésdefiníciót az első rekurzív tagdefiníció előtt kell elhelyezni, és egy UNION ALL operátort kell használni az utolsó horgonytag és az első rekurzív tag összekapcsolásához.

  2. A rutin rekurzív meghívása.

    A rekurzív hívás egy vagy több olyan CTE-lekérdezésdefiníciót tartalmaz, amelyet UNION ALL a CTE-re hivatkozó operátorok csatlakoztatnak. Ezeket a lekérdezésdefiníciókat rekurzív tagoknak nevezzük.

  3. Megszüntetési ellenőrzés.

    A megszüntetési ellenőrzés implicit; a rekurzió leáll, ha az előző hívásból nem ad vissza sorokat.

Note

A helytelenül összeállított rekurzív CTE végtelen ciklust okozhat. Ha például a rekurzív tag lekérdezésdefiníciója ugyanazokat az értékeket adja vissza a szülő- és gyermekoszlopok esetében is, akkor egy végtelen ciklus jön létre. Egy rekurzív lekérdezés eredményeinek tesztelése során korlátozhatja az adott utasításhoz engedélyezett rekurziós szintek számát a tipp és a 0 és 32 767 közötti érték használatával MAXRECURSION az OPTIONINSERT, UPDATE, DELETEvagy SELECT utasítás záradékában.

További információkért lásd:

Pszeudokód és szemantika

A rekurzív CTE-struktúrának tartalmaznia kell legalább egy horgonytagot és egy rekurzív tagot. Az alábbi pszeudokód egy egyszerű rekurzív CTE összetevőit mutatja be, amely egyetlen horgonytagot és egyetlen rekurzív tagot tartalmaz.

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

A rekurzív végrehajtás szemantikája a következő:

  1. Ossza fel a CTE-kifejezést horgony- és rekurzív tagokra.
  2. Futtassa a horgonytagokat, és hozza létre az első meghívási vagy alaperedmény-készletet (T0).
  3. Futtassa a rekurzív tagokat Ti bemenetként és Ti + 1 kimenetként.
  4. Ismételje meg a 3. lépést, amíg egy üres készletet nem ad vissza.
  5. Adja vissza az eredményhalmazt. Ez a UNION ALL feladata T0Tn.

Examples

Az alábbi példa a rekurzív CTE-struktúra szemantikáját mutatja be az alkalmazottak hierarchikus listájának visszaadásával, kezdve a legmagasabb rangú alkalmazottal az AdventureWorks2025 adatbázisban. A példa a kódvégrehajtás bemutatása.

Alkalmazotti tábla létrehozása:

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

Töltse ki a táblát értékekkel:

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

Példakód bemutatója

A rekurzív CTE DirectReportsegy horgonytagot és egy rekurzív tagot határoz meg.

A horgonytag az alap eredményhalmazt T0adja vissza. Ez a legmagasabb rangú alkalmazott a vállalatban. Ez azt jelenti, hogy egy alkalmazott, aki nem jelent egy vezetőnek.

A horgonytag az alábbi eredményhalmazt adja vissza:

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

A rekurzív tag a horgonytag eredményhalmazában az alkalmazott közvetlen alárendeltjeinek visszaadását adja vissza. Ez az Alkalmazott tábla és DirectReports a CTE közötti illesztési művelettel érhető el. Ez maga a CTE-re mutató hivatkozás, amely létrehozza a rekurzív meghívást. A CTE-ben DirectReportsTibemenetként () szereplő alkalmazott alapján az illesztés (MyEmployees.ManagerID = DirectReports.EmployeeID) kimenetként (Ti+ 1) adja vissza azokat az alkalmazottakat, akik (Ti) vezetőként rendelkeznek.

Ezért a rekurzív tag első iterációja a következő eredményhalmazt adja vissza:

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

A rekurzív tag ismételten aktiválódik. A rekurzív tag második iterációja a 3. lépésben megadott egysoros eredményhalmazt használja (az egyiket EmployeeID273tartalmazza) bemeneti értékként, és ezt az eredményhalmazt adja vissza:

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

A rekurzív tag harmadik iterációja az előző eredményhalmazt használja bemeneti értékként, és ezt az eredményhalmazt adja vissza:

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

A futó lekérdezés által visszaadott végső eredményhalmaz a horgony és a rekurzív tagok által létrehozott összes eredményhalmaz egyesítését jelenti.

Itt van az eredmények összessége.

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

Kapcsolódó tartalom

  • Last updated on