Utilisation d'expressions de table communes
Une expression de table commune (CTE, Common Table Expression) peut être considérée comme un jeu de résultats temporaire défini dans l'étendue d'exécution d'une seule instruction SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Une expression CTE est comparable à une table dérivée, en ce sens qu'elle n'est pas stockée sous forme d'objet et dure uniquement le temps de la requête. Contrairement à une table dérivée, une expression CTE peut faire référence à elle-même est être référencée plusieurs fois dans la même requête.
Une expression CTE peut être utilisée pour :
- créer une requête récursive. Pour plus d'informations, consultez Requêtes récursives utilisant des expressions de table communes.
- remplacer une vue lorsque l'usage général d'une vue n'est pas nécessaire, c'est-à-dire que la définition n'a pas besoin d'être stockée dans des métadonnées ;
- permettre le groupement par une colonne dérivée d'une sous-sélection scalaire, ou d'une fonction non déterministe ou à accès externe ;
- faire plusieurs fois référence à la table résultante dans la même instruction.
L'utilisation d'une expression CTE présente pour avantages une meilleure lisibilité et une plus grande facilité de maintenance des requêtes complexes. La requête peut être divisée en blocs de construction logiques simples. Ces blocs simples peuvent servir ensuite à créer des expressions CTE intermédiaires plus complexes jusqu'à la génération du jeu de résultats final.
Les expressions CTE peuvent être écrites dans des routines définies par l'utilisateur, telles que des fonctions, des procédures stockées, des déclencheurs ou des vues.
Structure d'une expression CTE
Une expression CTE est composée du nom représentant l'expression CTE, d'une liste de colonnes facultative et d'une requête définissant l'expression CTE. Une fois l'expression CTE définie, elle peut être référencée comme une table ou une vue dans une instruction SELECT, INSERT, UPDATE ou DELETE. Une expression CTE peut également être utilisée dans une instruction CREATE VIEW dans le cadre de son instruction SELECT de définition.
La structure de base de la syntaxe d'une expression CTE est la suivante :
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
La liste des noms de colonne est facultative uniquement si des noms distincts pour toutes les colonnes résultantes sont fournis dans la définition de la requête.
L'instruction permettant d'exécuter l'expression CTE est la suivante :
SELECT <column_list>
FROM expression_name
Exemple
L'exemple suivant illustre les composants de la structure CTE : nom de l'expression, liste de colonnes et requête. L'expression CTE Sales_CTE possède trois colonnes (SalesPersonID, NumberOfOrders et MaxDate) et est définie comme le nombre total de commandes et la date de la commande la plus récente de la table SalesOrderHeader pour chaque représentant commercial. Lors de l'exécution de l'instruction, l'expression CTE est référencée deux fois : l'une pour retourner les colonnes sélectionnées concernant le représentant commercial et la suivante pour extraire des informations comparables concernant le responsable du représentant commercial. Les données du représentant commercial et du responsable sont retournées sous forme d'une ligne unique.
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
Voici un jeu de résultats partiel :
EmployeeID NumberOfOrders MaxDate ManagerID NumberOfOrders MaxDate
----------- -------------- ---------- --------- -------------- ----------
268 48 2004-06-01 273 NULL NULL
275 450 2004-06-01 268 48 2004-06-01
276 418 2004-06-01 268 48 2004-06-01
277 473 2004-06-01 268 48 2004-06-01
Voir aussi
Autres ressources
WITH common_table_expression (Transact-SQL)
SELECT (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
CREATE VIEW (Transact-SQL)