Sdílet prostřednictvím

WITH common_table_expression (Transact-SQL)

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)koncový bod SQL Analytics ve službě Microsoft FabricWarehouse v Microsoft Fabricdatabáze SQL v Microsoft Fabric

Určuje dočasnou pojmenovanou sadu výsledků označovanou jako běžný výraz tabulky (CTE). To je odvozeno z jednoduchého dotazu a definované v rámci rozsahu provádění jednoho SELECT, INSERT, UPDATEMERGE, nebo DELETE příkazu. Tuto klauzuli lze také použít v příkazu jako CREATE VIEW součást jeho definující SELECT příkaz. Běžný výraz tabulky může obsahovat odkazy na sebe sama. Označuje se jako rekurzivní společný výraz tabulky.

Transact-SQL konvence syntaxe

Syntaxe

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

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

Argumenty

expression_name

Platný identifikátor pro společný výraz tabulky. expression_name se musí lišit od názvu jakéhokoli jiného společného výrazu tabulky definovaného ve stejné WITH <common_table_expression> klauzuli, ale expression_name může být stejný jako název základní tabulky nebo zobrazení. Jakýkoli odkaz na expression_name v dotazu používá společný výraz tabulky, nikoli základní objekt.

column_name

Určuje název sloupce ve výrazu společné tabulky. Duplicitní názvy v rámci jedné definice CTE nejsou povolené. Zadaný počet názvů sloupců se musí shodovat s počtem sloupců v sadě výsledků CTE_query_definition. Seznam názvů sloupců je volitelný pouze v případě, že jsou v definici dotazu zadány jedinečné názvy všech výsledných sloupců.

CTE_query_definition

Určuje příkaz, SELECT jehož sada výsledků naplní společný výraz tabulky. Příkaz SELECT pro CTE_query_definition musí splňovat stejné požadavky jako při vytváření zobrazení, s výjimkou CTE nemůže definovat další CTE. Další informace naleznete v části Poznámky a CREATE VIEW.

Pokud je definováno více CTE_query_definition , definice dotazu musí být spojené jedním z těchto operátorů sady: UNION ALL, UNION, , EXCEPTnebo INTERSECT.

Pokyny k používání

Pokyny pro nerekurzivní běžné výrazy tabulek

Poznámka:

Následující pokyny platí pro nerekurzivní běžné výrazy tabulek. Pokyny, které platí pro rekurzivní běžné výrazy tabulek, najdete v tématu Pokyny pro rekurzivní běžné výrazy tabulek.

Za CTE musí následovat jeden SELECT, INSERT, , UPDATE, MERGEnebo DELETE příkaz, který odkazuje na některé nebo všechny sloupce CTE. CTE lze také zadat v příkazu jako CREATE VIEW součást definující SELECT příkaz zobrazení.

V nerekurzivním CTE je možné definovat několik definic dotazů CTE. Definice musí být sloučeny jedním z těchto operátorů množiny: UNION ALL, UNION, INTERSECT, nebo EXCEPT.

CTE může odkazovat sám na sebe a dříve definované CTE ve stejné WITH klauzuli. Přeposílání odkazů není povolené.

Zadání více než jedné WITH klauzule v CTE není povoleno. Pokud například CTE_query_definition obsahuje poddotaz, nemůže tento poddotaz obsahovat vnořenou WITH klauzuli, která definuje další CTE.

Další informace o vnořených cích CTE v Microsoft Fabric najdete v tématu Vnořené společné tabulkové výrazy (CTE) v datových skladech prostředků infrastruktury (Transact-SQL).

Výsledky dotazů z běžných výrazů tabulky nejsou materializovány. Každý vnější odkaz na pojmenovanou sadu výsledků vyžaduje opětovné spuštění definovaného dotazu. U dotazů, které vyžadují více odkazů na pojmenovanou sadu výsledků, zvažte místo toho použití dočasného objektu .

Uloženou proceduru nelze spustit ve společném výrazu tabulky.

