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

WITH common_table_expression (Transact-SQL)

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példányAzure Synapse AnalyticsElemzési platformrendszer (PDW)SQL Analytics-végpont a Microsoft FabricbenRaktár a Microsoft FabricbenSQL-adatbázis a Microsoft Fabricben

Egy ideiglenes elnevezett eredménykészletet határoz meg, amelyet közös táblakifejezésnek (CTE) nevezünk. Ez egy egyszerű lekérdezésből származik, és egyetlen SELECT, , INSERT, UPDATE, MERGEvagy DELETE utasítás végrehajtási hatókörén belül van definiálva. Ez a záradék egy CREATE VIEW utasításban is használható a meghatározó SELECT utasítás részeként. A gyakori táblakifejezések magukra mutató hivatkozásokat is tartalmazhatnak. Ezt rekurzív gyakori táblakifejezésnek nevezzük.

További információ: Gyakori táblakifejezéseket használó rekurzív lekérdezések.

Transact-SQL szintaxis konvenciók

Syntax

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

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

Arguments

expression_name

A közös táblakifejezés érvényes azonosítója. expression_name különböznie kell az ugyanabban WITH <common_table_expression> a záradékban definiált többi közös táblakifejezés nevétől, de expression_name ugyanaz lehet, mint egy alaptábla vagy nézet neve. A lekérdezésben szereplő expression_name hivatkozás a közös táblakifejezést használja, nem pedig az alapobjektumot.

column_name

Egy oszlopnevet ad meg a közös táblakifejezésben. Egyetlen CTE-definícióban nem lehet ismétlődő neveket létrehozni. A megadott oszlopnevek számának meg kell egyeznie a CTE_query_definition eredményhalmazában szereplő oszlopok számával. Az oszlopnevek listája csak akkor választható, ha az összes eredményként kapott oszlop neve szerepel a lekérdezésdefinícióban.

CTE_query_definition

Olyan utasítást SELECT ad meg, amelynek eredményhalmaza kitölti a közös táblakifejezést. A SELECTCTE_query_definition utasításának meg kell felelnie a nézet létrehozásához szükséges követelményeknek, kivéve, ha a CTE nem definiálhat másik CTE-t. További információkért tekintse meg a Megjegyzések szakaszt és a CREATE VIEW nézetet.

Ha egynél több CTE_query_definition van definiálva, a lekérdezésdefiníciókat a következő halmaz-operátorok egyikének kell összekapcsolnia: UNION ALL, UNION, EXCEPTvagy INTERSECT.

Használati irányelvek

A gyakori táblakifejezések lekérdezési eredményei nem materializáltak. A névvel ellátott eredményhalmazra mutató minden külső hivatkozáshoz újra végre kell hajtani a definiált lekérdezést. Az olyan lekérdezések esetében, amelyek több hivatkozást igényelnek az elnevezett eredményhalmazra, fontolja meg inkább egy ideiglenes objektum használatát.

Nem hajthat végre tárolt eljárást egy közös táblakifejezésben.

A rekurzív és a nem rekurzív CT-kkel kapcsolatos használati irányelvekért tekintse meg a következő szakaszokat.

Nem rekurzív gyakori táblakifejezések irányelvei

Note

Az alábbi irányelvek nem rekurzív gyakori táblakifejezésekre vonatkoznak. A rekurzív közös táblakifejezésekre vonatkozó irányelvekért tekintse meg a rekurzív gyakori táblakifejezések irányelveit.

A CTE-t egyetlen SELECT, , INSERT, UPDATE, MERGEvagy DELETE utasításnak kell követnie, amely a CTE-oszlopok egy részét vagy egészét hivatkozik. A CTE egy utasításban CREATE VIEW is megadható a nézet definiáló SELECT utasításának részeként.

Több CTE-lekérdezésdefiníció definiálható nem rekurzív CTE-ben. A definíciókat a következő operátorok egyikével kell kombinálni: UNION ALL, UNION, INTERSECTvagy EXCEPT.

A CTE hivatkozhat magára és a korábban definiált CTE-kre ugyanabban WITH a záradékban. A továbbítási hivatkozás nem engedélyezett.

A CTE-ben egynél WITH több záradék megadása nem engedélyezett. Ha például egy CTE_query_definition alqueryt tartalmaz, akkor az alquery nem tartalmazhat beágyazott WITH záradékot, amely egy másik CTE-t határoz meg.

