Dela via

MED common_table_expression (Transact-SQL)

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-analysslutpunkt i Microsoft FabricWarehouse i Microsoft FabricSQL-databas i Microsoft Fabric

Anger en tillfällig namngiven resultatuppsättning, som kallas för ett gemensamt tabelluttryck (CTE). Detta härleds från en enkel fråga och definieras inom körningsomfånget för en enda SELECT, INSERT, UPDATE, MERGEeller DELETE -instruktion. Den här satsen kan också användas i en -instruktion som en CREATE VIEW del av dess definierande SELECT instruktion. Ett vanligt tabelluttryck kan innehålla referenser till sig själv. Detta kallas för ett rekursivt vanligt tabelluttryck.

Transact-SQL syntaxkonventioner

Syntax

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

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

Argumentpunkter

expression_name

En giltig identifierare för det vanliga tabelluttrycket. expression_name måste skilja sig från namnet på andra vanliga tabelluttryck som definierats i samma WITH <common_table_expression> sats, men expression_name kan vara samma som namnet på en bastabell eller vy. Alla referenser till expression_name i frågan använder det gemensamma tabelluttrycket och inte basobjektet.

column_name

Anger ett kolumnnamn i det gemensamma tabelluttrycket. Dubblettnamn inom en enda CTE-definition tillåts inte. Antalet kolumnnamn som anges måste matcha antalet kolumner i resultatuppsättningen för CTE_query_definition. Listan med kolumnnamn är endast valfri om distinkta namn för alla resulterande kolumner anges i frågedefinitionen.

CTE_query_definition

Anger en SELECT instruktion vars resultatuppsättning fyller det gemensamma tabelluttrycket. Instruktionen SELECT för CTE_query_definition måste uppfylla samma krav som för att skapa en vy, förutom att en CTE inte kan definiera en annan CTE. Mer information finns i avsnittet Anmärkningar och SKAPA VY.

Om fler än en CTE_query_definition har definierats måste frågedefinitionerna kopplas av någon av dessa uppsättningsoperatorer: UNION ALL, UNION, EXCEPTeller INTERSECT.

Användningsriktlinjer

Riktlinjer för icke-kursiva vanliga tabelluttryck

Anmärkning

Följande riktlinjer gäller för icke-aktuella vanliga tabelluttryck. Riktlinjer som gäller för rekursiva vanliga tabelluttryck finns i Riktlinjer för rekursiva vanliga tabelluttryck.

En CTE måste följas av en enda SELECT, INSERT, UPDATE, MERGEeller DELETE -instruktion som refererar till vissa eller alla CTE-kolumner. En CTE kan också anges i en -instruktion som en CREATE VIEW del av den definierande SELECT instruktionen för vyn.

Flera CTE-frågedefinitioner kan definieras i en icke-cursiv CTE. Definitionerna måste kombineras av någon av dessa uppsättningsoperatorer: UNION ALL, UNION, INTERSECT, eller EXCEPT.

En CTE kan referera till sig själv och tidigare definierade CTE:er i samma WITH sats. Vidarebefordran av hänvisningar tillåts inte.

Det är inte tillåtet att ange fler än en WITH sats i en CTE. Om en CTE_query_definition till exempel innehåller en underfråga kan den underfrågan inte innehålla en kapslad sats WITH som definierar en annan CTE.

Mer information om kapslade CTE:er i Microsoft Fabric finns i Kapslat common table expression (CTE) i Fabric-datalager (Transact-SQL).

Frågeresultat från vanliga tabelluttryck materialiseras inte. Varje yttre referens till den namngivna resultatuppsättningen kräver att den definierade frågan körs igen. Överväg att använda ett tillfälligt objekt i stället för frågor som kräver flera referenser till den namngivna resultatuppsättningen.

Du kan inte köra en lagrad procedur i ett gemensamt tabelluttryck.

Följande satser kan inte användas i CTE_query_definition:

  • ORDER BY (förutom när en TOP eller-sats OFFSET/FETCH anges)
  • INTO
  • OPTION sats med frågetips 1
  • FOR BROWSE

1 Satsen OPTION kan inte användas i en CTE-definition. Den kan bara användas i den yttersta SELECT instruktionen.

När en CTE används i en -sats som är en del av en batch måste -instruktionen innan den följas av ett semikolon.

En fråga som refererar till en CTE kan användas för att definiera en markör.

Tabeller på fjärrservrar kan refereras i CTE.

När du kör en CTE kan alla tips som refererar till en CTE komma i konflikt med andra tips som identifieras när CTE kommer åt sina underliggande tabeller, på samma sätt som tips som refererar till vyer i frågor. När detta inträffar returnerar frågan ett fel.

Riktlinjer för rekursiva vanliga tabelluttryck

Anmärkning

Följande riktlinjer gäller för att definiera ett rekursivt gemensamt tabelluttryck. Riktlinjer som gäller för icke-aktuella CTE:er finns i Riktlinjer för icke-kursiva vanliga tabelluttryck.

