WITH common_table_expression (Transact-SQL)

文章
10/16/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 的 SQL 端點分析 Microsoft Fabric 的倉儲

指定稱為通用資料表運算式 (CTE) 的暫存具名結果集。這是衍生自簡單的查詢，並定義於單一 SELECT、INSERT、UPDATE、DELETE 或 MERGE 語句的執行範圍內。您也可以在 CREATE VIEW 陳述式中使用這個子句，作為用來定義 SELECT 陳述式的一部分。通用資料表運算式可以包括指向本身的參考。這稱為遞迴通用資料表運算式。

Transact-SQL 語法慣例

語法

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

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

引數

expression_name

通用資料表運算式的有效識別碼。 expression_name 與相同 WITH <common_table_expression> 子句中定義的任何其他通用資料表運算式的名稱不得相同，但 expression_name 可與基底資料表或檢視同名。任何指向 expression_name 的參考都是使用通用資料表運算式，而不是基底物件。

column_name

在一般資料表運算式中，指定資料行名稱。在單一 CTE 定義內，名稱不可重複。指定的資料行名稱數目必須與 CTE_query_definition 的結果集資料行數目相符。只有在查詢定義提供了所有結果資料行的個別名稱時，資料行名稱清單才是選擇性的。

CTE_query_definition

指定其結果集擴展一般資料表運算式的 SELECT 陳述式。除了 CTE 不可定義另一個 CTE 以外，CTE_query_definition 的 SELECT 陳述式必須符合建立檢視的相同需求。如需詳細資訊，請參閱「備註」一節和 CREATE VIEW (Transact-SQL)。

如果定義了多個 CTE_query_definition，就必須由下列設定運算子來聯結查詢定義：UNION ALL、UNION、EXCEPT 或 INTERSECT。

建立和使用通用資料表運算式的指導方針

下列方針適用於非遞迴的通用資料表運算式。如需適用於遞迴通用資料表運算式的方針，請參閱下面的定義和使用遞迴通用資料表運算式的方針。

CTE 之後必須接著參考部分或所有 CTE 資料行的單一 SELECT、INSERT、UPDATE 或 DELETE 陳述式。您也可以在 CREATE VIEW 陳述式中，將 CTE 指定為檢視之定義 SELECT 陳述式的一部分。
您可以在非遞迴的 CTE 中，定義多個 CTE 查詢定義。這些定義必須透過下列其中一個設定運算子來組合：UNION ALL、UNION、INTERSECT 或 EXCEPT。
CTE 可以參考其本身，以及先前在相同 WITH 子句中所定義的 CTE。不允許轉送參考。
不允許在 CTE 中指定多個 WITH 子句。例如，如果 CTE_query_definition 包含子查詢，該子查詢不可包含定義另一個 CTE 的巢狀 WITH 子句。
如需 Microsoft Fabric 中巢狀 CTE 的詳細資訊，請參閱網狀架構數據倉儲中巢狀通用數據表運算式（CTE）。
在 CTE_query_definition 中不可使用下列子句：
- ORDER BY (除非指定了 TOP 子句)
- INTO
- 含有查詢提示的 OPTION 子句
- FOR BROWSE
當批次中的陳述式使用 CTE 時，在 CTE 之前的陳述式，後面必須接著分號。
參考 CTE 的查詢可用來定義資料指標。
在 CTE 中，可以參考遠端伺服器的資料表。
執行 CTE 時，任何參考 CTE 的提示都可能會與其他在 CTE 存取其基礎表時所探索到的提示衝突，與參考查詢中檢視的提示相同。當這種情況發生時，查詢會傳回錯誤：

定義和使用遞迴通用資料表運算式的指導方針

下列方針適用於定義遞迴通用資料表運算式：

遞迴 CTE 定義必須包含至少兩個 CTE 查詢定義，錨點成員和遞迴成員各一個。您可以定義多個錨點成員和遞迴成員；不過，所有錨點成員查詢定義都必須放在第一個遞迴成員定義的前面。除非 CTE 查詢定義參考 CTE 本身，否則，它們都是錨點成員。
錨點成員必須由下列其中一個設定運算子所組合：UNION ALL、UNION、INTERSECT 或 EXCEPT。在最後一個錨點成員和第一個遞迴成員之間，以及在組合多個成員時，UNION ALL 是唯一允許使用的設定運算子。
錨點和遞迴成員中的資料行數目必須相同。
遞迴成員資料行的資料類型必須與錨點成員中對應資料行的資料類型相同。
遞迴成員的 FROM 子句只能參考 CTE expression_name一次。
遞迴成員的 CTE_query_definition 中不允許使用下列項目：
- SELECT DISTINCT
- GROUP BY
- PIVOT (當資料庫相容性層級為 110 或更高時。請參閱 SQL Server 2016 中對於資料庫引擎功能的重大變更。)
- HAVING
- 純量彙總
- TOP
- LEFT、RIGHT、OUTER JOIN (允許使用 INNER JOIN)
- 子查詢
- 適用於對 CTE_query_definition 內 CTE 之遞迴參考的提示。