A Beágyazott CTE-kkel kapcsolatos további információkért lásd: Beágyazott közös táblakifejezés (CTE) a Fabric adattárházakban (Transact-SQL).

A következő záradékok nem használhatók a CTE_query_definition:

  • ORDER BY (kivéve, ha egy TOP vagy OFFSET/FETCH több záradék van megadva)
  • INTO
  • OPTION záradék lekérdezési tippekkel 1
  • FOR BROWSE

1 A OPTION záradék nem használható CTE-definícióban. Csak a legkülső SELECT utasításban használható.

Ha CTE-t használ egy köteg részét képező utasításban, az előtte lévő utasítást pontosvesszővel kell követni.

A CTE-ra hivatkozó lekérdezések a kurzor definiálására használhatók.

A távoli kiszolgálókon lévő táblákra a CTE-ben lehet hivatkozni.

CTE végrehajtásakor a CTE-ra hivatkozó tippek ütközhetnek más, a CTE alapjául szolgáló táblák elérésekor felfedezett tippekkel, ugyanúgy, mint a lekérdezésekben lévő nézetekre hivatkozó tippek. Ha ez történik, a lekérdezés hibát ad vissza.

Rekurzív gyakori táblakifejezések irányelvei

Note

A rekurzív közös táblakifejezések meghatározására az alábbi irányelvek vonatkoznak. A nem rekurzív CTE-kre vonatkozó irányelvekért tekintse meg a nem rekurzív gyakori táblakifejezések irányelveit.

A rekurzív CTE-definíciónak tartalmaznia kell legalább két CTE-lekérdezésdefiníciót, egy horgonytagot és egy rekurzív tagot. Több horgonytag és rekurzív tag definiálható; Azonban minden horgonytag-lekérdezésdefiníciót az első rekurzív tagdefiníció elé kell helyezni. Minden CTE-lekérdezésdefiníció horgonytag, hacsak nem hivatkoznak magára a CTE-ra.

A horgonytagokat a következő operátorok egyikével kell kombinálni: UNION ALL, UNION, INTERSECTvagy EXCEPT. UNION ALL az utolsó horgonytag és az első rekurzív tag között engedélyezett egyetlen beállítási operátor, több rekurzív tag kombinálásakor.

A horgonyban és a rekurzív tagokban lévő oszlopok számának meg kell egyeznie.

A rekurzív tag oszlopának adattípusának meg kell egyeznie a horgonytag megfelelő oszlopának adattípusával.

A FROM rekurzív tagok záradékának csak egyszer kell hivatkoznia a CTE expression_name.

A rekurzív tagok CTE_query_definition a következő elemek nem engedélyezettek:

  • SELECT DISTINCT
  • GROUP BY
  • PIVOT 1
  • HAVING
  • Skaláris aggregáció
  • TOP
  • LEFT, RIGHT, OUTER JOIN (INNER JOIN engedélyezett)
  • Subqueries
  • Egy CTE_query_definition belüli CTE rekurzív hivatkozására alkalmazott tipp.

1 Ha az adatbázis kompatibilitási szintje 110 vagy magasabb. Tekintse meg az SQL Server 2016 adatbázismotor-funkcióinak kompatibilitástörő változásait.

