Udostępnij za pomocą

Z common_table_expression (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Punkt końcowy analizy SQL w usłudze Microsoft FabricHurtownia danych w usłudze Microsoft FabricBaza danych SQL w usłudze Microsoft Fabric

Określa tymczasowy nazwany zestaw wyników, znany jako wspólne wyrażenie tabeli (CTE). Jest to pochodna prostego zapytania i zdefiniowana w zakresie wykonywania pojedynczej SELECTinstrukcji , , INSERTUPDATE, MERGElub DELETE . Klauzula CREATE VIEW ta może być również używana w instrukcji w ramach jej instrukcji definiującej SELECT . Wspólne wyrażenie tabeli może zawierać odwołania do samego siebie. Jest to nazywane rekursywnym wspólnym wyrażeniem tabeli.

Aby uzyskać więcej informacji, zobacz Zapytania cykliczne używające typowych wyrażeń tabeli.

Transact-SQL konwencje składni

Syntax

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

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

Arguments

expression_name

Prawidłowy identyfikator wspólnego wyrażenia tabeli. expression_name musi być inna niż nazwa innego wspólnego wyrażenia tabeli zdefiniowanego w tej samej WITH <common_table_expression> klauzuli, ale expression_name może być taka sama jak nazwa tabeli podstawowej lub widoku. Każde odwołanie do expression_name w zapytaniu używa wspólnego wyrażenia tabeli, a nie obiektu podstawowego.

column_name

Określa nazwę kolumny w wspólnym wyrażeniu tabeli. Zduplikowane nazwy w ramach jednej definicji CTE nie są dozwolone. Określona liczba nazw kolumn musi być zgodna z liczbą kolumn w zestawie wyników CTE_query_definition. Lista nazw kolumn jest opcjonalna tylko wtedy, gdy w definicji zapytania podano różne nazwy dla wszystkich wynikowych kolumn.

CTE_query_definition

Określa instrukcję SELECT , której zestaw wyników wypełnia wspólne wyrażenie tabeli. Instrukcja SELECTdla CTE_query_definition musi spełniać te same wymagania co do tworzenia widoku, z wyjątkiem CTE nie może zdefiniować innego CTE. Aby uzyskać więcej informacji, zobacz sekcję Uwagi i CREATE VIEW.

Jeśli zdefiniowano więcej niż jedną CTE_query_definition, definicje zapytań muszą być przyłączone przez jeden z następujących operatorów zestawu: UNION ALL, , UNIONEXCEPTlub INTERSECT.

Wskazówki dotyczące użycia

Wyniki zapytań z typowych wyrażeń tabeli nie są zmaterializowane. Każde zewnętrzne odwołanie do nazwanego zestawu wyników wymaga ponownego wykonania zdefiniowanego zapytania. W przypadku zapytań, które wymagają wielu odwołań do nazwanego zestawu wyników, rozważ użycie obiektu tymczasowego .

Nie można wykonać procedury składowanej w typowym wyrażeniu tabeli.

Aby uzyskać wskazówki dotyczące użycia cyklicznych i nierekursywnych CT, zobacz poniższe sekcje.

Wskazówki dotyczące nierekursywnych typowych wyrażeń tabeli

Note

Poniższe wytyczne dotyczą nierekursywnych typowych wyrażeń tabeli. Aby uzyskać wskazówki dotyczące cyklicznych typowych wyrażeń tabeli, zobacz Wytyczne dotyczące cyklicznych typowych wyrażeń tabel.

Po CTE musi występować pojedyncza SELECTinstrukcja , INSERT, UPDATE, MERGElub DELETE odwołująca się do niektórych lub wszystkich kolumn CTE. CTE można również określić w instrukcji w CREATE VIEW ramach instrukcji definiującej SELECT widok.

Wiele definicji zapytań CTE można zdefiniować w nierekursywnym CTE. Definicje muszą być łączone przez jeden z następujących operatorów zestawu: UNION ALL, , UNIONINTERSECTlub EXCEPT.

CTE może odwoływać się do siebie i wcześniej zdefiniowanych CTE w tej samej WITH klauzuli. Odwołanie do przodu nie jest dozwolone.

Określanie więcej niż jednej WITH klauzuli w CTE nie jest dozwolone. Jeśli na przykład CTE_query_definition zawiera podzapytywanie, podzapytanie nie może zawierać zagnieżdżonej WITH klauzuli definiującej inny obiekt CTE.

Aby uzyskać więcej informacji na temat zagnieżdżonych wartości CTE w usłudze Microsoft Fabric, zobacz Zagnieżdżone wspólne wyrażenie tabeli (CTE) w magazynowaniu danych sieci szkieletowej (Transact-SQL).

W CTE_query_definition nie można używać następujących klauzul:

  • ORDER BY (z wyjątkiem sytuacji, gdy określono klauzulę TOP lub OFFSET/FETCH )
  • INTO
  • OPTION klauzula z wskazówkami zapytania 1
  • FOR BROWSE

1 Klauzula OPTION nie może być używana wewnątrz definicji CTE. Można go używać tylko w najbardziej zewnętrznej SELECT instrukcji.

Gdy CTE jest używany w instrukcji, która jest częścią partii, instrukcja przed musi być zgodna ze średnikiem.

Zapytanie odwołujące się do CTE może służyć do definiowania kursora.

Tabele na serwerach zdalnych można odwoływać się w CTE.

Podczas wykonywania CTE wszelkie wskazówki odwołujące się do CTE mogą powodować konflikt z innymi wskazówkami, które są wykrywane, gdy usługa CTE uzyskuje dostęp do jego tabel bazowych, w taki sam sposób, jak wskazówki, które odwołują się do widoków w zapytaniach. W takim przypadku zapytanie zwraca błąd.

Wskazówki dotyczące cyklicznych typowych wyrażeń tabeli

Note

Poniższe wytyczne dotyczą definiowania cyklicznego wspólnego wyrażenia tabeli. Aby zapoznać się z wytycznymi dotyczącymi nierekursywnych wystąpień CT, zobacz Wytyczne dotyczące nierekursywnych typowych wyrażeń tabeli.

Rekursywna definicja CTE musi zawierać co najmniej dwie definicje zapytań CTE, składową kotwicy i cykliczną składową. Można zdefiniować wiele elementów członkowskich kotwicy i cyklicznych składowych; należy jednak umieścić wszystkie definicje zapytań składowych kotwicy przed pierwszą definicją cyklicznej składowej. Wszystkie definicje zapytań CTE są elementami członkowskimi kotwicy, chyba że odwołują się do samego CTE.

Składowe kotwicy muszą być łączone przez jeden z następujących operatorów zestawu: UNION ALL, , UNIONINTERSECTlub EXCEPT. UNION ALL jest jedynym operatorem zestawu dozwolonym między ostatnim elementem członkowskim zakotwiczenia i pierwszym elementem cyklicznym, a także podczas łączenia wielu cyklicznych elementów członkowskich.

Liczba kolumn w kotwicy i rekursywnych składowych musi być taka sama.

Typ danych kolumny w rekursywnym elemencie członkowskim musi być taki sam jak typ danych odpowiadającej mu kolumny w składowej zakotwiczenia.

Klauzula FROM elementu członkowskiego cyklicznego musi odwoływać się tylko raz do expression_name CTE.

Następujące elementy nie są dozwolone w CTE_query_definition cyklicznego elementu członkowskiego:

  • SELECT DISTINCT
  • GROUP BY
  • PIVOT 1
  • HAVING
  • Agregacja skalarna
  • TOP
  • LEFT, , RIGHTOUTER JOIN (INNER JOIN jest dozwolony)
  • Subqueries
  • Wskazówka zastosowana do rekursywnego odwołania do CTE wewnątrz CTE_query_definition.

1 Gdy poziom zgodności bazy danych wynosi 110 lub wyższy. Zobacz Istotne zmiany w funkcjach aparatu bazy danych w programie SQL Server 2016.

Poniższe wskazówki dotyczą używania cyklicznego wyrażenia wspólnej tabeli:

  • Wszystkie kolumny zwracane przez rekursywną usługę CTE są dopuszczane do wartości null niezależnie od wartości null kolumn zwracanych przez instrukcje uczestniczące SELECT .

  • Niepoprawnie skomponowany rekursywny obiekt CTE może spowodować nieskończoną pętlę. Jeśli na przykład cykliczna definicja zapytania członkowskiego zwraca te same wartości zarówno dla kolumn nadrzędnych, jak i podrzędnych, zostanie utworzona nieskończona pętla. Aby zapobiec nieskończonej pętli, można ograniczyć liczbę poziomów rekursji dozwolonych dla określonej instrukcji przy użyciu MAXRECURSION wskazówki i wartości między 0 i 32767 w OPTION klauzuli INSERTUPDATE, , DELETElub SELECT instrukcji. Dzięki temu można kontrolować wykonywanie instrukcji do momentu rozwiązania problemu z kodem tworzącego pętlę. Wartość domyślna dla całego serwera to 100. Po określeniu wartości 0 nie zostanie zastosowany żaden limit. Można określić tylko jedną MAXRECURSION wartość na instrukcję. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące zapytań.

  • Nie można użyć widoku zawierającego cyklicznego wyrażenia wspólnej tabeli do aktualizowania danych.

  • Kursory można definiować na zapytaniach przy użyciu ctEs. CTE jest argumentem select_statement definiującym zestaw wyników kursora. Tylko szybkie kursory tylko do przodu i statyczne (migawka) są dozwolone w przypadku cyklicznych ctEs. Jeśli w rekursywnym CTE określono inny typ kursora, typ kursora jest konwertowany na statyczny.

  • Tabele na serwerach zdalnych można odwoływać się w CTE. Jeśli serwer zdalny jest przywoływane w rekursywnym elemencie członkowskim CTE, bufor jest tworzony dla każdej tabeli zdalnej, dzięki czemu tabele mogą być wielokrotnie dostępne lokalnie. Jeśli jest to zapytanie CTE, bufor indeksu/z opóźnieniem są wyświetlane w planie zapytania i będą miały dodatkowy WITH STACK predykat. Jest to jeden ze sposobów potwierdzenia prawidłowej rekursji.

  • Funkcje analityczne i agregujące w cyklicznej części CTE są stosowane do zestawu dla bieżącego poziomu rekursji, a nie do zestawu dla CTE. Funkcje takie jak ROW_NUMBER działają tylko w podzestawie danych przekazanych do nich przez bieżący poziom rekursji, a nie cały zestaw danych przekazywanych do rekursywnej części CTE. Aby uzyskać więcej informacji, zobacz przykład I. Użyj funkcji analitycznych w rekursywnym CTE, który następuje poniżej.

Typowe wyrażenia tabel w usłudze Azure Synapse Analytics i Analytics Platform System (PDW)

Bieżąca implementacja obiektów CTE w usłudze Azure Synapse Analytics and Analytics Platform System (PDW) ma następujące funkcje i wymagania:

  • CTE można określić w instrukcji SELECT .

  • CTE można określić w instrukcji CREATE VIEW .

  • CTE można określić w instrukcji CREATE TABLE AS SELECT (CTAS).

  • CTE można określić w instrukcji CREATE REMOTE TABLE AS SELECT (CRTAS).

  • CTE można określić w instrukcji CREATE EXTERNAL TABLE AS SELECT (CETAS).

  • Do tabeli zdalnej można odwoływać się z CTE.

  • Do tabeli zewnętrznej można odwoływać się z CTE.

  • W CTE można zdefiniować wiele definicji zapytań CTE.

  • Po CTE można śledzić SELECTinstrukcje , INSERT, UPDATE, DELETE, lub MERGE .

  • Wspólne wyrażenie tabeli, które zawiera odwołania do siebie (cyklicznego wspólnego wyrażenia tabeli) nie jest obsługiwane.

  • Określanie więcej niż jednej WITH klauzuli w CTE nie jest dozwolone. Jeśli na przykład definicja zapytania CTE zawiera podzapytywanie, podzapytanie nie może zawierać zagnieżdżonej WITH klauzuli definiującej inny obiekt CTE.

  • Klauzula ORDER BY nie może być używana w CTE_query_definition, z wyjątkiem sytuacji, gdy określono klauzulę TOP .

  • Gdy CTE jest używany w instrukcji, która jest częścią partii, instrukcja przed musi być zgodna ze średnikiem.

  • W przypadku użycia w instrukcjach przygotowanych przez sp_prepareelementy CTE zachowują się tak samo jak inne SELECT instrukcje w pliku APS PDW. Jeśli jednak wartości CT są używane w ramach instrukcji CETAS przygotowanych przez sp_prepareprogram , zachowanie może odroczyć od programu SQL Server i innych instrukcji PDW usługi APS ze względu na sposób implementacji powiązania dla sp_prepareprogramu . Jeśli SELECT odwołanie do CTE używa nieprawidłowej kolumny, która nie istnieje w usłudze CTE, sp_prepare przechodzi bez wykrywania błędu, ale błąd jest zgłaszany podczas.sp_execute

Examples

A. Tworzenie wspólnego wyrażenia tabeli

Poniższy przykład przedstawia łączną liczbę zamówień sprzedaży rocznie dla każdego przedstawiciela sprzedaży w 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. Używanie wspólnego wyrażenia tabeli w celu ograniczenia liczby i średnich raportu

W poniższym przykładzie przedstawiono średnią liczbę zamówień sprzedaży dla wszystkich lat przedstawicieli sprzedaży.

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. Używanie wielu definicji CTE w jednym zapytaniu

W poniższym przykładzie pokazano, jak zdefiniować więcej niż jeden obiekt CTE w jednym zapytaniu. Przecinek służy do oddzielania definicji zapytań CTE. Funkcja FORMAT używana do wyświetlania kwot pieniężnych w formacie waluty została wprowadzona w programie 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;

Oto zestaw wyników częściowych.

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. Używanie cyklicznego wyrażenia wspólnej tabeli do wyświetlania wielu poziomów rekursji

W poniższym przykładzie przedstawiono hierarchiczną listę menedżerów i pracowników, którzy je zgłaszają. Przykład rozpoczyna się od utworzenia i wypełniania dbo.MyEmployees tabeli.

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

Używanie cyklicznego wyrażenia wspólnej tabeli do wyświetlania dwóch poziomów rekursji

W poniższym przykładzie pokazano menedżerów i pracowników zgłaszanych do nich. Liczba zwracanych poziomów jest ograniczona do dwóch.

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;

Wyświetlanie listy hierarchicznej przy użyciu cyklicznego wyrażenia wspólnej tabeli

W poniższym przykładzie dodano nazwy kierowników i pracowników oraz ich odpowiednie tytuły. Hierarchia menedżerów i pracowników jest dodatkowo podkreślana przez wcięcie poszczególnych poziomów.

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;

Użyj polecenia MAXRECURSION, aby anulować instrukcję

MAXRECURSION można użyć, aby zapobiec źle sformułowanej rekursywnej pętli CTE wchodzącej w nieskończoną pętlę. Poniższy przykład celowo tworzy pętlę nieskończoną i używa MAXRECURSION wskazówki, aby ograniczyć liczbę poziomów rekursji do dwóch.

--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 skorygowaniu MAXRECURSION błędu kodowania nie jest już wymagany. Poniższy przykład przedstawia poprawiony kod.

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. Używanie wspólnego wyrażenia tabeli do selektywnego przechodzenia przez relację cykliczną w instrukcji SELECT

W poniższym przykładzie pokazano hierarchię zestawów produktów i składników, które są wymagane do utworzenia roweru dla programu 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. Używanie cyklicznego CTE w instrukcji UPDATE

Poniższy przykład aktualizuje PerAssemblyQty wartość wszystkich części używanych do skompilowania produktu 'Road-550-W Yellow, 44' (ProductAssemblyID 800). Wspólne wyrażenie tabeli zwraca hierarchiczną listę części używanych do kompilowania ProductAssemblyID 800 i składników używanych do tworzenia tych części itd. Modyfikowane są tylko wiersze zwracane przez wspólne wyrażenie tabeli.

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. Używanie wielu elementów zakotwiczonych i cyklicznych

W poniższym przykładzie użyto wielu elementów kotwicy i rekursywnych elementów członkowskich, aby zwrócić wszystkie elementy przodków określonej osoby. Zostanie utworzona tabela, a wartości wstawione w celu ustanowienia rodziny rodziny zwracanej przez rekursywne 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. Używanie funkcji analitycznych w rekursywnym CTE

W poniższym przykładzie przedstawiono pułapkę, która może wystąpić podczas korzystania z funkcji analitycznej lub agregującej w cyklicznej części 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;

Poniższe wyniki są oczekiwanym wynikiem zapytania.

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

Poniższe wyniki są rzeczywistymi wynikami zapytania.

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

N Zwraca wartość 1 dla każdego przekazywania rekursywnej części CTE, ponieważ tylko podzbiór danych dla tego poziomu rekursji jest przekazywany do ROWNUMBERelementu . Dla każdej iteracji cyklicznej części zapytania jest przekazywany tylko jeden wiersz do ROWNUMBERelementu .

Przykłady: Azure Synapse Analytics and Analytics Platform System (PDW)

J. Używanie wspólnego wyrażenia tabeli w instrukcji CTAS

W poniższym przykładzie zostanie utworzona nowa tabela zawierająca łączną liczbę zamówień sprzedaży rocznie dla każdego przedstawiciela sprzedaży w firmie 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. Używanie wspólnego wyrażenia tabeli w instrukcji CETAS

W poniższym przykładzie zostanie utworzona nowa tabela zewnętrzna zawierająca łączną liczbę zamówień sprzedaży rocznie dla każdego przedstawiciela sprzedaży w firmie 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. Używanie wielu rozdzielonych przecinkami ctEs w instrukcji

W poniższym przykładzie pokazano, że w jednej instrukcji są uwzględniane dwa ctEs. Nie można zagnieżdżać CTK (bez rekursji).

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;

Treści powiązane

  • Last updated on