Delen via

Recursieve query's met veelgebruikte tabelexpressies (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-database in Microsoft Fabric Preview

Een algemene tabelexpressie (CTE) biedt het aanzienlijke voordeel dat u naar zichzelf kunt verwijzen, waardoor een recursieve CTE ontstaat. Een recursieve CTE is een CTE waarin een eerste CTE herhaaldelijk wordt uitgevoerd om subsets met gegevens te retourneren totdat de volledige resultatenset is verkregen.

Een query wordt een recursieve query genoemd wanneer deze verwijst naar een recursieve CTE. Het retourneren van hiërarchische gegevens is een veelvoorkomend gebruik van recursieve query's. Bijvoorbeeld het weergeven van werknemers in een organigram of gegevens in een stuk materiaalscenario waarin een bovenliggend product een of meer onderdelen heeft en die onderdelen mogelijk subonderdelen hebben, of onderdelen zijn van andere ouders.

Een recursieve CTE kan de code die nodig is voor het uitvoeren van een recursieve query in een SELECT, INSERT, UPDATEof DELETECREATE VIEWinstructie aanzienlijk vereenvoudigen. In eerdere versies van SQL Server vereist een recursieve query meestal het gebruik van tijdelijke tabellen, cursors en logica om de stroom van de recursieve stappen te beheren. Zie WITH common_table_expression voor meer informatie over veelgebruikte tabelexpressies.

In Microsoft Fabric ondersteunen Fabric Data Warehouse en het SQL-analyse-eindpunt zowel standaard, sequentiële als geneste CTE's, maar niet recursieve CTE's.

Structuur van een recursieve CTE

De structuur van de recursieve CTE in Transact-SQL is vergelijkbaar met recursieve routines in andere programmeertalen. Hoewel een recursieve routine in andere talen een scalaire waarde retourneert, kan een recursieve CTE meerdere rijen retourneren.

Een recursieve CTE bestaat uit drie elementen:

  1. Aanroep van de routine.

    De eerste aanroep van de recursieve CTE bestaat uit een of meer CTE-querydefinities die zijn toegevoegd doorUNION ALL, UNIONof EXCEPTINTERSECT operators. Omdat deze querydefinities de basisresultatenset van de CTE-structuur vormen, worden ze ankerleden genoemd.

    CTE-querydefinities worden beschouwd als ankerleden, tenzij ze verwijzen naar de CTE zelf. Alle querydefinities voor ankerleden moeten vóór de eerste recursieve liddefinitie worden geplaatst en een UNION ALL operator moet worden gebruikt om het laatste ankerlid samen te voegen met het eerste recursieve lid.

  2. Recursieve aanroep van de routine.

    De recursieve aanroep bevat een of meer CTE-querydefinities die zijn gekoppeld door UNION ALL operators die verwijzen naar de CTE zelf. Deze querydefinities worden recursieve leden genoemd.

  3. Beëindigingscontrole.

    De beëindigingscontrole is impliciet; recursie stopt wanneer er geen rijen worden geretourneerd uit de vorige aanroep.

Note

Een onjuist samengestelde recursieve CTE kan een oneindige lus veroorzaken. Als de recursieve lidquerydefinitie bijvoorbeeld dezelfde waarden retourneert voor zowel de bovenliggende als de onderliggende kolommen, wordt er een oneindige lus gemaakt. Wanneer u de resultaten van een recursieve query test, kunt u het aantal recursieniveaus beperken dat is toegestaan voor een specifieke instructie met behulp van de MAXRECURSION hint en een waarde tussen 0 en 32.767 in de component van de OPTIONINSERTUPDATE, of DELETESELECT instructie.

Voor meer informatie, zie:

Pseudocode en semantiek

De recursieve CTE-structuur moet ten minste één ankerlid en één recursief lid bevatten. De volgende pseudocode toont de onderdelen van een eenvoudige recursieve CTE die één ankerlid en één recursief lid bevat.

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

De semantiek van de recursieve uitvoering is als volgt:

  1. Splits de CTE-expressie in anker- en recursieve leden.
  2. Voer de ankerleden uit die de eerste aanroep of basisresultatenset (T0) maken.
  3. Voer de recursieve leden uit met Ti als invoer en Ti + 1 als uitvoer.
  4. Herhaal stap 3 totdat een lege set wordt geretourneerd.
  5. De resultatenset retourneren. Dit is een UNION ALL van T0 .Tn

Examples

In het volgende voorbeeld ziet u de semantiek van de recursieve CTE-structuur door een hiërarchische lijst met werknemers te retourneren, te beginnen met de hoogste rangschikkingsmedewerker in de AdventureWorks2022 database. Een overzicht van de code-uitvoering volgt het voorbeeld.

Een werknemertabel maken:

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

Vul de tabel in met waarden:

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

Overzicht van voorbeeldcode

De recursieve CTE, DirectReportsdefinieert één ankerlid en één recursief lid.

Het ankerlid retourneert de basisresultatenset T0. Dit is de hoogste rangschikkingsmedewerker in het bedrijf. Dat wil gezegd: een werknemer die niet rapporteert aan een manager.

Dit is de resultatenset die wordt geretourneerd door het ankerlid:

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

Het recursieve lid retourneert de directe ondergeschikten van de werknemer in de resultatenset van het ankerlid. Dit wordt bereikt door een joinbewerking tussen de tabel Werknemer en de DirectReports CTE. Dit is deze verwijzing naar de CTE zelf die de recursieve aanroep tot stand brengt. Op basis van de werknemer in de CTE DirectReports als invoer (Ti), retourneert de join (MyEmployees.ManagerID = DirectReports.EmployeeID) als uitvoer (Ti+ 1), de werknemers die () als manager hebben.Ti

Daarom retourneert de eerste iteratie van het recursieve lid deze resultatenset:

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

Het recursieve lid wordt herhaaldelijk geactiveerd. De tweede iteratie van het recursieve lid maakt gebruik van de resultatenset met één rij in stap 3 (met een EmployeeID van 273) als invoerwaarde en retourneert deze resultatenset:

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

De derde iteratie van het recursieve lid gebruikt de vorige resultatenset als invoerwaarde en retourneert deze resultatenset:

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

De uiteindelijke resultatenset die door de actieve query wordt geretourneerd, is de samenvoeging van alle resultatensets die worden gegenereerd door het anker en recursieve leden.

Hier is het resultatenoverzicht.

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

Verwante inhoud