1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| select t.annee,t.id_c,t.lib_chef
from (select adh_cot.annee,a.id as id_c,a.libelle as lib_chef
max(case when adh_cot.annee = 2006 then nb_cot else 0 end) as nb_cot_2006,
max(case when adh_cot.annee = 2007 then nb_cot else 0 end) as nb_cot_2007,
max(case when adh_cot.annee = 2008 then nb_cot else 0 end) as nb_cot_2008
from (select c.annee,adh.id_chef,count(*) as nb_cot
from adherent adh
JOIN cotisation c ON adh.id=c.id_adherent
where c.annee in (2006,2007,2008)
and adh.chef=0
group by c.annee,adh.id_chef
) as adh_cot
left join adherent a ON adh_cot.id_chef = a.id
group by adh_cot.annee,a.id,a.libelle
) as t
where t.nb_cot_2006 > 0
and t.nb_cot_2007 = 0
and t.nb_cot_2008 > 0 |
Partager