WITH common_table_expression (Transact-SQL)WITH common_table_expression (Transact-SQL)

S’APPLIQUE À : ouiSQL Server ouiAzure SQL Database ouiAzure SQL Data Warehouse ouiParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Spécifie un jeu de résultats nommé temporaire, désigné par le terme d'expression de table commune (CTE, Common Table Expression).Specifies a temporary named result set, known as a common table expression (CTE). 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, DELETE ou MERGE.This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. Cette clause peut également être utilisée dans une instruction CREATE VIEW comme faisant partie de l'instruction SELECT qui la définit.This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. Une expression de table commune peut inclure des références à elle-même.A common table expression can include references to itself. Dans ce cas, elle est désignée en tant qu'expression de table commune récursive.This is referred to as a recursive common table expression.

Icône de lien de rubrique Conventions de la syntaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SyntaxeSyntax

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

ArgumentsArguments

expression_nameexpression_name
Identificateur valide pour l’expression de table commune.Is a valid identifier for the common table expression. 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.expression_name must be different from the name of any other common table expression defined in the same WITH <common_table_expression> clause, but expression_name can be the same as the name of a base table or view. Toute référence à expression_name dans la requête utilise l’expression de table commune à la place de l’objet de base.Any reference to expression_name in the query uses the common table expression and not the base object.

column_namecolumn_name
Spécifie un nom de colonne dans l'expression de table commune.Specifies a column name in the common table expression. 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).Duplicate names within a single CTE definition are not allowed. 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.The number of column names specified must match the number of columns in the result set of the 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.The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

CTE_query_definitionCTE_query_definition
Spécifie une instruction SELECT dont le jeu de résultats remplit l'expression de table commune.Specifies a SELECT statement whose result set populates the common table expression. L’instruction SELECT de CTE_query_definition doit remplir les 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.The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE. Pour plus d’informations, consultez la section Notes et CREATE VIEW (Transact-SQL).For more information, see the Remarks section and CREATE VIEW (Transact-SQL).

Si plusieurs 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.If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.

NotesRemarks

Principes de création et d'utilisation des expressions de table communesGuidelines for Creating and Using Common Table Expressions

Les principes suivants s'appliquent à des expressions de table communes non récursives.The following guidelines apply to nonrecursive common table expressions. 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 ci-dessous.For guidelines that apply to recursive common table expressions, see Guidelines for Defining and Using Recursive Common Table Expressions that follows.

  • Une expression de table commune (CTE) 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.A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. 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.A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

  • 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.Multiple CTE query definitions can be defined in a nonrecursive CTE. Les définitions doivent être associées par l’un des opérateurs de jeu ci-après : UNION ALL, UNION, INTERSECT ou EXCEPT.The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or 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.A CTE can reference itself and previously defined CTEs in the same WITH clause. Les références à des éléments ultérieurs ne sont pas autorisées.Forward referencing is not allowed.

  • La spécification de plusieurs clauses WITH dans une expression de table commune n'est pas autorisée.Specifying more than one WITH clause in a CTE is not allowed. Par exemple, si un argument CTE_query_definition contient une sous-requête, celle-ci ne peut pas contenir de clause WITH imbriquée qui définit une autre expression de table commune.For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • Les clauses suivantes ne peuvent pas être utilisées dans l’argument CTE_query_definition :The following clauses cannot be used in the CTE_query_definition:

    • ORDER BY (sauf si une clause TOP est spécifiée)ORDER BY (except when a TOP clause is specified)

    • INTO

    • Clause OPTION avec des indicateurs de requêteOPTION clause with query hints

    • 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.When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • Une requête faisant référence à une expression de table commune peut être utilisée pour définir le curseur.A query referencing a CTE can be used to define a cursor.

  • Les tables sur des serveurs distants peuvent être référencées dans l’expression de table commune.Tables on remote servers can be referenced in the CTE.

  • 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.When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. Lorsque cela se produit, la requête retourne une erreur.When this occurs, the query returns an error.

Principes de définition et d'utilisation des expressions de table communes récursivesGuidelines for Defining and Using Recursive Common Table Expressions

