[SQL Server] Utilisation des CTE

Récemment, j’ai été amené à de nouveau utiliser ce qu’on appelle des expressions CTE (Common Table Expression).
Ayant un peu perdu l’habitude de les utiliser sous SQL Server, je me suis dit qu’un article sur cette fonctionnalité ne ferait pas de mal 🙂

Présentation
Les expressions CTE, ou requêtes récursives, ont été introduites depuis la version 2005 de SQL Server, mais cette fonctionnalité existait déjà dans d’autres SGBD (Oracle notamment).
Une expression CTE se présente dans une requête sous la forme d’une table temporaire dont la durée de vie est celle de la requête. Cette expression CTE sera interrogée de façon récursive afin d’en sortir un SELECT de résultats.

On définit une CTE en ajoutant une clause WITH avant un SELECT, INSERT, UPDATE, DELETE ou MERGE. On peut également l’utiliser dans une vue avant un SELECT.
La clause peut inclure une ou plusieurs CTE, comme le montre la syntaxe suivante:

[WITH <common_table_expression> [,…]]
<common_table_expression>::=
cte_name [(column_name [,…])]
AS (cte_query)

Exemple d’application simple (sans récursivité)
Nous allons maintenant directement voir dans quels cas concrètement utiliser ces expression, puis les expliquer via un exemple.
Problèmatique : Différents commerciaux réalisent un chiffre d’affaires pour leurs ventes à certaines dates, ces commerciaux ont des Managers afin de les superviser. Nous désirons voir pour chacun des commerciaux leur CA individuel et aggregé par dates.

Représentons ce problème via des tables sous SQL Server. Nous avons une table « sales » qui contiendra les données chiffrées des ventes associées à un commercial, puis une table SalesPeople qui contiendra l’ensemble des commerciaux et managers.

On notera deux choses :
– La même table stocke les commerciaux et manager, la jointure est réflexive ;
– Un commercial peut faire plusieurs ventes.

Sales CTE Schéma

Alimentons les tables de données fictives :
Données tables

Créons à présent notre expression CTE, qui va calculer la somme des ventes groupées par commerciaux :

CTE Requête

A présent que nous avons une table CTE « TotalAggregateSales » qui contient l’ensemble des données aggregées des ventes par commerciaux, nous allons pouvoir afficher ces résultats par un SELECT :

Select CTE

Voici le résultat obtenu de la requête :

Résultat requête CTE

Bien, tout cela est intéressant, mais nous n’avons pas encore vu comment utiliser la récursivité avec les expressions CTE !

On désire afficher, toujours avec les CA réalisés, les noms des commerciaux et leurs managers associés. Pour ce faire, nous allons dans notre CTE sélectionner dans un premier temps l’ensemble des commerciaux qui n’ont pas de manager, puis faire une union avec ceux qui ont un manager :

CTE Récursive

Nous obtenons comme résultat une liste des commerciaux avec leurs ID, et l’identifiant de leur manager respectif ainsi que le nombre de manager pour chacun des commerciaux :

Résultat CTE récursive

Sources : MSDN

Suggestion d'articles

Partagez:

2 réflexions au sujet de « [SQL Server] Utilisation des CTE »

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.