共用方式為

網狀架構數據倉儲中的巢狀通用數據表表示式 （CTE） （Transact-SQL）

  • 發行項

適用於：Microsoft Fabric 中的 SQL 分析端點和倉儲

一般數據表表示式 （CTE） 可以藉由將一般複雜的查詢解構為可重複使用的區塊，來簡化複雜的查詢。

CTE 有四種類型，包括 標準、 循序、 遞歸和 巢狀 CTE。

  • 標準 CTE 不會在其定義中參考或定義另一個 CTE。
  • 巢狀 CTE 的定義包括定義另一個 CTE。
  • 循序 CTE 的定義可以參考現有的 CTE，但無法定義另一個 CTE。
  • 遞歸 CTE 會在其定義中參考本身。

網狀架構倉儲和 SQL 分析端點都支援 標準、循序和 巢狀 CTE。 雖然標準 CTE 和循序 CTE 已在 Microsoft Fabric 中正式推出，但巢狀 CTE 目前為預覽功能。

如需通用資料表表達式的詳細資訊，請參閱 WITH common_table_expression （Transact-SQL） 。

語法

WITH <NESTED_CTE_NAME_LEVEL1> [ (column_name , ...) ] AS
    (WITH <NESTED_CTE_NAME_LEVEL2> [ (column_name , ...) ] AS
        (
            ...
                WITH <NESTED_CTE_NAME_LEVELn-1> [ ( column_name , ...) ] AS
                (
                    WITH <NESTED_CTE_NAME_LEVELn> [ ( column_name , ...) ] AS
                    (
                        Standard_CTE_query_definition
                    )
                    <SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVELn
                )
                <SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVELn-1
            ...
        )
    <SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVEL2
    )

建立和使用巢狀 CTE 的指導方針

除了建立和使用標準 CTE 的指導方針之外，以下是巢狀 CTE 的額外指導方針：

  • 巢狀 CTE 只能在 SELECT 語句中使用。 它不能用於UPDATE、INSERT或DELETE語句。
  • 巢狀 CTE 的定義中不允許 UPDATE、INSERT 或 DELETE 語句。
  • 無法複製位於相同巢狀層級的 CTE 名稱。
  • 巢狀 CTE 只能看見位於其直接較高層級的巢狀 CTE 或循序 CTE。
  • 巢狀 CTE 定義中允許跨資料庫查詢。
  • 巢狀 CTE 定義中不允許查詢提示（亦即 OPTION 子句）。
  • 巢狀 CTE 無法在 CREATE VIEW 中使用。
  • 巢狀 CTE 的定義不支援 AS OF。
  • CTE 子查詢定義中支援巢狀 CTE CTE，但在一般子查詢中則不支援。
  • 為了獲得最佳體驗，請將巢狀層級限制為64。
  • 巢狀 CTE 可以在網狀架構查詢編輯器或 SQL Server Management Studio （SSMS） 中建立。 SSMS Intellisense 無法辨識巢狀 CTE 語法，但這不會封鎖建立巢狀 CTE。

範例

標準、循序和巢狀 CTE 之間的差異

-- Standard CTE
;WITH Standard_CTE AS (
    SELECT * FROM T1
)
SELECT * FROM Standard_CTE;

-- Sequential CTE
;WITH CTE1 AS (
    SELECT * FROM T1
),
CTE2 AS (SELECT * FROM CTE1),
CTE3 AS (SELECT * FROM CTE2)
SELECT * FROM CTE3

-- Nested CTE
;WITH OUTER_CTE AS (
    WITH INNER_CTE AS (
        SELECT * FROM T1
    )
    SELECT * FROM INNER_CTE
)
SELECT * FROM OUTER_CTE;

CTE 的名稱範圍僅限於其範圍

CTE 名稱可以在不同的巢狀層級重複使用。 無法複製位於相同巢狀層級的 CTE 名稱。 在此範例中，名稱 cte1 會同時用於外部和內部範圍。