Les principes suivants s'appliquent à la définition d'une expression de table commune récursive :The following guidelines apply to defining a recursive common table expression:

  • 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.The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. 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.Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. 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.All CTE query definitions are anchor members unless they reference the CTE itself.

  • 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.Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or 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.UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.

  • Le nombre de colonnes des membres d'ancrage et récursifs doivent être identiques.The number of columns in the anchor and recursive members must be the same.

  • 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.The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.

  • 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.The FROM clause of a recursive member must refer only one time to the CTE expression_name.

  • Les éléments suivants ne sont pas autorisés dans l’argument CTE_query_definition d’un membre récursif :The following items are not allowed in the CTE_query_definition of a recursive member:

Les principes suivants s'appliquent à l'utilisation d'une expression de table commune récursive :The following guidelines apply to using a recursive common table expression:

  • 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.All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.

  • Une expression de table commune récursive incorrectement composée peut entraîner une boucle infinie.An incorrectly composed recursive CTE may cause an infinite loop. 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.For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. 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 comprise entre 0 et 32 767 dans la clause OPTION de l’instruction INSERT, UPDATE, DELETE ou SELECT.To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. 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.This lets you control the execution of the statement until you resolve the code problem that is creating the loop. La valeur par défaut à l'échelle du serveur est 100.The server-wide default is 100. Lorsque 0 est spécifié, aucune limite n'est appliquée.When 0 is specified, no limit is applied. Une seule valeur MAXRECURSION peut être spécifiée par instruction.Only one MAXRECURSION value can be specified per statement. Pour plus d’informations, consultez Indicateurs de requête (Transact-SQL).For more information, see Query Hints (Transact-SQL).

  • Une vue contenant une expression de table commune récursive ne peut pas être utilisée pour mettre à jour des données.A view that contains a recursive common table expression cannot be used to update data.

  • Les curseurs peuvent être définis sur des requêtes à l'aide d'expressions de table communes.Cursors may be defined on queries using CTEs. L’expression de table commune est l’argument select_statement qui définit le jeu de résultats du curseur.The CTE is the select_statement argument that defines the result set of the cursor. Seuls les curseurs d'avance rapide uniquement et statiques (instantané) sont autorisés pour les expressions de table communes récursives.Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. Si un autre type de curseur est spécifié dans une expression de table commune récursive, ce type est converti en statique.If another cursor type is specified in a recursive CTE, the cursor type is converted to static.

  • Les tables de serveurs distants peuvent être référencées dans l'expression de table commune.Tables on remote servers may be referenced in the CTE. 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.If the remote server is referenced in the recursive member of the CTE, a spool is created for each remote table so the tables can be repeatedly accessed locally. 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.If it is a CTE query, Index Spool/Lazy Spools is displayed in the query plan and will have the additional WITH STACK predicate. Il s'agit de l'une des méthodes permettant de confirmer la récursivité appropriée.This is one way to confirm proper recursion.

  • 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.Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the 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.Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data passed to the recursive part of the CTE. Pour plus d’informations, consultez « K. Utilisation de fonctions analytiques dans une expression de table commune récursive », plus loin dans cet article.For more information, see example K. Using analytical functions in a recursive CTE that follows.

Fonctionnalités et limitations des expressions de table commune dans SQL Data WarehouseSQL Data Warehouse et Parallel Data WarehouseParallel Data WarehouseFeatures and Limitations of Common Table Expressions in SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

