1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
|
SELECT d.noms as noms, s.genre as sexe,
COUNT(dl.id_legis) as total_legis, GROUP_CONCAT(CAST(dl.id_legis AS CHAR) ORDER BY dl.id_legis SEPARATOR ", " ) AS liste_legis,
COUNT(DISTINCT pp.id_parti) as total_parti, GROUP_CONCAT(DISTINCT pp.sigle ORDER BY pp.id_parti SEPARATOR ", " ) AS liste_parti,
COUNT(DISTINCT dl.id_dpt) as total_dpt, GROUP_CONCAT(DISTINCT d2.libelledpt ORDER BY d2.libelledpt SEPARATOR ", " ) AS liste_dpt,
r.libelleregion as region
FROM deputes AS d
LEFT JOIN sexes AS s ON d.sexe = s.id_sexe
LEFT JOIN depute_legislatures AS dl ON dl.id_depute = d.id_depute
LEFT JOIN partis_politiques AS pp ON dl.id_parti = pp.id_parti
LEFT JOIN departements AS d2 ON d2.iddpt = dl.id_dpt
LEFT JOIN regions AS r ON r.idregion = d2.idregion
WHERE d.sexe = 1
GROUP BY dl.id_depute
HAVING total_legis > 1
ORDER BY total_legis DESC, d.noms |
Partager