A rekurzív gyakori táblakifejezések használatára az alábbi irányelvek vonatkoznak:

  • A rekurzív CTE által visszaadott összes oszlop null értékű, függetlenül a részt vevő SELECT utasítások által visszaadott oszlopok null értékétől.

  • 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. A végtelen ciklus megakadályozása érdekében korlátozhatja egy adott utasításhoz engedélyezett rekurziós szintek számát a MAXRECURSION tipp és az 0 , 32767, OPTIONvagy INSERT utasítás záradéka UPDATEközötti DELETE és SELECT az abban szereplő érték használatával. Ez lehetővé teszi az utasítás végrehajtásának szabályozását, amíg meg nem oldja a ciklust létrehozó kódhibát. A kiszolgálószintű alapértelmezett érték 100. Ha 0 van megadva, a rendszer nem alkalmaz korlátot. Utasításonként csak egy MAXRECURSION érték adható meg. További információ: Lekérdezési tippek.

  • A rekurzív gyakori táblakifejezéseket tartalmazó nézet nem használható adatok frissítésére.

  • A kurzorok a CTE-k használatával definiálhatók lekérdezéseken. A CTE a kurzor eredményhalmazát meghatározó select_statement argumentum. A rekurzív CTE-k esetében csak gyors előremutató és statikus (pillanatkép) kurzorok engedélyezettek. Ha egy rekurzív CTE-ben egy másik kurzortípus van megadva, a rendszer statikussá alakítja a kurzortípust.

  • A távoli kiszolgálókon lévő táblákra a CTE-ben lehet hivatkozni. Ha a távoli kiszolgálóra a CTE rekurzív tagja hivatkozik, a rendszer minden távoli táblához létrehoz egy spoolt, hogy a táblák helyileg ismételten elérhetők legyenek. Ha CTE-lekérdezésről van szó, akkor az Index Spool/Lazy Spools megjelenik a lekérdezéstervben, és a további WITH STACK predikátum is megjelenik. Ez az egyik módszer a megfelelő rekurzió megerősítésére.

  • A CTE rekurzív részében lévő elemzési és aggregátumfüggvényeket a rendszer az aktuális rekurziós szint halmazára alkalmazza, a CTE-hez tartozó készletre nem. Az olyan függvények, mint ROW_NUMBER az aktuális rekurziós szint által nekik átadott adatok csak részhalmazán működnek, a CTE rekurzív részére átadott teljes adatkészlet nem. További információ: I. Példa: Elemzési függvények használata a következő rekurzív CTE-ben.

Az Azure Synapse Analytics and Analytics Platform System (PDW) általános táblakifejezései

Az Azure Synapse Analytics and Analytics Platform System (PDW) cte-jeinek jelenlegi implementálása a következő funkciókkal és követelményekkel rendelkezik:

  • A CTE egy utasításban SELECT adható meg.

  • A CTE egy utasításban CREATE VIEW adható meg.

  • A CTE egy (CTAS) utasításban CREATE TABLE AS SELECT adható meg.

  • A CTE megadható egy CREATE REMOTE TABLE AS SELECT (CRTAS) utasításban.

  • A CTE megadható egy CREATE EXTERNAL TABLE AS SELECT (CETAS) utasításban.

  • Egy távoli táblára hivatkozhat egy CTE-ből.

  • Egy külső táblára hivatkozhat egy CTE-ből.

  • A CTE-ben több CTE-lekérdezésdefiníció is definiálható.

  • A CTE-t SELECTkövethetik , INSERT, UPDATE, DELETEvagy MERGE utasítások.

  • A saját magára mutató hivatkozásokat (rekurzív közös táblakifejezést) tartalmazó közös táblakifejezések nem támogatottak.

  • A CTE-ben egynél WITH több záradék megadása nem engedélyezett. Ha például egy CTE-lekérdezésdefiníció alqueryt tartalmaz, akkor az alkérés nem tartalmazhat beágyazott WITH záradékot, amely egy másik CTE-t definiál.

  • A ORDER BY záradék nem használható a CTE_query_definition, kivéve, ha egy TOP záradék meg van adva.

  • Ha CTE-t használ egy köteg részét képező utasításban, az előtte lévő utasítást pontosvesszővel kell követni.

  • A cte-k az általuk készített sp_prepareutasításokban ugyanúgy viselkednek, mint az APS PDW más SELECT utasításai. Ha azonban a CETAS által sp_prepareelőkészített CETAS részeként használják a CTE-ket, a viselkedés késleltethető az SQL Serverről és más APS PDW-utasításokból a kötés implementálásának sp_preparemódja miatt. Ha SELECT a CTE nem megfelelő oszlopot használ, amely nem létezik a CTE-ben, a sp_prepare hiba észlelése nélkül halad át, de a hiba a rendszer közben sp_execute jelenik meg.

Examples

A. Közös táblakifejezés létrehozása

Az alábbi példa az Adventure Works Cycles egyes értékesítési képviselőinek évi értékesítési rendeléseinek teljes számát mutatja be.

-- 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. Gyakori táblakifejezés használata a számok és a jelentés átlagainak korlátozásához

Az alábbi példa az értékesítési képviselők összes évre vonatkozó értékesítési rendeléseinek átlagos számát mutatja.

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. Több CTE-definíció használata egyetlen lekérdezésben

