WITH common_table_expression (Transact-SQL)

2025-06-25

S’applique à :SQL Server Base de données Azure SQL Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Point de terminaison d'analyse SQL dans Microsoft Fabric Entrepôt dans Microsoft Fabric Base de données SQL dans Microsoft Fabric

Spécifie un jeu de résultats nommé temporaire, désigné par le terme d'expression de table commune (CTE, Common Table Expression). Il s’agit d’une requête simple et définie dans l’étendue d’exécution d’une instruction unique SELECT, , INSERTUPDATEou MERGEDELETE d’une instruction. Cette clause peut également être utilisée dans une CREATE VIEW instruction dans le cadre de son instruction de définition SELECT . Une expression de table commune peut inclure des références à elle-même. Dans ce cas, elle est désignée en tant qu'expression de table commune récursive.

Conventions de la syntaxe Transact-SQL

Syntaxe

[ WITH <common_table_expression> [ , ...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ , ...n ] ) ]
    AS
    ( CTE_query_definition )

Les arguments

expression_name

Identificateur valide pour l’expression de table commune. expression_name doit être différent du nom de toutes les autres expressions de table communes définies dans la même clause WITH <common_table_expression>, mais expression_name ne peut pas être identique au nom de la table ou de l’affichage de base. Toute référence à expression_name dans la requête utilise l’expression de table commune à la place de l’objet de base.

column_name

Spécifie un nom de colonne dans l'expression de table commune. Les noms dupliqués ne sont pas autorisés au sein d'une définition d'expression de table commune unique (CTE). Le nombre de noms de colonnes spécifiés doit correspondre au nombre de colonnes dans le jeu de résultats de CTE_query_definition. La liste des noms de colonnes n'est facultative que si des noms distincts pour toutes les colonnes résultants sont fournis dans la définition de la requête.

CTE_query_definition

Spécifie une SELECT instruction dont le jeu de résultats remplit l’expression de table commune. L’instruction SELECT de CTE_query_definition doit répondre aux mêmes exigences que pour la création d’une vue, sauf qu’une CTE ne peut pas définir une autre CTE. Pour plus d’informations, consultez la section Notes et CREATE VIEW.

Si plusieurs CTE_query_definition sont définies, les définitions de requête doivent être jointes par l’un des opérateurs de jeu suivants : UNION ALL, , UNIONEXCEPTou INTERSECT.

Instructions d’utilisation

Les résultats des requêtes provenant d’expressions de table courantes ne sont pas matérialisés. Chaque référence externe au jeu de résultats nommé nécessite que la requête définie soit réexécutée. Pour les requêtes qui nécessitent plusieurs références au jeu de résultats nommé, envisagez d’utiliser un objet temporaire à la place.

Vous ne pouvez pas exécuter de procédure stockée dans une expression de table commune.

Pour obtenir des instructions d’utilisation sur les CTE récursives et non récursives, consultez les sections suivantes.

Instructions pour les expressions de table courantes nonrecursives
Instructions pour les expressions de table courantes récursives

Instructions pour les expressions de table courantes nonrecursives

Remarque

Les principes suivants s'appliquent à des expressions de table communes non récursives. Pour obtenir des instructions qui s’appliquent aux expressions de table courantes récursives, consultez Instructions pour les expressions de table courantes récursives.

Un CTE doit être suivi d’une instruction unique SELECT, , INSERTUPDATE, MERGEou DELETE d’instruction qui référence certaines ou toutes les colonnes CTE. Une expression de table commune peut également être spécifiée dans une instruction CREATE VIEW comme faisant partie de l’instruction SELECT de définition de la vue.

Des définitions de requête d'expression de table commune multiples peuvent être définies dans une expression de table commune non récursive. Les définitions doivent être associées par l’un des opérateurs de jeu ci-après : UNION ALL, UNION, INTERSECT ou EXCEPT.

Une expression de table commune peut faire référence à elle-même ou à des expressions de table communes définies précédemment dans la même clause WITH. Les références à des éléments ultérieurs ne sont pas autorisées.

La spécification de plusieurs clauses WITH dans une expression de table commune n’est pas autorisée. Par exemple, si un CTE_query_definition contient une sous-requête, cette sous-requête ne peut pas contenir de clause imbriquée WITH qui définit un autre CTE.

