Delen via

WITH common_table_expression (Transact-SQL)

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

Hiermee geeft u een tijdelijke benoemde resultatenset op, ook wel een algemene tabelexpressie (CTE) genoemd. Dit is afgeleid van een eenvoudige query en gedefinieerd binnen het uitvoeringsbereik van één SELECT, INSERT, UPDATEof MERGEDELETE instructie. Deze component kan ook worden gebruikt in een CREATE VIEW instructie als onderdeel van de definitie-instructie SELECT . Een algemene tabelexpressie kan verwijzingen naar zichzelf bevatten. Dit wordt een recursieve algemene tabelexpressie genoemd.

Zie Recursieve query's met behulp van algemene tabelexpressies voor meer informatie.

Transact-SQL syntaxis-conventies

Syntax

[ WITH <common_table_expression> [ , ...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ , ...n ] ) ]
    AS
    ( CTE_query_definition )

Arguments

expression_name

Een geldige id voor de algemene tabelexpressie. expression_name moet afwijken van de naam van een andere algemene tabelexpressie die in dezelfde WITH <common_table_expression> component is gedefinieerd, maar expression_name kan dezelfde zijn als de naam van een basistabel of -weergave. Elke verwijzing naar expression_name in de query maakt gebruik van de algemene tabelexpressie en niet van het basisobject.

column_name

Hiermee geeft u een kolomnaam in de algemene tabelexpressie. Dubbele namen binnen één CTE-definitie zijn niet toegestaan. Het aantal opgegeven kolomnamen moet overeenkomen met het aantal kolommen in de resultatenset van de CTE_query_definition. De lijst met kolomnamen is alleen optioneel als afzonderlijke namen voor alle resulterende kolommen worden opgegeven in de querydefinitie.

CTE_query_definition

Hiermee geeft u een SELECT instructie waarvan de resultatenset de algemene tabelexpressie vult. De SELECT instructie voor CTE_query_definition moet voldoen aan dezelfde vereisten als voor het maken van een weergave, behalve dat een CTE geen andere CTE kan definiëren. Zie de sectie Opmerkingen en CREATE VIEW voor meer informatie.

Als er meer dan één CTE_query_definition is gedefinieerd, moeten de querydefinities worden toegevoegd door een van deze setoperators: UNION ALL, UNION, EXCEPTof INTERSECT.

Usage guidelines

Queryresultaten van algemene tabelexpressies worden niet gerealiseerd. Voor elke externe verwijzing naar de benoemde resultatenset moet de gedefinieerde query opnieuw worden uitgevoerd. Voor query's waarvoor meerdere verwijzingen naar de benoemde resultatenset zijn vereist, kunt u in plaats daarvan een tijdelijk object gebruiken.

U kunt een opgeslagen procedure niet uitvoeren in een algemene tabelexpressie.

Zie de volgende secties voor gebruiksrichtlijnen voor recursieve en niet-recursieve CTE's.

Richtlijnen voor niet-recursieve algemene tabelexpressies

Note

De volgende richtlijnen zijn van toepassing op niet-recursieve algemene tabelexpressies. Zie Richtlijnen voor recursieve algemene tabelexpressies voor richtlijnen die van toepassing zijn op recursieve algemene tabelexpressies.

Een CTE moet worden gevolgd door één SELECT, INSERT, UPDATEof MERGEDELETE een instructie die verwijst naar enkele of alle CTE-kolommen. Een CTE kan ook worden opgegeven in een CREATE VIEW instructie als onderdeel van de definiërende SELECT instructie van de weergave.

Er kunnen meerdere CTE-querydefinities worden gedefinieerd in een niet-recursieve CTE. De definities moeten worden gecombineerd door een van deze setoperators: UNION ALL, UNION, INTERSECTof EXCEPT.

Een CTE kan verwijzen naar zichzelf en eerder gedefinieerde CTE's in dezelfde WITH component. Forward referencing is niet toegestaan.

Het opgeven van meer dan één WITH component in een CTE is niet toegestaan. Als een CTE_query_definition bijvoorbeeld een subquery bevat, kan die subquery geen geneste WITH component bevatten waarmee een andere CTE wordt gedefinieerd.

Zie Nested Common Table Expression (CTE) in Fabric datawarehousing (Transact-SQL) voor meer informatie over geneste CTE's in Microsoft Fabric.

