Récapitulatif des Common Table Expressions (CTE)

Requêtes hiérarchiques et récursives dans SQL Server

Jasmin Ludolf

Content Developer

Plan du cours

Qu'est-ce que la récursion et comment l'utiliser ?
Comment utiliser les Common Expression Tables (CTE) pour des requêtes hiérarchiques et récursives ?
Comment représenter une hiérarchie en SQL et l'interroger ?
Exemples concrets (organisation d'entreprise, nomenclature, plan de vol)

Image de poupées russes et leur propriété récursive.

Qu'est-ce qu'un CTE ?

Définition d'un CTE

Spécifie un jeu de résultats temporaire et nommé, appelé common table expression (CTE). Il est dérivé d'une requête simple et défini dans le périmètre d'exécution d'une seule instruction

Structure d'un CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

Utilisation des CTE

Gérer des requêtes complexes
Utilisable dans les instructions SELECT, INSERT, UPDATE ou DELETE
Plusieurs CTE peuvent être définis dans une même clause WITH
Combiner plusieurs CTE avec UNION ou JOIN
Remplacer une vue
Auto-référencer une table
Requête récursive

Définir un CTE pour une organisation IT

Sélectionner les managers avec un CTE :

WITH JOBS (id, SurName, JobTitle) AS

(
    SELECT 
          ID, 
          Name,
          Position
    FROM employee
    WHERE Position like '%Manager%'
),

WITH AS pour définir le CTE
SELECT sur la table Employee

Définir un CTE pour une organisation IT

Sélectionner les employés avec un salaire supérieur à 10 000

SALARIES (ID,Salary) AS (
   SELECT
        ID,
        Salary
   FROM ITSalary
   WHERE Salary > 10000)

Définir un CTE pour une organisation IT

La requête complète :

WITH JOBS (id, SurName, JobTitle) AS 
(    SELECT ID, Name, Position
        FROM employee 
        WHERE Position like '%Manager%'),

SALARIES (ID,Salary) AS
(    SELECT ID, Salary
        FROM ITSalary 
        WHERE Salary > 10000)

SELECT JOBS.Name, JOBS.Position, SALARIES.Salary
    FROM JOBS
    INNER JOIN SALARIES
    on JOBS.ID = SALARIES.ID;

SurNmames     | JobTitle          | Salary
Paul Smith    | IT Manager        | 15,000
Adam Peterson | Sourcing Manager  | 12,500
Anna Nilson   | Portfolio Manager | 10,500

Passons à la pratique !

Requêtes hiérarchiques et récursives dans SQL Server