下列方針適用於使用遞迴通用資料表運算式：

遞迴 CTE 能夠傳回的所有資料行都可為 Null，不論參與的 SELECT 陳述式所傳回之資料行 Null 屬性為何，都是如此。
撰寫不正確的遞歸 CTE 可能會導致無限迴圈。例如，若遞迴成員查詢定義對父資料行與子資料行傳回相同的值，就會建立無限迴圈。若要防止無限迴圈，您可以在 INSERT、UPDATE、DELETE 或 SELECT 陳述式的 OPTION 子句中使用 MAXRECURSION 提示以及 0 與 32,767 之間的值，來限制特定陳述式所能使用的遞迴層級數目。這可讓您控制陳述式的執行，直到產生迴圈的程式碼問題解決為止。伺服器範圍的預設值是 100。當指定 0 時，不會套用任何限制。每個陳述式只能指定一個 MAXRECURSION 值。如需詳細資訊，請參閱查詢提示 (Transact-SQL)。
包含遞迴通用資料表運算式的檢視無法用來更新資料。
您可以使用 CTE 在查詢上定義資料指標。 CTE 是定義資料指標結果集的 select_statement 引數。遞迴 CTE 只能使用僅限向前快轉和靜態 (快照集) 資料指標。如果在遞迴 CTE 中指定了另一種資料指標類型，就會將資料指標類型轉換成靜態。
在 CTE 中，可以參考遠端伺服器的資料表。如果在 CTE 遞迴成員參考遠端伺服器，便會為每個遠端資料表各建立一項多工緩衝處理，以便在本機重複存取資料表。如果是 CTE 查詢，索引多工緩衝處理/延遲多工緩衝處理會顯示在查詢計劃中，且將會有額外的 WITH STACK 述詞。這是確認適當遞迴的一種方式。
CTE 遞迴部分中的分析和彙總函式會套用至目前遞迴層級的集合，而不會套用至 CTE 的集合。 ROW_NUMBER 之類的函式只會針對目前遞迴層級傳遞給它們的資料子集運作，而不會針對傳遞給 CTE 遞迴部分的整個資料集運作。如需詳細資訊，請參閱後續的範例「I. 在遞迴 CTE 中使用分析函式」。

Azure Synapse Analytics and Analytics Platform System 中的常見數據表表達式（PDW）

Azure Synapse Analytics and Analytics Platform System 中目前 CTE 的實作具有下列功能和需求：

可以在 SELECT 陳述式中指定 CTE。
可以在 CREATE VIEW 陳述式中指定 CTE。
可以在 CREATE TABLE AS SELECT (CTAS) 陳述式中指定 CTE。
可以在 CREATE REMOTE TABLE AS SELECT (CRTAS) 陳述式中指定 CTE。
可以在 CREATE EXTERNAL TABLE AS SELECT (CETAS) 陳述式中指定 CTE。
可以從 CTE 參考遠端資料表。
可以從 CTE 參考外部資料表。
可以在 CTE 中定義多個 CTE 查詢定義。
CTE 後面可以接著 SELECT、、INSERTUPDATE、DELETE、或 MERGE 語句。
不支援包含自我參考的通用資料表運算式 (遞迴通用資料表運算式)。
不允許在 CTE 中指定多個 WITH 子句。例如，如果 CTE 查詢定義包含子查詢，該子查詢就不能包含定義另一個 CTE 的巢狀 WITH 子句。
除非已指定 TOP 子句，否則不能在 CTE_query_definition 中使用 ORDER BY 子句。
當批次中的陳述式使用 CTE 時，在 CTE 之前的陳述式，後面必須接著分號。
在備妥的 sp_prepare語句中使用時，CTE 的行為方式會與 SELECT APS PDW 中的其他語句相同。不過，如果使用 CETAS 做為所 sp_prepare準備 CETAS 的一部分，則行為可能會因為針對 sp_prepare實作系結的方式而延遲 SQL Server 和其他 APS PDW 語句。如果參考 CTE 的 SELECT 使用不存在於 CTE 中的錯誤資料行，sp_prepare 將不會偵測錯誤就逕行通過，但會為在 sp_execute 期間擲回錯誤。

範例

A. 建立通用資料表運算式

下列範例顯示 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. 使用通用資料表運算式來限制計數和報告平均值

下列範例顯示業務人員所有年度的平均銷售訂單數目。

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. 在單一查詢中使用多個 CTE 定義

下列範例會示範如何在單一查詢中定義一個以上的 CTE。逗號可用來分隔 CTE 查詢定義。 FORMAT 函式，用來以貨幣格式顯示貨幣金額，適用於 SQL Server 2012 和更新版本。

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
JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID
                    AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear
ORDER BY SalesPersonID, SalesYear;

以下為部分結果集。

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. 使用遞迴通用資料表運算式顯示多層級的遞迴

下列範例會顯示經理及向經理提出報告的員工的階層式清單。此範例會開始建立並擴展 dbo.MyEmployees 資料表。

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