De volgende componenten kunnen niet worden gebruikt in de CTE_query_definition:

  • ORDER BY (behalve wanneer een TOP of OFFSET/FETCH component is opgegeven)
  • INTO
  • OPTION component met queryhints 1
  • FOR BROWSE

1 De OPTION component kan niet worden gebruikt binnen een CTE-definitie. Het kan alleen worden gebruikt in de buitenste SELECT instructie.

Wanneer een CTE wordt gebruikt in een instructie die deel uitmaakt van een batch, moet de instructie worden gevolgd door een puntkomma.

Een query die verwijst naar een CTE, kan worden gebruikt om een cursor te definiëren.

In de CTE kunnen naar tabellen op externe servers worden verwezen.

Bij het uitvoeren van een CTE kunnen hints die verwijzen naar een CTE conflicteren met andere hints die worden gedetecteerd wanneer de CTE de onderliggende tabellen opent, op dezelfde manier als hints die verwijzen naar weergaven in query's. Wanneer dit gebeurt, retourneert de query een fout.

Richtlijnen voor recursieve algemene tabelexpressies

Note

De volgende richtlijnen zijn van toepassing op het definiëren van een recursieve algemene tabelexpressie. Zie Richtlijnen voor niet-recursieve tabelexpressies voor richtlijnen die van toepassing zijn op niet-recursieve CTE's.

De recursieve CTE-definitie moet ten minste twee CTE-querydefinities, een ankerlid en een recursief lid bevatten. Er kunnen meerdere ankerleden en recursieve leden worden gedefinieerd; Alle querydefinities voor ankerleden moeten echter vóór de eerste recursieve liddefinitie worden geplaatst. Alle CTE-querydefinities zijn ankerleden, tenzij ze verwijzen naar de CTE zelf.

Ankerleden moeten worden gecombineerd door een van deze setoperators: UNION ALL, UNION, INTERSECTof EXCEPT. UNION ALL is de enige operator die is toegestaan tussen het laatste ankerlid en het eerste recursieve lid en bij het combineren van meerdere recursieve leden.

Het aantal kolommen in het anker en recursieve leden moet hetzelfde zijn.

Het gegevenstype van een kolom in het recursieve lid moet hetzelfde zijn als het gegevenstype van de bijbehorende kolom in het ankerlid.

De FROM component van een recursief lid mag slechts één keer verwijzen naar de CTE-expression_name.

De volgende items zijn niet toegestaan in de CTE_query_definition van een recursief lid:

  • SELECT DISTINCT
  • GROUP BY
  • PIVOT 1
  • HAVING
  • Scalar aggregation
  • TOP
  • LEFT, , RIGHTOUTER JOIN (INNER JOIN is toegestaan)
  • Subqueries
  • Een hint die is toegepast op een recursieve verwijzing naar een CTE binnen een CTE_query_definition.

1 Wanneer het compatibiliteitsniveau van de database 110 of hoger is. Zie Belangrijke wijzigingen in database-enginefuncties in SQL Server 2016.