Pour plus d’informations sur les CTEs imbriquées dans Microsoft Fabric, consultez L’expression de table commune imbriquée (CTE) dans l’entreposage de données Fabric (Transact-SQL).

Les clauses suivantes ne peuvent pas être utilisées dans l’argument CTE_query_definition :

ORDER BY(sauf lorsqu’une ou TOP une OFFSET/FETCH clause est spécifiée)
INTO
OPTION clause avec indicateurs de requête ¹
FOR BROWSE

¹ La OPTION clause ne peut pas être utilisée à l’intérieur d’une définition CTE. Elle ne peut être utilisée que dans l’instruction la plus SELECT externe.

Lorsqu'une expression de table commune est utilisée dans une instruction faisant partie d'un traitement, l'instruction qui la précède doit être suivie d'un point-virgule.

Une requête faisant référence à une expression de table commune peut être utilisée pour définir le curseur.

Les tables sur des serveurs distants peuvent être référencées dans l’expression de table commune.

Lors de l’exécution d’un CTE, tous les indicateurs qui référencent un CTE peuvent entrer en conflit avec d’autres indicateurs découverts lorsque l’objet CTE accède à ses tables sous-jacentes, de la même manière que les indicateurs qui référencent des vues dans les requêtes. Lorsque cela se produit, la requête retourne une erreur.

Instructions pour les expressions de table courantes récursives

Remarque

Les instructions suivantes s’appliquent à la définition d’une expression de table commune récursive. Pour obtenir des instructions qui s’appliquent aux expressions de table courantes non récursives, consultez Recommandations pour les expressions de table courantes nonrecursives.

La définition de l'expression de table commune récursive doit contenir au moins deux définitions de requête d'expression de table commune, un membre d'ancrage et un membre récursif. Plusieurs membres d'ancrage et membres récursifs peuvent être définis ; toutefois, toutes les définitions de requêtes de membres d'ancrage doivent être placées avant la première définition de membre récursif. Toutes les définitions de requête d'expression de table commune sont des membres d'ancrage à moins qu'ils ne fassent référence à l'expression de table commune elle-même.

Les membres d’ancre doivent être combinés par l’un des opérateurs de jeu suivants : UNION ALL, , UNION, INTERSECTou EXCEPT. UNION ALL est le seul opérateur défini autorisé entre le dernier membre d’ancrage et le premier membre récursif, et lors de la combinaison de plusieurs membres récursifs.

Le nombre de colonnes des membres d'ancrage et récursifs doivent être identiques.

Le type de données d'une colonne du membre récursif doit également être identique au type de données de la colonne correspondante du membre d'ancrage.

La clause FROM d’un membre récursif doit référencer une seule fois l’argument expression_name de l’expression de table commune.

Les éléments suivants ne sont pas autorisés dans l’argument CTE_query_definition d’un membre récursif :

SELECT DISTINCT
GROUP BY
PIVOT ¹
HAVING
Agrégation scalaire
TOP
LEFT, RIGHT, OUTER JOIN (INNER JOIN est autorisé)
Sous-requêtes
Indicateur appliqué à une référence récursive à une expression de table commune à l’intérieur d’un argument CTE_query_definition.

¹ Lorsque le niveau de compatibilité de la base de données est de 110 ou supérieur. Consultez changements cassants apportés aux fonctionnalités du moteur de base de données dans SQL Server 2016.

Les principes suivants s'appliquent à l'utilisation d'une expression de table commune récursive :