V CTE_query_definition se nedají použít následující klauzule:

  • ORDER BY (s výjimkou případu, kdy TOPOFFSET/FETCH je zadána klauzule)
  • INTO
  • OPTION klauzule s nápovědou dotazu 1
  • FOR BROWSE

1 Klauzuli OPTION nelze použít uvnitř definice CTE. Lze jej použít pouze v nejkrajnějším SELECT prohlášení.

Pokud se CTE používá v příkazu, který je součástí dávky, musí být příkaz před ním následovat středníkem.

Dotaz odkazující na CTE se dá použít k definování kurzoru.

Tabulky na vzdálených serverech lze odkazovat v CTE.

Při provádění CTE můžou všechny rady odkazující na CTE kolidovat s dalšími radami, které se zjistí, když CTE přistupuje ke svým podkladovým tabulkám stejným způsobem jako nápovědy, které odkazují na zobrazení v dotazech. V takovém případě dotaz vrátí chybu.

Pokyny pro rekurzivní běžné výrazy tabulek

Poznámka:

Následující pokyny platí pro definování rekurzivního společného výrazu tabulky. Pokyny, které platí pro nerekurzivní CES, najdete v tématu Pokyny pro nerekurzivní běžné tabulkové výrazy.

Rekurzivní definice CTE musí obsahovat alespoň dvě definice dotazu CTE, člen ukotvení a rekurzivní člen. Lze definovat více členů ukotvení a rekurzivní členy; Všechny definice dotazu členů ukotvení však musí být vloženy před první rekurzivní definici člena. Všechny definice dotazů CTE jsou členy ukotvení, pokud na samotné CTE odkazují.

Členy ukotvení musí být sloučeny jedním z těchto operátorů množiny: UNION ALL, UNION, INTERSECT, nebo EXCEPT. UNION ALL je jediný operátor sady povolený mezi posledním členem ukotvení a prvním rekurzivním členem a při kombinování více rekurzivních členů.

Počet sloupců v ukotvení a rekurzivních členech musí být stejný.

Datový typ sloupce v rekurzivním členu musí být stejný jako datový typ odpovídajícího sloupce v členu ukotvení.

Klauzule FROM rekurzivního členu musí odkazovat pouze jednou na expression_name CTE.

V CTE_query_definition rekurzivního členu nejsou povoleny následující položky:

  • SELECT DISTINCT
  • GROUP BY
  • PIVOT 1
  • HAVING
  • Skalární agregace
  • TOP
  • LEFT, , RIGHTOUTER JOIN (INNER JOIN je povoleno)
  • Poddotazy
  • Tip použitý na rekurzivní odkaz na CTE uvnitř CTE_query_definition.

1 Pokud je úroveň kompatibility databáze 110 nebo vyšší. Podívejte se na zásadní změny funkcí databázového stroje v SQL Serveru 2016.