De volgende richtlijnen zijn van toepassing op het gebruik van een recursieve algemene tabelexpressie:

  • Alle kolommen die door de recursieve CTE worden geretourneerd, zijn null-waarde, ongeacht de null-waarde van de kolommen die door de deelnemende SELECT instructies worden geretourneerd.

  • 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. Als u een oneindige lus wilt voorkomen, kunt u het aantal recursieniveaus beperken dat is toegestaan voor een bepaalde instructie met behulp van de MAXRECURSION hint en een waarde tussen 0 en 32767 in de component van de OPTIONINSERT, of UPDATEDELETE instructieSELECT. Hiermee kunt u de uitvoering van de instructie beheren totdat u het codeprobleem oplost dat de lus maakt. De standaardinstelling voor de hele server is 100. Wanneer 0 is opgegeven, wordt er geen limiet toegepast. Er kan slechts één MAXRECURSION waarde per instructie worden opgegeven. Zie Hints voor query's voor meer informatie.

  • Een weergave die een recursieve algemene tabelexpressie bevat, kan niet worden gebruikt om gegevens bij te werken.

  • Cursors kunnen worden gedefinieerd voor query's met behulp van CTE's. De CTE is het select_statement argument waarmee de resultatenset van de cursor wordt gedefinieerd. Alleen snelle voorwaartse en statische cursors (momentopnamen) zijn toegestaan voor recursieve CTE's. Als een ander cursortype is opgegeven in een recursieve CTE, wordt het cursortype geconverteerd naar statisch.

  • In de CTE kunnen naar tabellen op externe servers worden verwezen. Als naar de externe server wordt verwezen in het recursieve lid van de CTE, wordt er een spool gemaakt voor elke externe tabel, zodat de tabellen herhaaldelijk lokaal kunnen worden geopend. Als het een CTE-query is, worden index-Spool-/luie Spools weergegeven in het queryplan en worden de extra WITH STACK predicaat toegevoegd. Dit is een manier om de juiste recursie te bevestigen.

  • Analytische en statistische functies in het recursieve deel van de CTE worden toegepast op de set voor het huidige recursieniveau en niet op de set voor de CTE. Functies werken zoals ROW_NUMBER alleen op de subset van gegevens die eraan worden doorgegeven door het huidige recursieniveau en niet de volledige set gegevens die worden doorgegeven aan het recursieve deel van de CTE. Zie voorbeeld I voor meer informatie. Gebruik analytische functies in een recursieve CTE die volgt.

Algemene tabelexpressies in Azure Synapse Analytics en Analytics Platform System (PDW)

De huidige implementatie van CTE's in Azure Synapse Analytics en Analytics Platform System (PDW) heeft de volgende functies en vereisten:

  • Een CTE kan worden opgegeven in een SELECT instructie.

  • Een CTE kan worden opgegeven in een CREATE VIEW instructie.

  • Een CTE kan worden opgegeven in een CREATE TABLE AS SELECT (CTAS)-instructie.

  • Een CTE kan worden opgegeven in een CREATE REMOTE TABLE AS SELECT (CRTAS)-instructie.

  • Een CTE kan worden opgegeven in een CREATE EXTERNAL TABLE AS SELECT (CETAS)-instructie.

  • Er kan naar een externe tabel worden verwezen vanuit een CTE.

  • Er kan naar een externe tabel worden verwezen vanuit een CTE.

  • Er kunnen meerdere CTE-querydefinities worden gedefinieerd in een CTE.

  • Een CTE kan worden gevolgd doorSELECT, INSERT, UPDATEof DELETEMERGE instructies.

  • Een algemene tabelexpressie die verwijzingen naar zichzelf bevat (een recursieve algemene tabelexpressie) wordt niet ondersteund.

  • Het opgeven van meer dan één WITH component in een CTE is niet toegestaan. Als een CTE-querydefinitie bijvoorbeeld een subquery bevat, kan die subquery geen geneste WITH component bevatten waarmee een andere CTE wordt gedefinieerd.

  • Een ORDER BY component kan niet worden gebruikt in de CTE_query_definition, behalve wanneer er een TOP component is opgegeven.

  • Wanneer een CTE wordt gebruikt in een instructie die deel uitmaakt van een batch, moet de instructie worden gevolgd door een puntkomma.

  • Bij gebruik in instructies die door sp_prepareCTE's zijn voorbereid, gedragen CTE's zich op dezelfde manier als andere SELECT instructies in APS PDW. Als CTE's echter worden gebruikt als onderdeel van CETAS die door sp_prepareis voorbereid, kan het gedrag worden uitgesteld van SQL Server en andere APS PDW-instructies vanwege de manier waarop binding wordt geïmplementeerd voor sp_prepare. Als SELECT deze verwijst naar CTE een verkeerde kolom gebruikt die niet in CTE bestaat, worden de sp_prepare slaagt zonder de fout te detecteren, maar wordt de fout in sp_execute plaats daarvan gegenereerd.

Examples

A. Een algemene tabelexpressie maken

In het volgende voorbeeld ziet u het totale aantal verkooporders per jaar voor elke verkoopvertegenwoordiger bij Adventure Works Cycles.

-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID,
       COUNT(SalesOrderID) AS TotalSales,
       SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;

B. Een algemene tabelexpressie gebruiken om het aantal en rapportgemiddelden te beperken

In het volgende voorbeeld ziet u het gemiddelde aantal verkooporders voor alle jaren voor de verkoopmedewerkers.

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
    SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;