Den rekursiva CTE-definitionen måste innehålla minst två CTE-frågedefinitioner, en fästpunktsmedlem och en rekursiv medlem. Flera fästpunktsmedlemmar och rekursiva medlemmar kan definieras. Alla frågedefinitioner för fästpunktsmedlemmar måste dock placeras före den första rekursiva medlemsdefinitionen. Alla CTE-frågedefinitioner är fästpunktsmedlemmar om de inte refererar till själva CTE.

Fästpunktsmedlemmar måste kombineras av någon av dessa uppsättningsoperatorer: UNION ALL, UNION, INTERSECTeller EXCEPT. UNION ALL är den enda uppsättningsoperatorn som tillåts mellan den sista fästpunktsmedlemmen och den första rekursiva medlemmen och när flera rekursiva medlemmar kombineras.

Antalet kolumner i fästpunkten och rekursiva medlemmar måste vara detsamma.

Datatypen för en kolumn i den rekursiva medlemmen måste vara samma som datatypen för motsvarande kolumn i fästpunktsmedlemmen.

FROM-satsen för en rekursiv medlem får bara referera en gång till CTE-expression_name.

Följande objekt tillåts inte i CTE_query_definition för en rekursiv medlem:

  • SELECT DISTINCT
  • GROUP BY
  • PIVOT 1
  • HAVING
  • Scalar-aggregering
  • TOP
  • LEFT, RIGHT, OUTER JOIN (INNER JOIN tillåts)
  • Underfrågor
  • Ett tips som tillämpas på en rekursiv referens till en CTE i en CTE_query_definition.

1 När databasens kompatibilitetsnivå är 110 eller högre. Se Icke-bakåtkompatibla ändringar av databasmotorfunktioner i SQL Server 2016.

Följande riktlinjer gäller för användning av ett rekursivt gemensamt tabelluttryck:

  • Alla kolumner som returneras av den rekursiva CTE:en är nullbara oavsett om kolumnerna som returneras av de deltagande SELECT uttrycken är nulla.

  • En felaktigt sammansatt rekursiv CTE kan orsaka en oändlig loop. Om frågedefinitionen rekursiv medlem till exempel returnerar samma värden för både överordnade och underordnade kolumner skapas en oändlig loop. För att förhindra en oändlig loop kan du begränsa antalet rekursionsnivåer som tillåts för en viss instruktion med hjälp av tipset MAXRECURSION och ett värde mellan 0 och 32767 i -instruktionen OPTION i instruktionen INSERT, UPDATE, DELETEeller SELECT . På så sätt kan du styra körningen av -instruktionen tills du löser kodproblemet som skapar loopen. Standardvärdet för hela servern är 100. När 0 har angetts tillämpas ingen gräns. Endast ett MAXRECURSION värde kan anges per instruktion. Mer information finns i Frågetips.

  • En vy som innehåller ett rekursivt vanligt tabelluttryck kan inte användas för att uppdatera data.

  • Markörer kan definieras för frågor med hjälp av CTE:er. CTE är det select_statement argument som definierar markörens resultatuppsättning. Endast snabbsnabb framåtriktade och statiska markörer (ögonblicksbilder) tillåts för rekursiva CTE:er. Om en annan markörtyp anges i en rekursiv CTE konverteras markörtypen till statisk.

  • Tabeller på fjärrservrar kan refereras i CTE. Om fjärrservern refereras till i den rekursiva medlemmen i CTE skapas en spool för varje fjärrtabell så att tabellerna kan nås upprepade gånger lokalt. Om det är en CTE-fråga visas Index Spool/Lazy Spools i frågeplanen och har ytterligare WITH STACK predikat. Detta är ett sätt att bekräfta korrekt rekursion.

  • Analys- och aggregeringsfunktioner i den rekursiva delen av CTE tillämpas på uppsättningen för den aktuella rekursionsnivån och inte på uppsättningen för CTE. Funktioner som fungerar endast på den delmängd av data som ROW_NUMBER skickas till dem av den aktuella rekursionsnivån och inte hela uppsättningen data som skickas till den rekursiva delen av CTE. Mer information finns i exempel I. Använd analysfunktioner i en rekursiv CTE som följer.

Vanliga tabelluttryck i Azure Synapse Analytics and Analytics Platform System (PDW)