Následující pokyny platí pro použití rekurzivního běžného výrazu tabulky:

  • Všechny sloupce vrácené rekurzivníM CTE mají hodnotu null bez ohledu na možnou hodnotu null sloupců vrácených zúčastněnými SELECT příkazy.

  • Nesprávně složený rekurzivní CTE může způsobit nekonečnou smyčku. Pokud například definice rekurzivního dotazu člena vrátí stejné hodnoty pro nadřazené i podřízené sloupce, vytvoří se nekonečná smyčka. Chcete-li zabránit nekonečné smyčce, můžete omezit počet úrovní rekurze povolených pro určitý příkaz pomocí MAXRECURSION nápovědy a hodnoty mezi 032767 a v OPTION klauzuli , INSERT, UPDATEDELETE, nebo SELECT příkazu. To vám umožní řídit provádění příkazu, dokud nevyřešíte problém s kódem, který vytváří smyčku. Výchozí hodnota pro celý server je 100. Při zadání hodnoty 0 se nepoužije žádný limit. Pro každý příkaz lze zadat pouze jednu MAXRECURSION hodnotu. Další informace najdete v tématu nápovědy k dotazům.

  • Zobrazení, které obsahuje rekurzivní společný výraz tabulky, nelze použít k aktualizaci dat.

  • Kurzory je možné definovat u dotazů pomocí objektů CTE. CTE je select_statement argument, který definuje sadu výsledků kurzoru. U rekurzivních objektů CTE jsou povoleny pouze rychlé kurzory jen vpřed a statické (snímky). Pokud je v rekurzivním CTE zadaný jiný typ kurzoru, typ kurzoru se převede na statický.

  • Tabulky na vzdálených serverech lze odkazovat v CTE. Pokud je vzdálený server odkazován v rekurzivním členu CTE, vytvoří se pro každou vzdálenou tabulku fond, aby k tabulkám bylo možné opakovaně přistupovat místně. Pokud se jedná o dotaz CTE, zobrazí se v plánu dotazů indexové zařazování indexů nebo opožděných fondů a další WITH STACK predikát. Toto je jeden ze způsobů, jak potvrdit správnou rekurzi.

  • Analytické a agregační funkce v rekurzivní části CTE se použijí na sadu pro aktuální rekurzní úroveň, nikoli na sadu pro CTE. Funkce jako ROW_NUMBER provozují pouze podmnožinu dat předávaných aktuální rekurzivní úrovní, nikoli celou sadu dat předávaných rekurzivní části CTE. Další informace najdete v příkladu I. Použití analytických funkcí v rekurzivním CTE, které následuje.

Běžné výrazy tabulek ve službě Azure Synapse Analytics a Platform Platform System (PDW)

Aktuální implementace CTE ve službě Azure Synapse Analytics a systému PDW (Platform System) má následující funkce a požadavky:

  • CTE lze zadat v SELECT příkazu.

  • CTE lze zadat v CREATE VIEW příkazu.

  • CTE lze zadat v CREATE TABLE AS SELECT příkazu (CTAS).

  • CTE lze zadat v CREATE REMOTE TABLE AS SELECT příkazu (CRTAS).

  • CTE lze zadat v CREATE EXTERNAL TABLE AS SELECT příkazu (CETAS).

  • Na vzdálenou tabulku lze odkazovat z CTE.

  • Na externí tabulku lze odkazovat z CTE.

  • V CTE je možné definovat několik definic dotazů CTE.

  • Za CTE může následovat SELECTpříkazy , , INSERTUPDATE, DELETE, nebo MERGE příkazy.

  • Běžný výraz tabulky, který obsahuje odkazy na sebe sama (rekurzivní společný výraz tabulky), se nepodporuje.

  • Zadání více než jedné WITH klauzule v CTE není povoleno. Pokud například definice dotazu CTE obsahuje poddotaz, tento poddotaz nemůže obsahovat vnořenou WITH klauzuli, která definuje další CTE.

  • Klauzuli ORDER BY nelze použít v CTE_query_definition, s výjimkou případů, kdy TOP je klauzule zadána.

  • Pokud se CTE používá v příkazu, který je součástí dávky, musí být příkaz před ním následovat středníkem.

  • Při použití vpříkazch sp_prepareSELECT Pokud se však CTE používají jako součást CETAS připravené sp_prepare, chování může odložit z SQL Serveru a dalších příkazů APS PDW kvůli způsobu implementace vazby pro sp_prepare. Pokud SELECT tento odkaz odkazuje na CTE, používá nesprávný sloupec, který v CTE neexistuje, sp_prepare předá se bez zjištění chyby, ale tato chyba se místo toho vyvolá sp_execute .

Příklady

A. Vytvoření společného výrazu tabulky

Následující příklad ukazuje celkový počet prodejních objednávek za rok pro každého obchodního zástupce v 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. Omezení počtu a průměrů sestav pomocí běžného výrazu tabulky

Následující příklad ukazuje průměrný počet prodejních objednávek pro všechny roky pro prodejní zástupce.

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. Použití více definic CTE v jednom dotazu