C. Meerdere CTE-definities in één query gebruiken

In het volgende voorbeeld ziet u hoe u meer dan één CTE in één query definieert. Er wordt een komma gebruikt om de CTE-querydefinities te scheiden. De FORMAT functie, die wordt gebruikt om de monetaire bedragen in een valutanotatie weer te geven, is geïntroduceerd in SQL Server 2012 (11.x).

WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)
AS
-- Define the first CTE query.
(
    SELECT SalesPersonID,
           SUM(TotalDue) AS TotalSales,
           YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID, YEAR(OrderDate)
), -- Use a comma to separate multiple CTE definitions.

-- Define the second CTE query, which returns sales quota data by year for each sales person.
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)
AS
(
    SELECT BusinessEntityID,
           SUM(SalesQuota) AS SalesQuota,
           YEAR(QuotaDate) AS SalesQuotaYear
    FROM Sales.SalesPersonQuotaHistory
    GROUP BY BusinessEntityID, YEAR(QuotaDate)
)
-- Define the outer query by referencing columns from both CTEs.
SELECT SalesPersonID,
       SalesYear,
       FORMAT(TotalSales, 'C', 'en-us') AS TotalSales,
       SalesQuotaYear,
       FORMAT(SalesQuota, 'C', 'en-us') AS SalesQuota,
       FORMAT(TotalSales - SalesQuota, 'C', 'en-us') AS Amt_Above_or_Below_Quota
FROM Sales_CTE
     INNER JOIN Sales_Quota_CTE
         ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID
        AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear
ORDER BY SalesPersonID, SalesYear;

Hier volgt een gedeeltelijke resultatenset.

SalesPersonID SalesYear   TotalSales    SalesQuotaYear SalesQuota  Amt_Above_or_Below_Quota
------------- ---------   -----------   -------------- ---------- ----------------------------------
274           2005        $32,567.92    2005           $35,000.00  ($2,432.08)
274           2006        $406,620.07   2006           $455,000.00 ($48,379.93)
274           2007        $515,622.91   2007           $544,000.00 ($28,377.09)
274           2008        $281,123.55   2008           $271,000.00  $10,123.55

D. Een recursieve algemene tabelexpressie gebruiken om meerdere niveaus van recursie weer te geven

In het volgende voorbeeld ziet u de hiërarchische lijst met managers en de werknemers die aan hen rapporteren. Het voorbeeld begint met het maken en vullen van de dbo.MyEmployees tabel.

-- Create an Employee table.
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 SMALLINT NULL,
    CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC),
    CONSTRAINT FK_MyEmployees_ManagerID_EmployeeID FOREIGN KEY (ManagerID) REFERENCES dbo.MyEmployees (EmployeeID)
);

-- Populate the table with values.
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);