L’implémentation actuelle des expressions de table communes dans SQL Data WarehouseSQL Data Warehouse et Parallel Data WarehouseParallel Data Warehouse présente les fonctionnalités et limitations suivantes :The current implementation of CTEs in SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse have the following features and limitations:

  • Une expression de table commune peut être spécifiée dans une instruction SELECT.A CTE can be specified in a SELECT statement.

  • Une expression de table commune peut être spécifiée dans une instruction CREATE VIEW.A CTE can be specified in a CREATE VIEW statement.

  • Une expression de table commune peut être spécifiée dans une instruction CREATE TABLE AS SELECT (CTAS).A CTE can be specified in a CREATE TABLE AS SELECT (CTAS) statement.

  • Une expression de table commune peut être spécifiée dans une instruction CREATE REMOTE TABLE AS SELECT (CRTAS).A CTE can be specified in a CREATE REMOTE TABLE AS SELECT (CRTAS) statement.

  • Une expression de table commune peut être spécifiée dans une instruction CREATE EXTERNAL TABLE AS SELECT (CETAS).A CTE can be specified in a CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.

  • Une table distante peut être référencée à partir d’une expression de table commune.A remote table can be referenced from a CTE.

  • Une table externe peut être référencée à partir d’une expression de table commune.An external table can be referenced from a CTE.

  • Plusieurs définitions de requête d’expression de table commune peuvent être définies dans une même expression de table commune.Multiple CTE query definitions can be defined in a CTE.

  • Une expression de table commune doit être suivie d’une seule instruction SELECT.A CTE must be followed by a single SELECT statement. Les instructions INSERT, UPDATE, DELETE et MERGE ne sont pas prises en charge.INSERT, UPDATE, DELETE, and MERGE statements are not supported.

  • 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.A common table expression that includes references to itself (a recursive common table expression) is not supported.

  • La spécification de plusieurs clauses WITH dans une expression de table commune n’est pas autorisée.Specifying more than one WITH clause in a CTE is not allowed. 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.For example, if a CTE query definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • Une clause ORDER BY ne peut pas être utilisée dans l’argument CTE_query_definition, sauf si une clause TOP est spécifiée.An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.

  • 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.When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • Quand elles sont utilisées dans des instructions préparées par sp_prepare, les expressions de table communes se comportent de la même façon que les autres instructions SELECT dans PDW.When used in statements prepared by sp_prepare, CTEs will behave the same way as other SELECT statements in PDW. Toutefois, si elles sont utilisées dans des instructions CETAS préparées par sp_prepare, leur comportement peut différer de SQL ServerSQL Server et d’autres instructions PDW en raison de la façon dont la liaison est implémentée pour sp_prepare.However, if CTEs are used as part of CETAS prepared by sp_prepare, the behavior can defer from SQL ServerSQL Server and other PDW statements because of the way binding is implemented for sp_prepare. Si l’instruction SELECT qui référence une expression de table commune utilise une colonne incorrecte qui n’existe pas dans cette expression, sp_prepare est passé sans que l’ erreur soit détectée, mais cette dernière est levée pendant sp_execute à la place.If SELECT that references CTE is using a wrong column that does not exist in CTE, the sp_prepare will pass without detecting the error, but the error will be thrown during sp_execute instead.

ExemplesExamples

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

L'exemple suivant affiche le nombre total de commandes client par année pour chaque commercial chez Adventure Works CyclesAdventure Works Cycles.The following example shows the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure 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.B. Utilisation d'une expression de table commune pour limiter les nombres et les moyennes de rapportsUsing a common table expression to limit counts and report averages

L'exemple suivant affiche le nombre moyen de commandes client de toutes les années pour les commerciaux.The following example shows the average number of sales orders for all years for the sales representatives.

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;  

C.C. Utilisation de plusieurs définitions d'expression de table commune dans une seule requêteUsing multiple CTE definitions in a single query

L'exemple ci-dessous montre comment définir plus d'une expression de table commune dans une seule requête.The following example shows how to define more than one CTE in a single query. Notez qu'une virgule sépare les définitions de requête d'expression de table commune.Notice that a comma is used to separate the CTE query definitions. La fonction FORMAT, utilisée pour afficher les montants monétaires dans un format monétaire, est disponible dans SQL Server 2012 et version ultérieure.The FORMAT function, used to display the monetary amounts in a currency format, is available in SQL Server 2012 and higher.

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  
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.Here is a partial result set.

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.D. Utilisation d'une expression de table commune récursive pour afficher plusieurs niveaux de récursivitéUsing a recursive common table expression to display multiple levels of recursion

L'exemple suivant affiche la liste hiérarchique des responsables et des employés sous leurs ordres.The following example shows the hierarchical list of managers and the employees who report to them. L'exemple commence en créant et en remplissant la table dbo.MyEmployees.The example begins by creating and populating the dbo.MyEmployees table.

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

E.E. Utilisation d'une expression de table commune récursive pour afficher deux niveaux de récursivitéUsing a recursive common table expression to display two levels of recursion

L'exemple suivant affiche les responsables et les employés sous leurs ordres.The following example shows managers and the employees reporting to them. Le nombre de niveaux retournés est limité à deux.The number of levels returned is limited to two.

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

F.F. Utilisation d'une expression de table commune récursive pour afficher une liste hiérarchiqueUsing a recursive common table expression to display a hierarchical list

