Delen via

Geneste Common Table Expression (CTE) in Fabric datawarehousing (Transact-SQL)

Van toepassing op:SQL Analytics-eindpunt en -magazijn in Microsoft Fabric

Common Table Expressions (CTE's) kunnen complexe query's vereenvoudigen door normaal gesproken complexe query's in herbruikbare blokken te deconstrueren.

Er zijn vier typen CTE, waaronder standaard, sequentiële, recursieveen genest CTE.

  • Een standaard-CTE verwijst niet naar of definieert geen andere CTE in de definitie.
  • Een geneste CTE-definitie omvat het definiëren van een andere CTE.
  • De definitie van een sequentiële CTE kan verwijzen naar een bestaande CTE, maar kan geen andere CTE definiëren.
  • Een recursieve CTE verwijst naar zichzelf in de definitie.

In Microsoft Fabric ondersteunen Fabric Data Warehouse en het SQL-analyse-eindpunt zowel standaard, sequentiële als genesteCTE's, maar niet recursieve CTE's.

Zie WITH common_table_expression (Transact-SQL)voor meer informatie over algemene tabelexpressies.

Syntaxis

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
    )

Richtlijnen voor het maken en gebruiken van een geneste CTE

Naast richtlijnen voor het maken en gebruiken van standaard-CTE's zijn hier extra richtlijnen voor geneste CTE's:

  • Een geneste CTE kan alleen worden gebruikt in een SELECT-instructie. Deze kan niet worden gebruikt in de instructies UPDATE, INSERT of DELETE.
  • Er zijn geen UPDATE-, INSERT- of DELETE-instructies toegestaan in de definitie van een geneste CTE.
  • CTE-namen op hetzelfde nestniveau kunnen niet worden gedupliceerd.
  • Een geneste CTE is alleen zichtbaar voor de geneste CTE of sequentiële CTE's die zich op een hoger niveau bevinden.
  • Query's tussen databases zijn toegestaan in een geneste CTE-definitie.
  • Queryhints (bijvoorbeeld OPTION-component) zijn niet toegestaan in de definitie van een geneste CTE.
  • Geneste CTE kan niet worden gebruikt in CREATE VIEW.
  • AS OF wordt niet ondersteund in de definitie van een geneste CTE.
  • Geneste CTE-subquery's worden ondersteund in een CTE-subquerydefinitie, maar niet in een algemene subquery.
  • Beperk nestniveaus tot 64 voor de beste ervaring.
  • Geneste CTE kan worden gemaakt in Fabric Query Editor of SQL Server Management Studio (SSMS). SSMS intellisense herkent geen geneste CTE-syntaxis, maar hiermee wordt het maken van geneste CTE niet geblokkeerd.

Voorbeelden

Verschillen tussen standaard-, sequentiële en geneste CTE's

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

Naambereik van CTE is beperkt tot het bereik ervan

CTE-namen kunnen opnieuw worden gebruikt op verschillende nestniveaus. CTE-namen op hetzelfde nestniveau kunnen niet worden gedupliceerd. In dit voorbeeld wordt de naam cte1 zowel buiten als binnen het bereik gebruikt.

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

Complexe geneste CTE met union, union all, intersect, and except

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;

Geneste CTE wordt ondersteund in de definitie van CTE-subquery, maar niet in algemene subquery

Deze query mislukt met de volgende fout: 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;

Verwijzingen naar een CTE kunnen het bereik ervan niet overschrijden

Deze query mislukt met de volgende fout: 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;

Verwante inhoud