使用遞迴通用資料表運算式顯示兩個層級的遞迴

下列範例會顯示經理及向經理提出報告的員工。傳回的層級數目只限兩個。

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 ;

使用遞迴通用資料表運算式顯示階層式清單

下列範例會新增經理和員工的姓名及其職稱。各個層級會進行縮排，更明顯地強調經理和員工的階層。

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
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;

使用 MAXRECURSION 取消陳述式

您可以利用 MAXRECURSION 來防止形式不良的遞迴 CTE 進入無限迴圈。下列範例會刻意建立無限迴圈，然後利用 MAXRECURSION 提示，將遞迴層級限制為 2。

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

更正編碼錯誤之後，就不再需要 MAXRECURSION。下列範例會顯示更正的程式碼。

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
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;

E. 使用通用資料表運算式，在 SELECT 陳述式中選擇性地逐步執行遞迴關聯性

下列範例會顯示建立 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. 在 UPDATE 陳述式中使用遞迴 CTE

下列範例會為用來建置產品 'Road-550-W Yellow, 44' (ProductAssemblyID``800) 的所有組件更新 PerAssemblyQty 值。通用資料表運算式會傳回一份階層式清單，其中包含用來建立 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
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;

H. 使用多個錨點和遞迴成員

下列範例會利用多個錨點和遞迴成員來傳回指定人員的所有上階。它會建立一份資料表，且會插入值來建立遞迴 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. 在遞迴 CTE 中使用分析函式

下列範例顯示在 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
    JOIN vw AS t ON t.itmID = r.itmIDComp
)

SELECT Lvl, N FROM r;

下列結果是此查詢的預期結果。

下列結果是此查詢的實際結果。

N 會針對 CTE 遞迴部分的每個行程傳回 1，因為只有該遞迴層級的資料子集會傳遞給 ROWNUMBER。對於此查詢遞迴部分的每個反覆運算，只有一個資料列會傳遞給 ROWNUMBER。

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

J. 在 CTAS 陳述式內使用通用資料表運算式

下列範例會建立一個新的資料表，其中包含 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. 在 CETAS 陳述式內使用通用資料表運算式

下列範例會建立一個新的外部資料表，其中包含 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. 在陳述式中使用多個以逗號分隔的 CTE

下列範例示範如何在單一陳述式中包含兩個 CTE。 CTE 不可位於巢狀結構中 (無遞迴)。

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;

分享方式：

WITH common_table_expression (Transact-SQL)

語法

引數

expression_name

column_name

CTE_query_definition

建立和使用通用資料表運算式的指導方針

定義和使用遞迴通用資料表運算式的指導方針

Azure Synapse Analytics and Analytics Platform System 中的常見數據表表達式（PDW）

範例

A. 建立通用資料表運算式

B. 使用通用資料表運算式來限制計數和報告平均值

C. 在單一查詢中使用多個 CTE 定義

D. 使用遞迴通用資料表運算式顯示多層級的遞迴

使用遞迴通用資料表運算式顯示兩個層級的遞迴

使用遞迴通用資料表運算式顯示階層式清單

使用 MAXRECURSION 取消陳述式

E. 使用通用資料表運算式，在 SELECT 陳述式中選擇性地逐步執行遞迴關聯性

F. 在 UPDATE 陳述式中使用遞迴 CTE

H. 使用多個錨點和遞迴成員

I. 在遞迴 CTE 中使用分析函式

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

J. 在 CTAS 陳述式內使用通用資料表運算式

K. 在 CETAS 陳述式內使用通用資料表運算式

L. 在陳述式中使用多個以逗號分隔的 CTE

意見反映

更多資源

分享方式：

WITH common_table_expression (Transact-SQL)

語法

引數

expression_name

column_name

CTE_query_definition

建立和使用通用資料表運算式的指導方針

定義和使用遞迴通用資料表運算式的指導方針

Azure Synapse Analytics and Analytics Platform System 中的常見數據表表達式 （PDW）

範例

A. 建立通用資料表運算式

B. 使用通用資料表運算式來限制計數和報告平均值

C. 在單一查詢中使用多個 CTE 定義

D. 使用遞迴通用資料表運算式顯示多層級的遞迴

使用遞迴通用資料表運算式顯示兩個層級的遞迴

使用遞迴通用資料表運算式顯示階層式清單

使用 MAXRECURSION 取消陳述式

E. 使用通用資料表運算式，在 SELECT 陳述式中選擇性地逐步執行遞迴關聯性

F. 在 UPDATE 陳述式中使用遞迴 CTE

H. 使用多個錨點和遞迴成員

I. 在遞迴 CTE 中使用分析函式

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

J. 在 CTAS 陳述式內使用通用資料表運算式

K. 在 CETAS 陳述式內使用通用資料表運算式

L. 在陳述式中使用多個以逗號分隔的 CTE

相關內容

意見反映

更多資源

Azure Synapse Analytics and Analytics Platform System 中的常見數據表表達式（PDW）