WITH common_table_expression (Transact-SQL)

Spécifie un jeu de résultats nommé temporaire, désigné par le terme d'expression de table commune (CTE, Common Table Expression). Cette clause est dérivée d'une requête simple et définie au sein de l'étendue d'exécution d'une seule instruction SELECT, INSERT, UPDATE, MERGE ou DELETE. Cette clause peut également être utilisée dans une instruction CREATE VIEW comme faisant partie de l'instruction SELECT qui la définit. 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 syntaxe de Transact-SQL

Syntaxe

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

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

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 peut être identique au nom d'une table de base ou d'une vue. Toute référence à expression_name dans la requête utilise l'expression de table commune et non 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, Common Table Expression). 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 instruction SELECT 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 conditions que celles requises pour la création d'une vue, mis à part qu'une expression de table commune ne peut pas en définir une autre. Pour plus d'informations, consultez la section Remarque et CREATE VIEW (Transact-SQL).

  Si plusieurs paramètres CTE_query_definition sont définis, les définitions de requêtes doivent être jointes par l'un des opérateurs de jeu ci-après : UNION ALL, UNION, EXCEPT ou INTERSECT. Pour plus d'informations sur l'utilisation des définitions de requêtes CTE récursives, consultez la section « Remarques » ci-dessous et la rubrique Requêtes récursives utilisant des expressions de table communes.

Notes

Principes de création et d'utilisation des expressions de table communes

Les principes suivants s'appliquent à des expressions de table communes non récursives. Pour obtenir les instructions qui s'appliquent à des expressions de table communes récursives, consultez « Principes de définition et d'utilisation des expressions de table communes récursives » à suivre.

• Une expression de table commune doit être suivie d'une seule instruction SELECT, INSERT, UPDATE, MERGE ou DELETE qui fait référence à certaines ou à toutes les colonnes de l'expression de table commune. 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 CTE_query_definition contient une sous-requête, celle-ci ne peut pas contenir de clause WITH imbriquée définissant une autre expression de table commune.

• Les clauses suivantes ne peuvent pas être utilisées dans CTE_query_definition :

  • COMPUTE ou COMPUTE BY

  • ORDER BY (sauf si une clause TOP est spécifiée)

  • INTO

  • Clause OPTION avec des indicateurs de requête

  • FOR XML

  • FOR BROWSE

• 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 de serveurs distants peuvent être référencées dans l'expression de table commune.

• Lors de l'exécution d'une expression de table commune, tous les indicateurs faisant référence à une expression de table commune peuvent entrer en conflit avec d'autres indicateurs découverts lorsque l'expression de table commune accède à ses tables sous-jacentes, de la même manière que les indicateurs qui font référence à des vues dans des requêtes. Dans ce cas, la requête renvoie une erreur. Pour plus d'informations, consultez Résolution de vues.

• Lorsqu'une expression de table commune est la cible d'une instruction UPDATE, toutes les références à l'expression de table commune dans l'instruction doivent correspondre. Par exemple, si un alias est affecté à l'expression de table commune dans la clause FROM, cet alias doit être utilisé pour toutes les autres références à l'expression de table commune. Les références ambiguës à l'expression de table commune peuvent entraîner un comportement de jointure et des résultats de requête inattendus. Pour plus d'informations, consultez UPDATE (Transact-SQL).

Principes de définition et d'utilisation des expressions de table communes récursives

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

• 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 communesont des membres d'ancrage à moins qu'elles ne fassent référence à l'expression de table commune elle-même.

• Les membres d'ancrage doivent être associés par l'un des opérateurs de jeu ci-après : UNION ALL, UNION, INTERSECT ou EXCEPT. UNION ALL est le seul opérateur défini autorisé entre le dernier membre d'ancrage et le premier membre récursif, ainsi que 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 ne doit faire référence qu'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

  • HAVING

  • Agrégation scalaire

  • TOP

  • LEFT, RIGHT, OUTER JOIN (INNER JOIN est autorisé)

  • Des 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.

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

• Toutes les colonnes renvoyées par une expression de table commune récursive peuvent prendre la valeur NULL que les colonnes renvoyées par les instructions SELECT participantes puissent prendre la valeur NULL ou non.

• Une expression de table commune récursive incorrectement composée 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 éviter une boucle infinie, vous pouvez limiter le nombre de niveaux de récursivité autorisés pour une instruction spécifique à l'aide de l'indicateur MAXRECURSION et une valeur entre 0 et 32 767 dans la clause OPTION de l'instruction INSERT, UPDATE, MERGE, DELETE ou SELECT. 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. Seule une valeur MAXRECURSION peut être spécifiée par instruction. Pour plus d'informations, consultez Indicateurs de requête (Transact-SQL).

• Une vue 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 des requêtes à l'aide d'expressions de table communes. 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 de 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 puissent être accédées localement de manière répétée. S'il s'agit d'une requête d'expression de table commune, Index Spool/Lazy Spools est affiché dans le plan de requête avec le prédicat WITH STACK supplémentaire. 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 K. Using analytical functions in a recursive CTE.

Exemples

A. Création d'une expression de table commune simple

L'exemple suivant montre le nombre total de commandes par an pour chaque commercial d'Adventure Works Cycles.

USE AdventureWorks2008R2;
GO
-- 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

B. Utilisation d'une expression de table commune pour limiter les comptes et les moyennes de rapports

L'exemple suivant montre le nombre moyen de commandes pour chaque commercial calculé sur toutes les années.

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

C. Utilisation d'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 par la création et le remplissage de 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 int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- 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);

USE AdventureWorks2008R2;
GO
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;
GO

D. Utilisation d'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 renvoyés est limité à deux.

USE AdventureWorks2008R2;
GO
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 ;
GO

E. Utilisation d'une expression de table commune récursive pour afficher une liste hiérarchique

L'exemple suivant prolonge l'exemple C en ajoutant 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.

USE AdventureWorks2008R2;
GO
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
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

F. Utilisation de 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.

USE AdventureWorks2008R2;
GO
--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 
    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);
GO

Après la correction de l'erreur de codage, MAXRECURSION n'est plus nécessaire. L'exemple suivant montre le code corrigé.

USE AdventureWorks2008R2;
GO
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
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

G. Utilisation d'une expression de table commune pour parcourir de façon sélective une relation récursive dans une instruction SELECT

Le diagramme suivant montre la hiérarchie des composants et assemblies de produits nécessaires pour monter la bicyclette pour ProductAssemblyID = 800.

USE AdventureWorks2008R2;
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;
GO

H. Utilisation d'une expression de table commune récursive dans une instruction UPDATE

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

USE AdventureWorks2008R2;
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
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0; 

I. Utilisation des membres d'ancrage et récursifs

L'exemple suivant utilise plusieurs membres d'ancrage et récursifs pour renvoyer tous les ancêtres d'une personne donnée. Une table est créée et des valeurs sont insérées pour établir la généalogie familiale retourné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

J. Utilisation de 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 
    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.

Lvl  N
1    0
1    0
1    0
1    0
2    4
2    3
2    2
2    1

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

Lvl  N
1    0
1    0
1    0
1    0
2    1
2    1
2    1
2    1

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.

Voir aussi

Référence

CREATE VIEW (Transact-SQL)

DELETE (Transact-SQL)

EXCEPT et INTERSECT (Transact-SQL)

INSERT (Transact-SQL)

SELECT (Transact-SQL)

UPDATE (Transact-SQL)

Concepts

Requêtes récursives utilisant des expressions de table communes

Utilisation d'expressions de table communes