Utiliser des expressions de table communes
Les expressions de table courantes (CTEs) fournissent un mécanisme permettant de définir une sous-requête qui peut ensuite être utilisée ailleurs dans une requête. Contrairement à une table dérivée, une table CTE est définie au début d’une requête et peut être référencée plusieurs fois dans la requête externe.
Les CTEs sont des expressions nommées définies dans une requête. Comme les sous-requêtes et les tables dérivées, les CTEs fournissent un moyen de décomposer les problèmes de requête en unités plus petites et plus modulaires. Les expressions CTE sont limitées dans l’étendue à l’exécution de la requête externe. Quand la requête externe se termine, la durée de vie de la CTE se termine également.
Écrire des requêtes avec des expressions CTE pour récupérer les résultats
Vous pouvez utiliser des expressions CTE pour récupérer les résultats. Pour créer une CTE, vous la définissez dans une clause WITH, en fonction de la syntaxe suivante :
WITH <CTE_name>
AS (<CTE_definition>)
Par exemple, pour utiliser un CTE pour récupérer des informations sur les commandes passées par des clients distincts, vous pouvez exécuter la requête suivante :
WITH CTE_year
AS
(
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM CTE_year
GROUP BY orderyear;
Vous nommez l’objet CTE (nommé CTE_year) à l’aide de la clause WITH, puis vous utilisez AS () pour définir votre sous-requête. Vous pouvez ensuite référencer votre CTE résultante dans la requête externe, qui, dans ce cas, est effectuée dans l’instruction SELECT finale (FROM CTE_year). Le résultat ressemble à ceci :
| année de commande | cust_count |
|---|---|
| 2019 | 67 |
| 2020 | 86 |
| 2021 | 81 |
Lorsque vous écrivez des requêtes avec des CTEs, tenez compte des instructions suivantes :
- Les expressions CTE nécessitent un nom pour l’expression de table, en plus des noms uniques pour chacune des colonnes référencées dans la clause SELECT de l’expression CTE.
- Les CTE peuvent utiliser des alias en ligne ou externes pour les colonnes.
- Contrairement à une table dérivée, une table CTE peut être référencée plusieurs fois dans la même requête avec une seule définition. Plusieurs expressions CTE peuvent également être définies dans la même clause WITH.
- Les CTEs prennent en charge la récursivité, dans laquelle l’expression est définie avec une référence à elle-même. Les CTE récursives dépassent la portée de ce module.