Vnořený společný tabulkový výraz (CTE) v datových skladech prostředků infrastruktury (Transact-SQL)

2025-01-27

platí pro:koncový bod sql Analytics a sklad v Microsoft Fabric

Common Table Expressions (CTE) může zjednodušit složité dotazy dekonstrukcí obvykle složitých dotazů do opakovaně použitelných bloků.

Existují čtyři typy CTE, včetně standardních, sekvenčních, rekurzivnícha vnořených CTE.

Standardní CTE ve své definici neodkazuje ani nedefinuje jiný CTE.
Definice vnořeného CTE zahrnuje definování jiného CTE.
Definice sekvenčního CTE může odkazovat na existující CTE, ale nemůže definovat další CTE.
Rekurzivní CTE odkazuje v jeho definici.

Koncový bod analýzy Fabric Warehouse i SQL podporují standardní, sekvenčnía vnořené cte. I když jsou standardní a sekvenční CTE obecně dostupné v Microsoft Fabric, vnořené CTE jsou v současné době funkcí Preview.

Další informace o běžných výrazech tabulky najdete v tématu WITH common_table_expression (Transact-SQL).

Syntax

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
    )

Pokyny pro vytváření a používání vnořeného CTE

Kromě pokynů pro vytváření a používání standardních ctE jsou zde další pokyny pro vnořené cte:

Vnořený CTE lze použít pouze v příkazu SELECT. Nejde ho použít v příkazech UPDATE, INSERT nebo DELETE.
V definici vnořeného CTE nejsou povoleny žádné příkazy UPDATE, INSERT nebo DELETE.
Názvy CTE na stejné úrovni vnoření nelze duplikovat.
Vnořený CTE je viditelný pouze pro vnořené CTE nebo sekvenční CTE, které jsou na jeho bezprostřední vyšší úrovni.
Dotazy napříč databázemi jsou povoleny v definici vnořeného CTE.
V definici vnořeného CTE nejsou povoleny rady dotazů (tj. klauzule OPTION).
Vnořené CTE nelze použít v zobrazení CREATE.
FUNKCE AS OF není podporována v definici vnořeného CTE.
Vnořené cte jsou podporovány v definici poddotazů CTE, ale ne v obecném poddotazu.
Nejlepších možností dosáhnete, když omezíte úrovně vnoření na 64.
Vnořené CTE je možné vytvořit v Editoru dotazů infrastruktury nebo v sadě SQL Server Management Studio (SSMS). IntelliSense SSMS nerozpozná vnořenou syntaxi CTE, ale neblokuje vytváření vnořených CTE.

Příklady

Rozdíly mezi standardními, sekvenčními a vnořenými 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;

Obor názvu CTE je omezen na jeho obor.

Názvy CTE je možné opakovaně používat na různých úrovních vnoření. Názvy CTE na stejné úrovni vnoření nelze duplikovat. V tomto příkladu se název cte1 používá ve vnějším i vnitřním oboru.

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

Komplexní vnořený CTE se sjednocením, sjednocením všech, protínajícími se a s výjimkou

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;

Vnořené CTE se podporuje v definici poddotazů CTE, ale ne v obecném poddotazu.

Tento dotaz selže s následující chybou: 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;

Odkazy na CTE nemůžou překročit jeho rozsah

Tento dotaz selže s následující chybou: 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)
oblasti T-SQL v Microsoft Fabric

Sdílet prostřednictvím