1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
|
WITH classe AS
(
SELECT 'julien' prenom , 1 bon_points FROM DUAL
UNION ALL SELECT 'eric' prenom , 1 bon_points FROM DUAL
UNION ALL SELECT 'eric' prenom , 3 bon_points FROM DUAL
UNION ALL SELECT 'paul' prenom , 1 bon_points FROM DUAL
UNION ALL SELECT 'paul' prenom , 5 bon_points FROM DUAL
),
liste_prenoms AS
(
SELECT 'julien' prenom_filtre FROM DUAL
UNION ALL
SELECT 'eric' prenom_filtre FROM DUAL
UNION ALL
SELECT 'remi' prenom_filtre FROM DUAL
UNION ALL
SELECT 'paul' prenom_filtre FROM DUAL
)
SELECT prenom_filtre,sum(nvl(bon_points,0)) FROM liste_prenoms
LEFT OUTER JOIN classe ON prenom = prenom_filtre
GROUP BY prenom_filtre |
Partager