Aller au contenu
Sous-requêtes & CTE

CTE récursive (WITH RECURSIVE)

Une CTE récursive part d'un point de départ puis se rappelle elle-même pour avancer d'un niveau à chaque tour. Elle parcourt une hiérarchie complète ou génère une suite de dates.

  • récursif
  • hiérarchie
  • arbre
  • organigramme
  • calendrier
  • WITH RECURSIVE

Syntaxe

Modèle
WITH RECURSIVE cte(colonnes) AS (  SELECT ...              -- membre d'ancrage : point de départ  UNION ALL  SELECT ... FROM cte     -- membre récursif : étape suivante  WHERE condition_arret)SELECT * FROM cte;

Exemple

Exemple concret
WITH RECURSIVE organigramme(id, name, niveau, chemin) AS (  SELECT id, name, 0, name  FROM employees  WHERE manager_id IS NULL  UNION ALL  SELECT e.id, e.name, o.niveau + 1, o.chemin || ' > ' || e.name  FROM employees e  JOIN organigramme o ON e.manager_id = o.id)SELECT niveau, cheminFROM organigrammeORDER BY chemin;-- Jeu de données : entreprise-- Résultat attendu :-- 0 | Isabelle Renard-- 1 | Isabelle Renard > Olivier Caron-- 2 | Isabelle Renard > Olivier Caron > Quentin Faure-- 2 | Isabelle Renard > Olivier Caron > Rachid Amrani-- 2 | Isabelle Renard > Olivier Caron > Wendy Roche-- 1 | Isabelle Renard > Patricia Meyer-- 2 | Isabelle Renard > Patricia Meyer > Ursula Klein-- 1 | Isabelle Renard > Sandrine Lopez-- 2 | Isabelle Renard > Sandrine Lopez > Théo Brun-- 1 | Isabelle Renard > Victor Nguyen

À retenir

  1. Déroulé : le membre d'ancrage donne la directrice (niveau 0). Le membre récursif ajoute ses subordonnés directs (niveau 1), puis leurs subordonnés, jusqu'à ce qu'aucune nouvelle ligne n'apparaisse.
  2. Génération d'un calendrier : WITH RECURSIVE jours(d) AS (SELECT '2024-03-01' UNION ALL SELECT date(d, '+1 day') FROM jours WHERE d < '2024-03-31') SELECT d FROM jours. Joint en LEFT JOIN aux commandes, il fait apparaître les jours sans vente.
  3. Piège : sans condition d'arrêt, ou avec un cycle dans les données (A manager de B, B manager de A), la récursion ne s'arrête jamais. Limitez la profondeur (WHERE o.niveau < 10) ou utilisez UNION au lieu d'UNION ALL pour ignorer les lignes déjà vues.
  4. SQL Server écrit simplement WITH, sans le mot RECURSIVE, et coupe par défaut après 100 niveaux (option MAXRECURSION).

Voir aussi