WITH common_table_expression (Transact-SQL)

Spécifie un ensemble de résultats nommé temporaire, désigné par le terme d'expression de table connue (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 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 par le terme expression de table commune récursive.

Conventions de la 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 l'ensemble 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 associées par l'un des opérateurs définis 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 connaître les principes s'appliquant aux expressions de table communes récursives, consultez la section « Principes de définition et d'utilisation des expressions de table communes récursives » ci-dessous.

• Une expression de table commune doit être suivie d'une seule instruction SELECT, INSERT, UPDATE 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 combinées par l'un des opérateurs définis suivants : UNION ALL, UNION, EXCEPT ou INTERSECT.
• 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 lot, 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. Lorsque cela se produit, la requête renvoie une erreur. Pour plus d'informations, consultez Résolution de vues.

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

Les principes suivants s'appliquent à la définition d'expressions de table communes récursives :

• 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'ancrage doivent être combinés par l'un des opérateurs définis suivants : UNION ALL, UNION, EXCEPT ou INTERSECT. 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'expressions de table communes récursives :

• 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, 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 pour l'ensemble du serveur est de 100. Si la valeur 0 est spécifiée, aucune limite n'est appliquée. Seule une valeur MAXRECURSION peut être spécifiée par instruction. Pour plus d'informations, consultez Indicateur 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 (capture instantanée) 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.

Exemples

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

L'exemple suivant affiche le nombre d'employés sous les ordres directs de chaque responsable dans Adventure Works Cycles.

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) 
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports 
FROM DirReps 
ORDER BY ManagerID;
GO

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

L'exemple suivant affiche le nombre moyen d'employés sous les ordres des responsables.

WITH DirReps (Manager, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
) 
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps 
WHERE DirectReports>= 2 ;
GO

C. Références multiples à une expression de table commune

L'exemple suivant affiche le nombre total de commandes et la date de commande la plus récente dans la table SalesOrderHeader pour chaque vendeur. Dans l'instruction d'exécution, l'expression de table commune est référencée deux fois : une fois pour renvoyer les colonnes sélectionnées pour le vendeur et une autre fois pour extraire les mêmes détails pour le responsable du vendeur. Les données pour le vendeur et le responsable sont renvoyées dans une seule ligne.

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

D. 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.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;
GO

E. 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 AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 ;
GO

F. 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 AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

G. 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 AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee 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 AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

H. 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 AdventureWorks;
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

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

L'exemple suivant augmente la valeur VacationHours de 25 pour cent pour tous les employés directement ou indirectement sous les ordres de ManagerID 12. L'expression de table commune récursive renvoie une liste hiérarchique d'employés directement sous les ordres de ManagerID 12, des employés sous les ordres de ces employés, etc.. Seules les lignes renvoyées par l'expression de table commune récursive sont modifiées.

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

J. 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 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('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(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 Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM 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, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO

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)

Autres ressources

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

Aide et Informations

Assistance sur SQL Server 2005