L'exemple suivant prolonge l'exemple D en ajoutant les noms du responsable et des employés, ainsi que leurs titres respectifs.The following example builds on Example D by adding the names of the manager and employees, and their respective titles. La hiérarchie des responsables et des employés est mise en évidence par l'indentation de chaque niveau.The hierarchy of managers and employees is additionally emphasized by indenting each level.

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

G.G. Utilisation de MAXRECURSION pour annuler une instructionUsing MAXRECURSION to cancel a statement

MAXRECURSION peut être utilisé pour empêcher une expression de table commune récursive mal rédigée d'entrer dans une boucle infinie.MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. L'exemple suivant créée intentionnellement une boucle infinie et utilise l'indicateur MAXRECURSION pour limiter le nombre de niveaux de récursivité à deux.The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two.

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

Une fois l'erreur de codage corrigée, MAXRECURSION n'est plus nécessaire.After the coding error is corrected, MAXRECURSION is no longer required. L'exemple suivant montre le code corrigé.The following example shows the corrected code.

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

H.H. Utilisation d'une expression de table commune pour parcourir de façon sélective une relation récursive dans une instruction SELECTUsing a common table expression to selectively step through a recursive relationship in a SELECT statement

L'exemple suivant montre la hiérarchie des composants et assemblys de produits nécessaires pour construire la bicyclette pour ProductAssemblyID = 800.The following example shows the hierarchy of product assemblies and components that are required to build the bicycle for ProductAssemblyID = 800.

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

I.I. Utilisation d'une expression de table commune récursive dans une instruction UPDATEUsing a recursive CTE in an UPDATE statement

L’exemple suivant met à jour la valeur PerAssemblyQty pour tous les composants du produit 'Road-550-W Yellow, 44' (ProductAssemblyID``800).The following example updates the PerAssemblyQty value for all parts that are used to build the product '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.The common table expression returns a hierarchical list of parts that are used to build ProductAssemblyID 800 and the components that are used to create those parts, and so on. Seules les lignes renvoyées par l'expression de table commune récursive sont modifiées.Only the rows returned by the common table expression are modified.

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

J.J. Utilisation de plusieurs membres d'ancrage et récursifsUsing multiple anchor and recursive members

L'exemple suivant utilise plusieurs membres d'ancrage et récursifs pour retourner tous les ancêtres d'une personne donnée.The following example uses multiple anchor and recursive members to return all the ancestors of a specified person. 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.A table is created and values inserted to establish the family genealogy returned by the recursive CTE.

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

K.K. Utilisation de fonctions analytiques dans une expression de table commune récursiveUsing analytical functions in a recursive CTE

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.The following example shows a pitfall that can occur when using an analytical or aggregate function in the recursive part of a 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.The following results are the expected results for the query.

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.The following results are the actual results for the query.

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.N returns 1 for each pass of the recursive part of the CTE because only the subset of data for that recursion level is passed to ROWNUMBER. Pour chacune des itérations de la partie récursive de la requête, une seule ligne est passée à ROWNUMBER.For each of the iterations of the recursive part of the query, only one row is passed to ROWNUMBER.

Exemples : SQL Data WarehouseSQL Data Warehouse et Parallel Data WarehouseParallel Data WarehouseExamples: SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

L.L. Utilisation d’une expression de table commune dans une instruction CTASUsing a common table expression within a CTAS statement

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 CyclesAdventure Works Cycles.The following example creates a new table containing the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.

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

M.M. Utilisation d’une expression de table commune dans une instruction CETASUsing a common table expression within a CETAS statement

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 CyclesAdventure Works Cycles.The following example creates a new external table containing the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.

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

N.N. Utilisation de plusieurs expressions de table communes, séparées par des virgules, dans une instructionUsing multiple comma separated CTEs in a statement

L’exemple suivant montre comment inclure deux expressions de table communes dans la même instruction.The following example demonstrates including two CTEs in a single statement. Les expressions de table communes ne peuvent pas être imbriquées (pas de récursivité).The CTEs cannot be nested (no recursion).

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;  

Voir aussiSee Also

CREATE VIEW (Transact-SQL) CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
EXCEPT et INTERSECT (Transact-SQL) EXCEPT and INTERSECT (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
UPDATE (Transact-SQL)UPDATE (Transact-SQL)