Вложенные общие табличные выражения (CTE) в хранилище данных Fabric (Transact-SQL)

Область применения: конечная точка аналитики SQL и хранилище в Microsoft Fabric

Общие выражения таблиц (CTEs) могут упростить сложные запросы, деконструируя обычно сложные запросы в многократно используемые блоки.

Существует четыре типа CTE, включая стандартный, последовательный, рекурсивный и вложенный CTE.

• Стандартная CTE не ссылается или не определяет другой CTE в определении.
• Определение вложенного CTE включает определение другого CTE.
• Последовательное определение CTE может ссылаться на существующий CTE, но не может определить другой CTE.
• Рекурсивная CTE ссылается на себя в своем определении.

Хранилище Fabric и конечная точка аналитики SQL поддерживают стандартные, последовательные и вложенные ТС (предварительная версия). Хотя стандартные и последовательные ТСЗ общедоступны в Microsoft Fabric, вложенные CTEs в настоящее время являются предварительной версией.

Дополнительные сведения о распространенных выражениях таблиц см. в разделе 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 можно использовать только в инструкции SELECT. Его нельзя использовать в инструкциях UPDATE, INSERT или DELETE.
• Инструкции UPDATE, INSERT или DELETE не допускаются в определении вложенного CTE.
• Имена CTE на том же уровне вложения не могут быть дублироваться.
• Вложенный CTE виден только вложенным CTE или последовательным КТС, которые находятся на его непосредственном более высоком уровне.
• Запросы между базами данных допускаются в вложенном определении CTE.
• Указания запросов (т. е. предложение OPTION) не допускаются в определении вложенного CTE.
• Вложенный CTE нельзя использовать в CREATE VIEW.
• AS OF не поддерживается в определении вложенного 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, но не в общем вложенных запросах

Этот запрос завершается ошибкой: 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;

Связанный контент

• WITH common_table_expression (Transact-SQL)
• SELECT (Transact-SQL)
• Область поверхности T-SQL в Microsoft Fabric