;WITH
    cte1 AS (
        WITH
            inner_cte1_1 AS (
                SELECT * FROM NestedCTE_t1 WHERE c1 = 1
            ),
            inner_cte1_2 AS (
                SELECT * FROM inner_cte1_1 WHERE c2 = 1
            )
        SELECT * FROM inner_cte1_2
    ),
    cte2 AS (
        WITH
            cte1 AS (
                SELECT * FROM NestedCTE_t1 WHERE c3 = 1
            ),
            inner_cte2_2 AS (
                SELECT * FROM cte1 WHERE c4 = 1
            )
        SELECT * FROM inner_cte2_2
    )

具有聯集、聯集全部、交集和例外的複雜巢狀 CTE

CREATE TABLE NestedCTE_t1 (
    c1 INT,
    c2 INT,
    c3 INT
);
GO

INSERT INTO NestedCTE_t1
VALUES (1, 1, 1);

INSERT INTO NestedCTE_t1
VALUES (2, 2, 2);

INSERT INTO NestedCTE_t1
VALUES (3, 3, 3);
GO

WITH
    outermost_cte_1 AS (
        WITH
            outer_cte_1 AS (
                WITH
                    inner_cte1_1 AS (
                        SELECT * FROM NestedCTE_t1 WHERE c1 = 1
                    ),
                    inner_cte1_2 AS (
                        SELECT * FROM inner_cte1_1
                        UNION SELECT * FROM inner_cte1_1
                    )
                SELECT * FROM inner_cte1_1
                UNION ALL SELECT * FROM inner_cte1_2
            ),
            outer_cte_2 AS (
                WITH
                    inner_cte2_1 AS (
                        SELECT * FROM NestedCTE_t1 WHERE c2 = 1
                        EXCEPT SELECT * FROM outer_cte_1
                    ),
                    inner_cte2_2 AS (
                        SELECT * FROM NestedCTE_t1 WHERE c3 = 1
                        UNION SELECT * FROM inner_cte2_1
                    )
                SELECT * FROM inner_cte2_1
                UNION ALL SELECT * FROM outer_cte_1
            )
        SELECT * FROM outer_cte_1
        INTERSECT SELECT * FROM outer_cte_2
    ),
    outermost_cte_2 AS (
        SELECT * FROM outermost_cte_1
        UNION SELECT * FROM outermost_cte_1
    )
SELECT * FROM outermost_cte_1
UNION ALL SELECT * FROM outermost_cte_2;

CTE 子查詢定義支援巢狀 CTE，但在一般子查詢中則不支援巢狀 CTE

此查詢失敗，並出現下列錯誤： Msg 156, Level 15, State 1, Line 3. Incorrect syntax near the keyword 'WITH'.

SELECT * FROM
(
    WITH
        inner_cte1_1 AS (SELECT * FROM NestedCTE_t1 WHERE c1 = 1),
        inner_cte1_2 AS  (SELECT * FROM inner_cte1_1)
    SELECT * FROM inner_cte1_2
) AS subq1;

CTE 的參考不能超過其範圍

此查詢失敗，並出現下列錯誤： Msg 208, Level 16, State 1, Line 1. Invalid object name 'inner_cte1_1'.

;WITH
    outer_cte_1 AS (
        WITH
            inner_cte1_1 AS (
                SELECT * FROM NestedCTE_t1 WHERE c1 = 1
            ),
            inner_cte1_2 AS (
                SELECT * FROM inner_cte1_1 WHERE c2 = 1
            )
        SELECT * FROM inner_cte1_2
    ),
    outer_cte_2 AS (
        WITH inner_cte2_1 AS (
            SELECT * FROM NestedCTE_t1 WHERE c3 = 1
        )
        SELECT
            tmp2.*
        FROM
            inner_cte1_1 AS tmp1,
            inner_cte2_1 AS tmp2
        WHERE
            tmp1.c4 = tmp2.c4
    )
SELECT * FROM outer_cte_2;

相關內容