Toutes les colonnes retournées par une expression de table commune récursive peuvent prendre la valeur NULL que les colonnes retournées par les instructions SELECT participantes puissent prendre la valeur NULL ou non.
Un CTE récursif mal composé peut entraîner une boucle infinie. Par exemple, si la définition de requête du membre récursif renvoie les mêmes valeurs pour les colonnes parent et enfant, une boucle infinie est créée. Pour empêcher une boucle infinie, vous pouvez limiter le nombre de niveaux de récursivité autorisés pour une instruction particulière à l’aide de l’indicateur MAXRECURSION et d’une valeur entre 0 et 32767 dans la OPTION clause de l’instruction , INSERT, UPDATEou DELETE de l’instructionSELECT. Cela vous permet de contrôler l'exécution de l'instruction jusqu'à ce que vous résolviez le problème de code qui crée la boucle. La valeur par défaut à l'échelle du serveur est 100. Lorsque 0 est spécifié, aucune limite n'est appliquée. Une seule valeur MAXRECURSION peut être spécifiée par instruction. Pour plus d’informations, consultez indicateurs de requête.
Un affichage contenant une expression de table commune récursive ne peut pas être utilisée pour mettre à jour des données.
Les curseurs peuvent être définis sur les requêtes à l’aide de CTEs. L’expression de table commune est l’argument select_statement qui définit le jeu de résultats du curseur. Seuls les curseurs d'avance rapide uniquement et statiques (instantané) sont autorisés pour les expressions de table communes récursives. Si un autre type de curseur est spécifié dans une expression de table commune récursive, ce type est converti en statique.
Les tables sur des serveurs distants peuvent être référencées dans l’expression de table commune. Si le serveur distant est référencé dans le membre récursif de l'expression de table commune récursive, un spouleur est créé pour chaque table distante afin que les tables soient accessibles localement de manière répétée. S’il s’agit d’une requête CTE, index Spool/Lazy Spools s’affichent dans le plan de requête et ont le prédicat supplémentaire WITH STACK . Il s'agit de l'une des méthodes permettant de confirmer la récursivité appropriée.
Les fonctions analytiques et d'agrégation dans la partie récursive de l'expression CTE sont appliquées à l'ensemble du niveau de récursivité actuel et non à l'ensemble de l'expression CTE. Les fonctions telles que ROW_NUMBER s’appliquent uniquement au sous-ensemble de données qui leur est transmis par le niveau de récursivité actuel, et non à l’ensemble entier de données transmis à la partie récursive de l’expression CTE. Pour plus d’informations, consultez l’exemple I. Utiliser des fonctions analytiques dans une expression de table commune récursive, comme suit.

Expressions de table courantes dans Azure Synapse Analytics and Analytics Platform System (PDW)

L’implémentation actuelle des CTEs dans Azure Synapse Analytics et Analytics Platform System (PDW) présente les fonctionnalités et exigences suivantes :

Une expression de table commune peut être spécifiée dans une instruction SELECT.
Une expression de table commune peut être spécifiée dans une instruction CREATE VIEW.
Une expression de table commune peut être spécifiée dans une instruction CREATE TABLE AS SELECT (CTAS).
Une expression de table commune peut être spécifiée dans une instruction CREATE REMOTE TABLE AS SELECT (CRTAS).
Une expression de table commune peut être spécifiée dans une instruction CREATE EXTERNAL TABLE AS SELECT (CETAS).
Une table distante peut être référencée à partir d’une expression de table commune.
Une table externe peut être référencée à partir d’une expression de table commune.
Plusieurs définitions de requête d’expression de table commune peuvent être définies dans une même expression de table commune.
Un CTE peut être suivi d’instructions SELECT, , INSERTUPDATE, DELETEou MERGE d’instructions.
Une expression de table commune qui contient des références à elle-même (expression de table commune récursive) n’est pas prise en charge.
La spécification de plusieurs clauses WITH dans une expression de table commune n’est pas autorisée. Par exemple, si une définition de requête d’expression de table commune contient une sous-requête, cette dernière ne peut pas contenir de clause WITH imbriquée qui définit une autre expression de table commune.
Une ORDER BY clause ne peut pas être utilisée dans le CTE_query_definition, sauf lorsqu’une TOP clause est spécifiée.
Lorsqu'une expression de table commune est utilisée dans une instruction faisant partie d'un traitement, l'instruction qui la précède doit être suivie d'un point-virgule.
Lorsqu’elles sont utilisées dans les instructions préparées par sp_prepare, les CTEs se comportent de la même façon que d’autres SELECT instructions dans APS PDW. Toutefois, si les CTEs sont utilisées dans le cadre de CETAS préparée par sp_prepare, le comportement peut différer de SQL Server et d’autres instructions PDW APS en raison de la façon dont la liaison est implémentée pour sp_prepare. Si SELECT cela fait référence à l’utilisation d’une colonne incorrecte qui n’existe pas dans CTE, le sp_prepare passage sans détecter l’erreur, mais l’erreur est levée au sp_execute lieu de cela.

Exemples

R. Créer une expression de table commune