Az alábbi példa bemutatja, hogyan definiálhat több CTE-t egyetlen lekérdezésben. A CTE-lekérdezésdefiníciók elválasztására vesszőt használunk. A FORMAT pénzösszegek pénznemformátumban való megjelenítéséhez használt függvény az SQL Server 2012-ben (11.x) lett bevezetve.

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;

Íme egy részleges eredményhalmaz.

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. Rekurzív gyakori táblakifejezés használata több rekurziós szint megjelenítéséhez

Az alábbi példa a vezetők és a nekik jelentést készítő alkalmazottak hierarchikus listáját mutatja be. A példa a tábla létrehozásával és feltöltésével dbo.MyEmployees kezdődik.

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

Rekurzív gyakori táblakifejezés használata két rekurziós szint megjelenítéséhez

Az alábbi példa a vezetőket és a nekik jelentett alkalmazottakat mutatja be. A visszaadott szintek száma kettőre korlátozódik.

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;

Hierarchikus lista megjelenítése rekurzív gyakori táblakifejezéssel

Az alábbi példa hozzáadja a vezető és az alkalmazottak nevét, valamint a hozzájuk tartozó címeket. A vezetők és az alkalmazottak hierarchiáját az egyes szintek behúzásával is hangsúlyozzuk.

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;

Utasítás lemondása a MAXRECURSION használatával

MAXRECURSION segítségével megakadályozhatja, hogy egy rosszul formázott rekurzív CTE végtelen hurokba lépjen. Az alábbi példa szándékosan létrehoz egy végtelen hurkot, és a MAXRECURSION tipp használatával a rekurziós szintek számát kettőre korlátozza.

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

A kódolási hiba kijavítása után már nincs szükség MAXRECURSION. Az alábbi példa a javított kódot mutatja be.

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. Gyakori táblakifejezés használata rekurzív kapcsolat szelektív végiglépéséhez a SELECT utasításban

Az alábbi példa a kerékpár ProductAssemblyID = 800összeállításához szükséges termékszerelvények és összetevők hierarchiáját mutatja be.

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. Rekurzív CTE használata UPDATE utasításban

Az alábbi példa frissíti a PerAssemblyQty termék 'Road-550-W Yellow, 44' (ProductAssemblyID 800)összeállításához használt összes alkatrész értékét. A közös táblakifejezés a buildeléshez ProductAssemblyID 800 használt részek hierarchikus listáját adja vissza, valamint az ezen részek létrehozásához használt összetevőket stb. A rendszer csak a közös táblakifejezés által visszaadott sorokat módosítja.

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. Több horgony és rekurzív tag használata

Az alábbi példa több horgony- és rekurzív tagot használ egy adott személy összes elődjének visszaadásához. Létrejön egy tábla, és beszúrja az értékeket a rekurzív CTE által visszaadott családi genealógiához.

-- 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. Elemzési függvények használata rekurzív CTE-ben

Az alábbi példa egy olyan buktatót mutat be, amely akkor fordulhat elő, ha a CTE rekurzív részében analitikus vagy aggregátumfüggvényt használ.

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;

A következő eredmények a lekérdezés várt eredményei.

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

Az alábbi eredmények a lekérdezés tényleges eredményei.

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

NA CTE rekurzív részének minden egyes passzára 1 értéket ad vissza, mert a rendszer csak az adott rekurziós szinthez tartozó adathalmazt adja át.ROWNUMBER A lekérdezés rekurzív részének minden iterációjához csak egy sort ad át a ROWNUMBERrendszer.

Példák: Azure Synapse Analytics and Analytics Platform System (PDW)

J. Gyakori táblakifejezés használata CTAS-utasításban

Az alábbi példa egy új táblát hoz létre, amely az Adventure Works Cycles egyes értékesítési képviselőinek évi értékesítési rendeléseinek teljes számát tartalmazza.

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. Közös táblakifejezés használata CETAS-utasításban

Az alábbi példa egy új külső táblát hoz létre, amely az Adventure Works Cycles egyes értékesítési képviselőinek évi értékesítési rendeléseinek teljes számát tartalmazza.

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. Több vesszővel tagolt CTE használata utasításban

Az alábbi példa két CTE-t mutat be egyetlen utasításban. A CTE-k nem ágyazhatók be (nincs rekurzió).

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;

Kapcsolódó tartalom

  • Last updated on