Udostępnij za pośrednictwem

Zagnieżdżone wspólne wyrażenie tabeli (CTE) w magazynowaniu danych sieci szkieletowej (Transact-SQL)

  • Artykuł

Dotyczy: punkt końcowy analizy sqli magazyn w usłudze Microsoft Fabric

Typowe wyrażenia tabel (CTE) mogą uprościć złożone zapytania przez dekonstrukcję zwykle złożonych zapytań do bloków wielokrotnego użytku.

Istnieją cztery typy CTE, w tym standardowe, sekwencyjne, rekursywnei zagnieżdżone CTE.

  • Standardowy CTE nie odwołuje się do innego CTE ani nie definiuje innego CTE w swojej definicji.
  • Definicja zagnieżdżonego języka CTE obejmuje definiowanie innego CTE.
  • Definicja sekwencyjnego CTE może odwoływać się do istniejącego CTE, ale nie może zdefiniować innego CTE.
  • Rekursywna dokumentacja CTE odwołuje się do samej definicji.

Magazyn sieci szkieletowej i punkt końcowy analizy SQL obsługują standardowe , sekwencyjne i zagnieżdżone CTEs. Mimo że standardowe i sekwencyjne wartości CTE są ogólnie dostępne w usłudze Microsoft Fabric, zagnieżdżone wartości CTE są obecnie funkcją w wersji zapoznawczej.

Aby uzyskać więcej informacji na temat typowych wyrażeń tabeli, zobacz WITH common_table_expression (Transact-SQL).

Składnia

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
    )

Wskazówki dotyczące tworzenia i używania zagnieżdżonego CTE

Oprócz wytycznych dotyczących tworzenia i używania standardowych CT, poniżej przedstawiono dodatkowe wytyczne dotyczące zagnieżdżonych CT:

  • Zagnieżdżone CTE można używać tylko w instrukcji SELECT. Nie można jej używać w instrukcjach UPDATE, INSERT ani DELETE.
  • W definicji zagnieżdżonego CTE nie są dozwolone żadne instrukcje UPDATE, INSERT ani DELETE.
  • Nazwy CTE na tym samym poziomie zagnieżdżania nie mogą być zduplikowane.
  • Zagnieżdżony CTE jest widoczny tylko dla zagnieżdżonych CTE lub sekwencyjnych CTE, które znajdują się na bezpośrednim wyższym poziomie.
  • Zapytania obejmujące wiele baz danych są dozwolone w zagnieżdżonej definicji CTE.
  • Wskazówki dotyczące zapytań (tj. klauzula OPTION) nie są dozwolone w definicji zagnieżdżonego języka CTE.
  • Zagnieżdżonych CTE nie można używać w widoku CREATE VIEW.
  • FUNKCJA AS OF nie jest obsługiwana w definicji zagnieżdżonego CTE.
  • Zagnieżdżone wartości CTE są obsługiwane w definicji podzapytania CTE, ale nie w ogólnym podzapytaniu.
  • Aby uzyskać najlepsze środowisko, ogranicz poziomy zagnieżdżania do 64.
  • Zagnieżdżone CTE można utworzyć w edytorze zapytań sieci szkieletowej lub programie SQL Server Management Studio (SSMS). Funkcja IntelliSense programu SSMS nie rozpoznaje zagnieżdżonej składni CTE, ale nie blokuje tworzenia zagnieżdżonego CTE.

Przykłady

Różnice między standardowymi, sekwencyjnymi i zagnieżdżonym ctEs

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

Zakres nazwy CTE jest ograniczony do zakresu

Nazwy CTE można używać ponownie na różnych poziomach zagnieżdżania. Nazwy CTE na tym samym poziomie zagnieżdżania nie mogą być zduplikowane. W tym przykładzie nazwa cte1 jest używana zarówno w zakresie zewnętrznym, jak i wewnętrznym.

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

Złożone zagnieżdżone CTE z union, union all, intersect i z wyjątkiem

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;

Zagnieżdżone CTE jest obsługiwane w definicji podzapytania CTE, ale nie w ogólnym podzapytaniu

To zapytanie kończy się niepowodzeniem z powodu następującego błędu: 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;

Odwołania do CTE nie mogą przekraczać zakresu

To zapytanie kończy się niepowodzeniem z powodu następującego błędu: 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;

Powiązana zawartość