Den aktuella implementeringen av CTE:er i Azure Synapse Analytics and Analytics Platform System (PDW) har följande funktioner och krav:

  • En CTE kan anges i en SELECT -instruktion.

  • En CTE kan anges i en CREATE VIEW -instruktion.

  • En CTE kan anges i en CREATE TABLE AS SELECT (CTAS)-instruktion.

  • En CTE kan anges i en CREATE REMOTE TABLE AS SELECT (CRTAS)-instruktion.

  • En CTE kan anges i en CREATE EXTERNAL TABLE AS SELECT (CETAS)-instruktion.

  • En fjärrtabell kan refereras från en CTE.

  • En extern tabell kan refereras från en CTE.

  • Flera CTE-frågedefinitioner kan definieras i en CTE.

  • En CTE kan följas av SELECT, INSERT, UPDATE, DELETEeller MERGE -instruktioner.

  • Ett vanligt tabelluttryck som innehåller referenser till sig själv (ett rekursivt vanligt tabelluttryck) stöds inte.

  • Det är inte tillåtet att ange fler än en WITH sats i en CTE. Om en CTE-frågedefinition till exempel innehåller en underfråga kan den underfrågan inte innehålla en kapslad sats WITH som definierar en annan CTE.

  • Det ORDER BY går inte att använda en sats i CTE_query_definition, förutom när en TOP sats har angetts.

  • När en CTE används i en -sats som är en del av en batch måste -instruktionen innan den följas av ett semikolon.

  • När de används i instruktioner som utarbetats av sp_preparebeter sig CTE på samma sätt som andra SELECT instruktioner i APS PDW. Men om CTE:er används som en del av CETAS som förberetts av sp_preparekan beteendet skjutas upp från SQL Server och andra APS PDW-instruktioner på grund av hur bindningen implementeras för sp_prepare. Om SELECT det refererar till CTE använder en fel kolumn som inte finns i CTE, sp_prepare passerar utan att identifiera felet, men felet utlöses under sp_execute i stället.

Exempel

A. Skapa ett vanligt tabelluttryck

I följande exempel visas det totala antalet försäljningsorder per år för varje säljare på 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. Använd ett vanligt tabelluttryck för att begränsa antal och rapportgenomsnitt

I följande exempel visas det genomsnittliga antalet försäljningsorder för alla år för säljrepresentanterna.

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. Använda flera CTE-definitioner i en enda fråga

I följande exempel visas hur du definierar mer än en CTE i en enda fråga. Ett kommatecken används för att avgränsa CTE-frågedefinitionerna. Funktionen FORMAT , som används för att visa de monetära beloppen i valutaformat, introducerades i 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;

Här är en partiell resultatuppsättning.

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. Använd ett rekursivt vanligt tabelluttryck för att visa flera rekursionsnivåer

I följande exempel visas den hierarkiska listan över chefer och de anställda som rapporterar till dem. Exemplet börjar med att skapa och fylla i dbo.MyEmployees tabellen.

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

Använd ett rekursivt vanligt tabelluttryck för att visa två rekursionsnivåer

I följande exempel visas chefer och anställda som rapporterar till dem. Antalet returnerade nivåer är begränsat till två.

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;

Använd ett rekursivt vanligt tabelluttryck för att visa en hierarkisk lista

I följande exempel läggs namnen på chefen och anställda och deras respektive titlar till. Hierarkin för chefer och anställda betonas dessutom genom att dra in varje nivå.

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;

Använd MAXRECURSION för att avbryta en instruktion

MAXRECURSION kan användas för att förhindra att en dåligt utformad rekursiv CTE kommer in i en oändlig loop. I följande exempel skapas avsiktligt en oändlig loop och MAXRECURSION tips används för att begränsa antalet rekursionsnivåer till två.

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

När kodfelet har korrigerats krävs inte längre MAXRECURSION. I följande exempel visas den korrigerade koden.

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. Använd ett vanligt tabelluttryck för att selektivt gå igenom en rekursiv relation i en SELECT-instruktion

I följande exempel visas hierarkin för produktsammansättningar och komponenter som krävs för att bygga cykeln för ProductAssemblyID = 800.

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. Använda en rekursiv CTE i en UPDATE-instruktion

I följande exempel uppdateras PerAssemblyQty värdet för alla delar som används för att skapa produkten 'Road-550-W Yellow, 44' (ProductAssemblyID 800). Det gemensamma tabelluttrycket returnerar en hierarkisk lista över delar som används för att skapa ProductAssemblyID 800 och de komponenter som används för att skapa dessa delar och så vidare. Endast de rader som returneras av det gemensamma tabelluttrycket ändras.

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. Använda flera fästpunkter och rekursiva medlemmar

I följande exempel används flera fästpunkter och rekursiva medlemmar för att returnera alla överordnade till en angiven person. En tabell skapas och värden infogas för att upprätta familjegenologin som returneras av den rekursiva CTE:en.

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

Jag. Använda analysfunktioner i en rekursiv CTE

I följande exempel visas en fallgrop som kan inträffa när du använder en analys- eller aggregeringsfunktion i den rekursiva delen av en 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;

Följande resultat är de förväntade resultaten för frågan.

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

Följande resultat är de faktiska resultaten för frågan.

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

N returnerar 1 för varje passering av den rekursiva delen av CTE eftersom endast delmängden av data för den rekursionsnivån skickas till ROWNUMBER. För var och en av iterationerna i den rekursiva delen av frågan skickas endast en rad till ROWNUMBER.

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

J. Använda ett vanligt tabelluttryck i en CTAS-instruktion

I följande exempel skapas en ny tabell som innehåller det totala antalet försäljningsorder per år för varje säljare på 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. Använda ett vanligt tabelluttryck i en CETAS-instruktion

I följande exempel skapas en ny extern tabell som innehåller det totala antalet försäljningsorder per år för varje säljare på 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. Använda flera kommaavgränsade CTE:er i en instruktion

I följande exempel visas att två CTE:er ingår i en enda instruktion. CtEs kan inte kapslas (ingen rekursion).

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;

Relaterat innehåll