Partager via

Expression de table commune imbriquée (CTE) dans l’entreposage de données Fabric (Transact-SQL)

  • Article

S’applique à : point de terminaison d’analytique SQL et Warehouse dans Microsoft Fabric

Les expressions de table courantes (CTEs) peuvent simplifier les requêtes complexes en déconstructant des requêtes généralement complexes en blocs réutilisables.

Il existe quatre types de CTE, notamment standard, séquentiel, récursif et imbriqué.

  • Un CTE standard ne référence pas ou ne définit pas d’autre CTE dans sa définition.
  • La définition d’une CTE imbriquée inclut la définition d’une autre table de données cloud.
  • La définition d’une CTE séquentielle peut référencer un CTE existant, mais ne peut pas définir une autre CTE.
  • Une CTE récursive fait référence à elle-même dans sa définition.

Le point de terminaison Fabric Warehouse et SQL Analytics prennent tous deux en charge les CTEs standard, séquentiels et imbriqués (préversion). Bien que les CTE standard et séquentiels soient généralement disponibles dans Microsoft Fabric, les CTE imbriquées sont actuellement une fonctionnalité en préversion.

Pour plus d’informations sur les expressions de table courantes, consultez WITH common_table_expression (Transact-SQL) .

Remarque

Lors de la préversion, la création d’un CTE imbriqué est prise en charge uniquement par SQL Server Management Studio (SSMS). IntelliSense dans SSMS ne reconnaît pas la syntaxe CTE imbriquée, mais cela ne bloque pas la création d’un CTE imbriqué. Pour une expérience optimale, limitez les niveaux d’imbrication à 64.

Syntaxe

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
    )

Instructions pour la création et l’utilisation d’une instance CTE imbriquée

Outre les instructions relatives à la création et à l’utilisation de CTEs standard, voici des instructions supplémentaires pour les CTE imbriquées :

  • Une CTE imbriquée ne peut être utilisée que dans une instruction SELECT. Il ne peut pas être utilisé dans les instructions UPDATE, INSERT ou DELETE.
  • Aucune instruction UPDATE, INSERT ou DELETE n’est autorisée dans la définition d’un CTE imbriqué.
  • Les noms CTE au même niveau d’imbrication ne peuvent pas être dupliqués.
  • Un CTE imbriqué n’est visible que par le CTE imbriqué ou les CTE séquentiels qui se trouvent à son niveau supérieur immédiat.
  • Les requêtes inter-bases de données sont autorisées dans une définition CTE imbriquée.
  • Les indicateurs de requête (c’est-à-dire la clause OPTION) ne sont pas autorisés dans la définition d’une CTE imbriquée.
  • Impossible d’utiliser L’Imbrication CTE dans CREATE VIEW.
  • AS OF n’est pas pris en charge dans la définition d’un CTE imbriqué.
  • Les CTEs imbriquées sont prises en charge dans une définition de sous-requête CTE, mais pas dans une sous-requête générale.

Exemples

Différences entre les CTEs standard, séquentielles et imbriquées

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

L’étendue du nom de l’objet CTE est limitée à son étendue

Les noms CTE peuvent être réutilisés à différents niveaux d’imbrication. Les noms CTE au même niveau d’imbrication ne peuvent pas être dupliqués. Dans cet exemple, le nom cte1 est utilisé dans l’étendue externe et interne.

;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 imbriquée complexe avec union, union all, intersecter et sauf

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;

L’objet CTE imbriqué est pris en charge dans la définition de sous-requête CTE, mais pas dans la sous-requête générale

Cette requête échoue avec l’erreur suivante : 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;

Les références à un CTE ne peuvent pas dépasser son étendue

Cette requête échoue avec l’erreur suivante : 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;

Contenu connexe