WITH DirectReports (ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
ORDER BY ManagerID;

Een recursieve algemene tabelexpressie gebruiken om twee niveaus van recursie weer te geven

In het volgende voorbeeld ziet u managers en de werknemers die aan hen rapporteren. Het aantal geretourneerde niveaus is beperkt tot twee.

WITH DirectReports (ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
WHERE EmployeeLevel <= 2;

Een recursieve algemene tabelexpressie gebruiken om een hiërarchische lijst weer te geven

In het volgende voorbeeld worden de namen van de manager en werknemers en hun respectieve titels toegevoegd. De hiërarchie van managers en werknemers wordt bovendien benadrukt door elk niveau te laten inspringen.

WITH DirectReports (Name, Title, EmployeeID, EmployeeLevel, Sort) AS
(
    SELECT CONVERT (VARCHAR (255), e.FirstName + ' ' + e.LastName),
           e.Title,
           e.EmployeeID,
           1,
           CONVERT (VARCHAR (255), e.FirstName + ' ' + e.LastName)
    FROM dbo.MyEmployees AS e
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT (VARCHAR (255), REPLICATE('|    ', EmployeeLevel) + e.FirstName + ' ' + e.LastName),
           e.Title,
           e.EmployeeID,
           EmployeeLevel + 1,
           CONVERT (VARCHAR (255), RTRIM(Sort) + '|    ' + FirstName + ' ' + LastName)
    FROM dbo.MyEmployees AS e
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID
)
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;

MAXRECURSION gebruiken om een instructie te annuleren

MAXRECURSION kan worden gebruikt om te voorkomen dat een slecht gevormde recursieve CTE een oneindige lus binnenkomt. In het volgende voorbeeld wordt opzettelijk een oneindige lus gemaakt en wordt de MAXRECURSION hint gebruikt om het aantal recursieniveaus te beperken tot twee.

--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.MyEmployees
    WHERE ManagerID IS NOT NULL
    UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte
         INNER JOIN dbo.MyEmployees AS e
             ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);

Nadat de coderingsfout is gecorrigeerd, is MAXRECURSION niet meer vereist. In het volgende voorbeeld ziet u de gecorrigeerde code.

WITH cte (EmployeeID, ManagerID, Title) AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.MyEmployees
    WHERE ManagerID IS NOT NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Title
    FROM dbo.MyEmployees AS e
         INNER JOIN cte
             ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;

E. Een algemene tabelexpressie gebruiken om selectief een recursieve relatie in een SELECT-instructie te doorlopen

In het volgende voorbeeld ziet u de hiërarchie van productassembly's en onderdelen die nodig zijn om de fiets voor ProductAssemblyID = 800te bouwen.

USE AdventureWorks2022;
GO

WITH Parts (AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID,
           b.ComponentID,
           b.PerAssemblyQty,
           b.EndDate,
           0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID,
           bom.ComponentID,
           p.PerAssemblyQty,
           bom.EndDate,
           ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
         INNER JOIN Parts AS p
             ON bom.ProductAssemblyID = p.ComponentID
            AND bom.EndDate IS NULL
)
SELECT AssemblyID,
       ComponentID,
       Name,
       PerAssemblyQty,
       EndDate,
       ComponentLevel
FROM Parts AS p
     INNER JOIN Production.Product AS pr
         ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;

F. Een recursieve CTE gebruiken in een UPDATE-instructie

In het volgende voorbeeld wordt de PerAssemblyQty waarde bijgewerkt voor alle onderdelen die worden gebruikt om het product 'Road-550-W Yellow, 44' (ProductAssemblyID 800)te bouwen. De algemene tabelexpressie retourneert een hiërarchische lijst met onderdelen die worden gebruikt voor het bouwen ProductAssemblyID 800 en de onderdelen die worden gebruikt om deze onderdelen te maken, enzovoort. Alleen de rijen die door de algemene tabelexpressie worden geretourneerd, worden gewijzigd.

USE AdventureWorks2022;
GO

WITH Parts (AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID,
           b.ComponentID,
           b.PerAssemblyQty,
           b.EndDate,
           0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID,
           bom.ComponentID,
           p.PerAssemblyQty,
           bom.EndDate,
           ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
         INNER JOIN Parts AS p
             ON bom.ProductAssemblyID = p.ComponentID
            AND bom.EndDate IS NULL
)
UPDATE Production.BillOfMaterials
    SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
     INNER JOIN Parts AS d
         ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;

H. Meerdere ankers en recursieve leden gebruiken

In het volgende voorbeeld worden meerdere ankers en recursieve leden gebruikt om alle voorouders van een opgegeven persoon te retourneren. Er wordt een tabel gemaakt en waarden ingevoegd om de familiezinnigheid vast te stellen die wordt geretourneerd door de recursieve CTE.

-- Genealogy table
IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
DROP TABLE dbo.Person;
GO

CREATE TABLE dbo.Person
(
    ID INT,
    Name VARCHAR (30),
    Mother INT,
    Father INT
);
GO

INSERT dbo.Person VALUES
(1, 'Sue', NULL, NULL),
(2, 'Ed', NULL, NULL),
(3, 'Emma', 1, 2),
(4, 'Jack', 1, 2),
(5, 'Jane', NULL, NULL),
(6, 'Bonnie', 5, 4),
(7, 'Bill', 5, 4);
GO

-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
    -- First anchor member returns Bonnie's mother.
    SELECT Mother
    FROM dbo.Person
    WHERE Name = 'Bonnie'
    UNION
    -- Second anchor member returns Bonnie's father.
    SELECT Father
    FROM dbo.Person
    WHERE Name = 'Bonnie'
    UNION ALL
    -- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID = Person.ID
    UNION ALL
    -- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, dbo.Person
    WHERE Generation.ID = Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, dbo.Person
WHERE Generation.ID = Person.ID;
GO

I. Analytische functies gebruiken in een recursieve CTE

In het volgende voorbeeld ziet u een valkuil die kan optreden bij het gebruik van een analytische of statistische functie in het recursieve deel van een CTE.

DECLARE @t1 TABLE (itmID INT, itmIDComp INT);
INSERT @t1 VALUES (1, 10), (2, 10);

DECLARE @t2 TABLE (itmID INT, itmIDComp INT);
INSERT @t2 VALUES (3, 10), (4, 10);

WITH vw AS
(
    SELECT itmIDComp, itmID FROM @t1
    UNION ALL SELECT itmIDComp, itmID FROM @t2
),
r AS
(
    SELECT t.itmID AS itmIDComp,
           NULL AS itmID,
           CAST (0 AS BIGINT) AS N,
           1 AS Lvl
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t(itmID)
    UNION ALL
    SELECT t.itmIDComp,
           t.itmID,
           ROW_NUMBER() OVER (PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N,
           Lvl + 1
    FROM r
         INNER JOIN vw AS t
             ON t.itmID = r.itmIDComp
)
SELECT Lvl, N FROM r;

De volgende resultaten zijn de verwachte resultaten voor de query.

Lvl  N
1    0
1    0
1    0
1    0
2    4
2    3
2    2
2    1

De volgende resultaten zijn de werkelijke resultaten voor de query.

Lvl  N
1    0
1    0
1    0
1    0
2    1
2    1
2    1
2    1

N retourneert 1 voor elke pass van het recursieve deel van de CTE, omdat alleen de subset van gegevens voor dat recursieniveau wordt doorgegeven aan ROWNUMBER. Voor elk van de iteraties van het recursieve deel van de query wordt slechts één rij doorgegeven aan ROWNUMBER.

Voorbeelden: Azure Synapse Analytics and Analytics Platform System (PDW)

J. Een algemene tabelexpressie gebruiken binnen een CTAS-instructie

In het volgende voorbeeld wordt een nieuwe tabel gemaakt met het totale aantal verkooporders per jaar voor elke verkoopvertegenwoordiger bij Adventure Works Cycles.

USE AdventureWorks2022;
GO

CREATE TABLE SalesOrdersPerYear
WITH (DISTRIBUTION = HASH(SalesPersonID)) AS
    -- Define the CTE expression name and column list.
    WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS
    -- Define the CTE query.
    (
        SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
        FROM Sales.SalesOrderHeader
        WHERE SalesPersonID IS NOT NULL
    )
    -- Define the outer query referencing the CTE name.
    SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
    FROM Sales_CTE
    GROUP BY SalesYear, SalesPersonID
    ORDER BY SalesPersonID, SalesYear;
GO

K. Een algemene tabelexpressie gebruiken binnen een CETAS-instructie

In het volgende voorbeeld wordt een nieuwe externe tabel gemaakt met het totale aantal verkooporders per jaar voor elke verkoopvertegenwoordiger bij Adventure Works Cycles.

USE AdventureWorks2022;
GO
CREATE EXTERNAL TABLE SalesOrdersPerYear
WITH
(
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:5000/files/Customer',
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|' )
) AS
    -- Define the CTE expression name and column list.
    WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS
    -- Define the CTE query.
    (
        SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
        FROM Sales.SalesOrderHeader
        WHERE SalesPersonID IS NOT NULL
    )
    -- Define the outer query referencing the CTE name.
    SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
    FROM Sales_CTE
    GROUP BY SalesYear, SalesPersonID
    ORDER BY SalesPersonID, SalesYear;
GO

L. Meerdere door komma's gescheiden CTE's gebruiken in een instructie

In het volgende voorbeeld ziet u hoe twee CTE's in één instructie worden opgenomen. De CTE's kunnen niet worden genest (geen recursie).

WITH CountDate (TotalCount, TableName) AS
(
    SELECT COUNT(datekey), 'DimDate' FROM DimDate
),
CountCustomer (TotalAvg, TableName) AS
(
    SELECT COUNT(CustomerKey), 'DimCustomer' FROM DimCustomer
)
SELECT TableName, TotalCount
FROM CountDate
UNION ALL
SELECT TableName, TotalAvg FROM CountCustomer;

Related content