L'exemple suivant affiche le nombre total de commandes client par année pour chaque commercial chez Adventure Works Cycles.

-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID,
       COUNT(SalesOrderID) AS TotalSales,
       SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;

B. Utiliser une expression de table commune pour limiter les nombres et les moyennes de rapports

L'exemple suivant affiche le nombre moyen de commandes client de toutes les années pour les commerciaux.

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
    SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;

Chapitre C. Utiliser plusieurs définitions d'expression de table commune dans une seule requête

L'exemple ci-dessous montre comment définir plus d'une expression de table commune dans une seule requête. Une virgule est utilisée pour séparer les définitions de requête CTE. La FORMAT fonction, utilisée pour afficher les montants monétaires dans un format monétaire, a été introduite dans SQL Server 2012 (11.x).

WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)
AS
-- Define the first CTE query.
(
    SELECT SalesPersonID,
           SUM(TotalDue) AS TotalSales,
           YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID, YEAR(OrderDate)
), -- Use a comma to separate multiple CTE definitions.

-- Define the second CTE query, which returns sales quota data by year for each sales person.
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)
AS
(
    SELECT BusinessEntityID,
           SUM(SalesQuota) AS SalesQuota,
           YEAR(QuotaDate) AS SalesQuotaYear
    FROM Sales.SalesPersonQuotaHistory
    GROUP BY BusinessEntityID, YEAR(QuotaDate)
)
-- Define the outer query by referencing columns from both CTEs.
SELECT SalesPersonID,
       SalesYear,
       FORMAT(TotalSales, 'C', 'en-us') AS TotalSales,
       SalesQuotaYear,
       FORMAT(SalesQuota, 'C', 'en-us') AS SalesQuota,
       FORMAT(TotalSales - SalesQuota, 'C', 'en-us') AS Amt_Above_or_Below_Quota
FROM Sales_CTE
     INNER JOIN Sales_Quota_CTE
         ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID
        AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear
ORDER BY SalesPersonID, SalesYear;

Voici un jeu de résultats partiel.

SalesPersonID SalesYear   TotalSales    SalesQuotaYear SalesQuota  Amt_Above_or_Below_Quota
------------- ---------   -----------   -------------- ---------- ----------------------------------
274           2005        $32,567.92    2005           $35,000.00  ($2,432.08)
274           2006        $406,620.07   2006           $455,000.00 ($48,379.93)
274           2007        $515,622.91   2007           $544,000.00 ($28,377.09)
274           2008        $281,123.55   2008           $271,000.00  $10,123.55

D. Utiliser une expression de table commune récursive pour afficher plusieurs niveaux de récursivité

L'exemple suivant affiche la liste hiérarchique des responsables et des employés sous leurs ordres. L'exemple commence en créant et en remplissant la table dbo.MyEmployees.

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
    EmployeeID SMALLINT NOT NULL,
    FirstName NVARCHAR (30) NOT NULL,
    LastName NVARCHAR (40) NOT NULL,
    Title NVARCHAR (50) NOT NULL,
    DeptID SMALLINT NOT NULL,
    ManagerID SMALLINT NULL,
    CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC),
    CONSTRAINT FK_MyEmployees_ManagerID_EmployeeID FOREIGN KEY (ManagerID) REFERENCES dbo.MyEmployees (EmployeeID)
);

