WITH (CTE)
Une CTE (WITH) donne un nom à un résultat intermédiaire, utilisable ensuite comme une table dans la requête. On découpe ainsi un calcul complexe en étapes lisibles et vérifiables.
- CTE
- Common Table Expression
- WITH
- lisibilité
Syntaxe
WITH etape1 AS ( SELECT ...),etape2 AS ( SELECT ... FROM etape1)SELECT * FROM etape2;Exemple
WITH montants AS ( SELECT o.customer_id, SUM(oi.quantity * p.price) AS montant FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id WHERE o.status <> 'annulé' GROUP BY o.customer_id)SELECT c.name, ROUND(m.montant, 2) AS montantFROM montants mJOIN customers c ON c.id = m.customer_idWHERE m.montant > 150ORDER BY m.montant DESC;-- Jeu de données : boutique-- Clients ayant dépensé plus de 150 €-- Résultat attendu :-- Lucas Martin | 209.96-- Emma Dubois | 179.98À retenir
- Plusieurs CTE se séparent par des virgules, et chacune peut lire les précédentes : WITH a AS (…), b AS (SELECT … FROM a) SELECT … FROM b.
- Méthode de mise au point : exécutez d'abord la CTE seule (SELECT * FROM montants), vérifiez-la, puis construisez la requête finale dessus.
- Piège : une CTE n'existe que pour la requête qui la suit immédiatement. Ce n'est ni une table ni une vue : pour la réutiliser ailleurs, créez une vue.
- Une CTE citée plusieurs fois peut être recalculée à chaque fois. SQLite (3.35+) et PostgreSQL (12+) acceptent AS MATERIALIZED pour forcer un calcul unique.
Voir aussi
- Sous-requêtesUne sous-requête est un SELECT placé à l'intérieur d'une autre requête, dans WHERE, FROM ou SELECT. Elle calcule une valeur intermédiaire, comme une moyenne, à laquelle on compare chaque ligne.
- 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.
- CREATE VIEW (vues)Une vue est une requête enregistrée sous un nom, qu'on interroge ensuite comme une table. Elle évite de réécrire une jointure complexe et permet de n'exposer que certaines colonnes.
- Fonctions de fenêtre (OVER)Une fonction de fenêtre calcule un agrégat sur un groupe de lignes tout en gardant chaque ligne, à l'inverse de GROUP BY. On compare ainsi chaque salaire à la moyenne de son service.