Ce cas pratique combine CTE récursive et agrégation — les notions de ce chapitre.

WITH RECURSIVE hierarchie AS (
  SELECT id, nom, parent_id, id AS racine
  FROM categories WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.nom, c.parent_id, h.racine
  FROM categories c
  JOIN hierarchie h ON c.parent_id = h.id
)
SELECT racine, COUNT(*) - 1 AS nb_descendants
FROM hierarchie
GROUP BY racine;

Étape par étape :

1. La CTE récursive propage racine (l'identifiant de la catégorie de départ) à chaque enfant ajouté, sans jamais le changer en cours de route.
2. Pour racine = 1 (Électronique), la CTE accumule 1, 2, 4 et 3 : 4 lignes au total, dont 1 est la racine elle-même.
3. COUNT(*) - 1 retire la racine du compte, ne gardant que ses véritables descendants : 3.

Pour racine = 5 (Vêtements) : 2 lignes accumulées (5 et 6), soit 1 descendant.

À retenir :

• Propager une valeur constante (ici racine) à travers chaque tour de récursion permet de savoir, à la fin, de quelle branche chaque ligne provient.
• GROUP BY s'applique normalement au résultat final d'une CTE récursive, exactement comme sur n'importe quelle autre table ou sous-requête.

À vous de jouer : quelle est la valeur de nb_descendants pour la racine 5 (Vêtements) ?