Následující příklad ukazuje, jak definovat více než jeden CTE v jednom dotazu. Čárka slouží k oddělení definic dotazů CTE. Funkce FORMAT , která se používá k zobrazení peněžních částek ve formátu měny, byla zavedena v SQL Serveru 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;

Tady je částečná sada výsledků.

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. Použití rekurzivního běžného výrazu tabulky k zobrazení více úrovní rekurze

Následující příklad ukazuje hierarchický seznam manažerů a zaměstnanců, kteří je nahlásí. Příklad začíná vytvořením a naplněním dbo.MyEmployees tabulky.

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

Použití rekurzivního společného výrazu tabulky k zobrazení dvou úrovní rekurze

Následující příklad ukazuje manažery a zaměstnance, kteří jim hlásí. Počet vrácených úrovní je omezen na dva.

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;

Zobrazení hierarchického seznamu pomocí rekurzivního výrazu společné tabulky

Následující příklad přidá jména nadřízených a zaměstnanců a jejich odpovídající názvy. Hierarchie manažerů a zaměstnanců je navíc zdůrazněna odsazením jednotlivých úrovní.

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;

Zrušení příkazu pomocí funkce MAXRECURSION

MAXRECURSION lze použít k zabránění špatně vytvořené rekurzivní CTE vstup do nekonečné smyčky. Následující příklad záměrně vytvoří nekonečnou smyčku a používá nápovědu MAXRECURSION k omezení počtu úrovní rekurze na dvě.

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

Po opravě MAXRECURSION chyby kódování se už nevyžaduje. Následující příklad ukazuje opravený kód.

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. Použití běžného výrazu tabulky k selektivnímu procházení rekurzivní relace v příkazu SELECT

Následující příklad ukazuje hierarchii sestavení produktů a součástí, které jsou potřebné k sestavení jízdního kola pro 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. Použití rekurzivního CTE v příkazu UPDATE

Následující příklad aktualizuje PerAssemblyQty hodnotu pro všechny části, které se používají k sestavení produktu 'Road-550-W Yellow, 44' (ProductAssemblyID 800). Výraz společné tabulky vrátí hierarchický seznam částí, které se používají k sestavení ProductAssemblyID 800 , a součásti, které se používají k vytvoření těchto částí atd. Upraví se jenom řádky vrácené běžným výrazem tabulky.

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. Použití více ukotvení a rekurzivních členů

Následující příklad používá více ukotvení a rekurzivní členy k vrácení všech předků zadané osoby. Vytvoří se tabulka a hodnoty se vloží k vytvoření rodinné identity vrácené rekurzivním 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

Já. Použití analytických funkcí v rekurzivním CTE

Následující příklad ukazuje nástrahy, ke kterým může dojít při použití analytické nebo agregační funkce v rekurzivní části 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;

Následující výsledky jsou očekávané výsledky dotazu.

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

Následující výsledky jsou skutečné výsledky dotazu.

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

N vrátí hodnotu 1 pro každý průchod rekurzivní části CTE, protože pouze podmnožina dat pro danou rekurzní úroveň je předána ROWNUMBER. Pro každou iteraci rekurzivní části dotazu se předá ROWNUMBERpouze jeden řádek .

Příklady: Azure Synapse Analytics a Analytický platformový systém (PDW)

J. Použití běžného výrazu tabulky v rámci příkazu CTAS

Následující příklad vytvoří novou tabulku obsahující celkový počet prodejních objednávek za rok pro každého obchodního zástupce v 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. Použití běžného výrazu tabulky v rámci příkazu CETAS

Následující příklad vytvoří novou externí tabulku obsahující celkový počet prodejních objednávek za rok pro každého obchodního zástupce v 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. Použití více čárkami oddělených CTE v příkazu

Následující příklad ukazuje zahrnutí dvou CTE do jednoho příkazu. CtEs nelze vnořit (bez rekurze).

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;

Související obsah