Récapitulatif des Common Table Expressions (CTE)

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

Jasmin Ludolf

Content Developer

Aperçu du cours

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

Image de poupées russes illustrant la récursivité.

Qu'est-ce qu'une CTE ?

Définition d'une CTE

Spécifie un jeu de résultats temporaire et nommé, appelé common table expression (CTE). Il provient d'une requête simple et est défini dans la portée d'exécution d'une seule instruction

Structure d'une CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

Utiliser les CTE

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

Définir une CTE pour une organisation TI

Sélectionner les gestionnaires avec une CTE :

WITH JOBS (id, SurName, JobTitle) AS

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

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

Définir une CTE pour une organisation TI

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

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

Définir une CTE pour une organisation TI

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