Rekursiva frågor med hjälp av vanliga tabelluttryck (Transact-SQL)

Gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalysplattformssystem (PDW)SQL-databas i Förhandsversion av Microsoft Fabric

Ett gemensamt tabelluttryck (CTE) ger den stora fördelen att kunna referera till sig själv, vilket skapar en rekursiv CTE. En rekursiv CTE är en där en första CTE körs upprepade gånger för att returnera delmängder av data tills den fullständiga resultatuppsättningen hämtas.

En fråga kallas för en rekursiv fråga när den refererar till en rekursiv CTE. Att returnera hierarkiska data är en vanlig användning av rekursiva frågor. Till exempel att visa anställda i ett organisationsschema, eller data i ett scenario med fakturamaterial där en överordnad produkt har en eller flera komponenter och dessa komponenter kan ha underkomponenter, eller kan vara komponenter, för andra överordnade.

En rekursiv CTE kan förenkla koden som krävs för att köra en rekursiv fråga i en SELECTinstruktion , INSERT, UPDATE, DELETEeller CREATE VIEW . I tidigare versioner av SQL Server kräver en rekursiv fråga vanligtvis att temporära tabeller, markörer och logik används för att styra flödet för de rekursiva stegen. Mer information om vanliga tabelluttryck finns i WITH common_table_expression.

I Microsoft Fabric stöder Fabric Data Warehouse och SQL-analysslutpunkten både standard, sekventiella och kapslade CTE:er, men inte rekursiva CTE:er.

Struktur för en rekursiv CTE

Strukturen för rekursiv CTE i Transact-SQL liknar rekursiva rutiner i andra programmeringsspråk. Även om en rekursiv rutin på andra språk returnerar ett skalärt värde kan en rekursiv CTE returnera flera rader.

En rekursiv CTE består av tre element:

1. Anrop av rutinen.

   Det första anropet av rekursiv CTE består av en eller flera CTE-frågedefinitioner som UNION ALLär kopplade till , UNION, EXCEPTeller INTERSECT operatorer. Eftersom dessa frågedefinitioner utgör basresultatuppsättningen för CTE-strukturen kallas de för fästpunktsmedlemmar.

   CTE-frågedefinitioner betraktas som fästpunktsmedlemmar om de inte refererar till själva CTE. Alla frågedefinitioner för fästpunktsmedlemmar måste placeras före den första rekursiva medlemsdefinitionen och en UNION ALL operator måste användas för att koppla den sista fästpunktsmedlemmen till den första rekursiva medlemmen.

2. Rekursiv anrop av rutinen.

   Det rekursiva anropet innehåller en eller flera CTE-frågedefinitioner som är kopplade till UNION ALL operatorer som refererar till själva CTE. Dessa frågedefinitioner kallas rekursiva medlemmar.

3. Avslutningskontroll.

   Avslutningskontrollen är implicit. rekursionen stoppas när inga rader returneras från föregående anrop.

Mer information finns i:

• Frågetips
• MED common_table_expression
• Rekursiva CTE:er

Pseudokod och semantik

Den rekursiva CTE-strukturen måste innehålla minst en fästpunktsmedlem och en rekursiv medlem. Följande pseudokod visar komponenterna i en enkel rekursiv CTE som innehåller en enda fästpunktsmedlem och en enda rekursiv medlem.

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

Semantiken för den rekursiva körningen är följande:

1. Dela upp CTE-uttrycket i fästpunkter och rekursiva medlemmar.
2. Kör fästpunktsmedlemmarna och skapa den första anrops- eller basresultatuppsättningen (T0).
3. Kör rekursiva medlemmar med Ti som indata och Ti + 1 som utdata.
4. Upprepa steg 3 tills en tom uppsättning returneras.
5. Returnera resultatuppsättningen. Detta är en UNION ALL av T0 till Tn.

Examples

I följande exempel visas semantiken i den rekursiva CTE-strukturen genom att returnera en hierarkisk lista över anställda, som börjar med den högst rankade medarbetaren AdventureWorks2022 , i databasen. En genomgång av kodkörningen följer exemplet.

Skapa en tabell med anställda:

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

Fyll i tabellen med värden:

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

Genomgång av exempelkod

Rekursiv CTE, DirectReports, definierar en fästpunktsmedlem och en rekursiv medlem.

Fästpunktsmedlemmen returnerar basresultatuppsättningen T0. Det här är den högst rankade medarbetaren i företaget. Det vill: en anställd som inte rapporterar till en chef.

Här är resultatuppsättningen som returneras av fästpunktsmedlemmen:

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

Den rekursiva medlemmen returnerar direkt underordnade till medarbetaren i resultatuppsättningen för fästpunktsmedlem. Detta uppnås genom en kopplingsåtgärd mellan tabellen Employee och DirectReports CTE. Det är den här referensen till själva CTE:t som etablerar det rekursiva anropet. Baserat på medarbetaren i CTE DirectReports som indata (Ti) returnerar kopplingen (MyEmployees.ManagerID = DirectReports.EmployeeID) som utdata (Ti + 1), de anställda som har (Ti) som chef.

Därför returnerar den första iterationen av den rekursiva medlemmen den här resultatuppsättningen:

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

Den rekursiva medlemmen aktiveras upprepade gånger. Den andra iterationen av den rekursiva medlemmen använder resultatuppsättningen för en rad i steg 3 (innehåller ett EmployeeID av 273) som indatavärde och returnerar den här resultatuppsättningen:

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

Den tredje iterationen av den rekursiva medlemmen använder den tidigare resultatuppsättningen som indatavärde och returnerar den här resultatuppsättningen:

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

Den slutliga resultatuppsättningen som returneras av den löpande frågan är en union av alla resultatuppsättningar som genereras av fästpunkten och rekursiva medlemmar.

Här är resultatet.

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

Relaterat innehåll

• MED common_table_expression (Transact-SQL)
• Frågetips (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• TA BORT (Transact-SQL)
• EXCEPT och INTERSECT (Transact-SQL)
• Rekursiva CTE:er