-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL),
(273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1),
(274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273),
(275, N'Michael', N'Blythe', N'Sales Representative', 3, 274),
(276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274),
(285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273),
(286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285),
(16, N'David', N'Bradley', N'Marketing Manager', 4, 273),
(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

WITH DirectReports (ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
ORDER BY ManagerID;

Utiliser une expression de table commune récursive pour afficher deux niveaux de récursivité

L'exemple suivant affiche les responsables et les employés sous leurs ordres. Le nombre de niveaux retournés est limité à deux.

WITH DirectReports (ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
WHERE EmployeeLevel <= 2;

Utiliser une expression de table commune récursive pour afficher une liste hiérarchique

L’exemple suivant ajoute les noms du responsable et des employés, ainsi que leurs titres respectifs. La hiérarchie des responsables et des employés est mise en évidence par l'indentation de chaque niveau.

WITH DirectReports (Name, Title, EmployeeID, EmployeeLevel, Sort) AS
(
    SELECT CONVERT (VARCHAR (255), e.FirstName + ' ' + e.LastName),
           e.Title,
           e.EmployeeID,
           1,
           CONVERT (VARCHAR (255), e.FirstName + ' ' + e.LastName)
    FROM dbo.MyEmployees AS e
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT (VARCHAR (255), REPLICATE('|    ', EmployeeLevel) + e.FirstName + ' ' + e.LastName),
           e.Title,
           e.EmployeeID,
           EmployeeLevel + 1,
           CONVERT (VARCHAR (255), RTRIM(Sort) + '|    ' + FirstName + ' ' + LastName)
    FROM dbo.MyEmployees AS e
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID
)
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;

Utiliser MAXRECURSION pour annuler une instruction

MAXRECURSION peut être utilisé pour empêcher une expression de table commune récursive mal rédigée d'entrer dans une boucle infinie. L'exemple suivant créée intentionnellement une boucle infinie et utilise l'indicateur MAXRECURSION pour limiter le nombre de niveaux de récursivité à deux.

--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.MyEmployees
    WHERE ManagerID IS NOT NULL
    UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte
         INNER JOIN dbo.MyEmployees AS e
             ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);

Une fois l’erreur de codage corrigée, MAXRECURSION n’est plus nécessaire. L'exemple suivant montre le code corrigé.

WITH cte (EmployeeID, ManagerID, Title) AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.MyEmployees
    WHERE ManagerID IS NOT NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Title
    FROM dbo.MyEmployees AS e
         INNER JOIN cte
             ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;

E. Utiliser une expression de table commune pour exécuter pas à pas de façon sélective une relation récursive dans une instruction SELECT

L'exemple suivant montre la hiérarchie des composants et assemblys de produits nécessaires pour construire la bicyclette pour ProductAssemblyID = 800.

USE AdventureWorks2022;
GO

WITH Parts (AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID,
           b.ComponentID,
           b.PerAssemblyQty,
           b.EndDate,
           0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID,
           bom.ComponentID,
           p.PerAssemblyQty,
           bom.EndDate,
           ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
         INNER JOIN Parts AS p
             ON bom.ProductAssemblyID = p.ComponentID
            AND bom.EndDate IS NULL
)
SELECT AssemblyID,
       ComponentID,
       Name,
       PerAssemblyQty,
       EndDate,
       ComponentLevel
FROM Parts AS p
     INNER JOIN Production.Product AS pr
         ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;

F. Utiliser une expression de table commune récursive dans une instruction UPDATE

L’exemple suivant met à jour la PerAssemblyQty valeur de toutes les parties utilisées pour générer le produit 'Road-550-W Yellow, 44' (ProductAssemblyID 800). L'expression de table commune retourne une liste hiérarchique des parties utilisées pour générer ProductAssemblyID 800 et des composants utilisés pour créer ces parties, et ainsi de suite. Seules les lignes renvoyées par l'expression de table commune récursive sont modifiées.

USE AdventureWorks2022;
GO

WITH Parts (AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID,
           b.ComponentID,
           b.PerAssemblyQty,
           b.EndDate,
           0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID,
           bom.ComponentID,
           p.PerAssemblyQty,
           bom.EndDate,
           ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
         INNER JOIN Parts AS p
             ON bom.ProductAssemblyID = p.ComponentID
            AND bom.EndDate IS NULL
)
UPDATE Production.BillOfMaterials
    SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
     INNER JOIN Parts AS d
         ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;

H. Utiliser plusieurs membres de signet et récursifs

L'exemple suivant utilise plusieurs membres d'ancrage et récursifs pour retourner tous les ancêtres d'une personne donnée. Une table est créée et les valeurs insérées pour établir la généalogie familiale renvoyée par l'expression de table commune récursive.

-- Genealogy table
IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
DROP TABLE dbo.Person;
GO

CREATE TABLE dbo.Person
(
    ID INT,
    Name VARCHAR (30),
    Mother INT,
    Father INT
);
GO

INSERT dbo.Person VALUES
(1, 'Sue', NULL, NULL),
(2, 'Ed', NULL, NULL),
(3, 'Emma', 1, 2),
(4, 'Jack', 1, 2),
(5, 'Jane', NULL, NULL),
(6, 'Bonnie', 5, 4),
(7, 'Bill', 5, 4);
GO

-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
    -- First anchor member returns Bonnie's mother.
    SELECT Mother
    FROM dbo.Person
    WHERE Name = 'Bonnie'
    UNION
    -- Second anchor member returns Bonnie's father.
    SELECT Father
    FROM dbo.Person
    WHERE Name = 'Bonnie'
    UNION ALL
    -- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID = Person.ID
    UNION ALL
    -- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, dbo.Person
    WHERE Generation.ID = Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, dbo.Person
WHERE Generation.ID = Person.ID;
GO

Je. Utiliser des fonctions analytiques dans une expression de table commune récursive

L'exemple suivant montre un piège qui peut se produire lors de l'utilisation d'une fonction analytique ou d'agrégation dans la partie récursive d'une expression CTE.

DECLARE @t1 TABLE (itmID INT, itmIDComp INT);
INSERT @t1 VALUES (1, 10), (2, 10);

DECLARE @t2 TABLE (itmID INT, itmIDComp INT);
INSERT @t2 VALUES (3, 10), (4, 10);

WITH vw AS
(
    SELECT itmIDComp, itmID FROM @t1
    UNION ALL SELECT itmIDComp, itmID FROM @t2
),
r AS
(
    SELECT t.itmID AS itmIDComp,
           NULL AS itmID,
           CAST (0 AS BIGINT) AS N,
           1 AS Lvl
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t(itmID)
    UNION ALL
    SELECT t.itmIDComp,
           t.itmID,
           ROW_NUMBER() OVER (PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N,
           Lvl + 1
    FROM r
         INNER JOIN vw AS t
             ON t.itmID = r.itmIDComp
)
SELECT Lvl, N FROM r;

Les résultats suivants sont ceux attendus pour la requête.

Les résultats suivants sont les résultats réels de la requête.

N retourne la valeur 1 à chaque passage de la partie récursive de l'expression CTE, car seul le sous-ensemble de données de ce niveau de récursivité est transmis à ROWNUMBER. Pour chacune des itérations de la partie récursive de la requête, une seule ligne est passée à ROWNUMBER.

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

J. Utiliser une expression de table commune dans une instruction CTAS

L’exemple suivant crée une table qui contient le nombre total de commandes client réalisées par chaque représentant commercial chez Adventure Works Cycles.

USE AdventureWorks2022;
GO

CREATE TABLE SalesOrdersPerYear
WITH (DISTRIBUTION = HASH(SalesPersonID)) AS
    -- Define the CTE expression name and column list.
    WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS
    -- Define the CTE query.
    (
        SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
        FROM Sales.SalesOrderHeader
        WHERE SalesPersonID IS NOT NULL
    )
    -- Define the outer query referencing the CTE name.
    SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
    FROM Sales_CTE
    GROUP BY SalesYear, SalesPersonID
    ORDER BY SalesPersonID, SalesYear;
GO

K. Utiliser une expression de table commune dans une instruction CETAS

L’exemple suivant crée une table externe qui contient le nombre total de commandes client réalisées par chaque représentant commercial chez Adventure Works Cycles.

USE AdventureWorks2022;
GO
CREATE EXTERNAL TABLE SalesOrdersPerYear
WITH
(
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:5000/files/Customer',
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|' )
) AS
    -- Define the CTE expression name and column list.
    WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS
    -- Define the CTE query.
    (
        SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
        FROM Sales.SalesOrderHeader
        WHERE SalesPersonID IS NOT NULL
    )
    -- Define the outer query referencing the CTE name.
    SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
    FROM Sales_CTE
    GROUP BY SalesYear, SalesPersonID
    ORDER BY SalesPersonID, SalesYear;
GO

L. Utiliser plusieurs expressions de table communes, séparées par des virgules, dans une instruction

L’exemple suivant montre comment inclure deux expressions de table communes dans la même instruction. Les expressions de table communes ne peuvent pas être imbriquées (pas de récursivité).

WITH CountDate (TotalCount, TableName) AS
(
    SELECT COUNT(datekey), 'DimDate' FROM DimDate
),
CountCustomer (TotalAvg, TableName) AS
(
    SELECT COUNT(CustomerKey), 'DimCustomer' FROM DimCustomer
)
SELECT TableName, TotalCount
FROM CountDate
UNION ALL
SELECT TableName, TotalAvg FROM CountCustomer;