Requête statuts des espèces [Résolu]

heretik25 · 20/11/2012, 16h55

Bonsoir,

J'ai une requête qui me permet de récupérer des informations (statuts de protections) sur des espèces.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT 
a.tao_id_taxon,
 
b.sta_nom_statut AS statut_uicn_fc,
b1.sta_nom_statut AS statut_uicn_fr,
b2.sta_nom_statut AS statut_uicn_monde,
b3.sta_nom_statut AS statut_det_znieff,
b4.sta_nom_statut AS statut_cat_patrimoniale,
b5.sta_nom_statut AS statut_code_atlas,
b6.sta_nom_statut AS statut_sp_invasive,
b7.sta_nom_statut AS statut_sp_grand_territoire,
b8.sta_nom_statut AS statut_orgfh,
b9.sta_nom_statut AS statut_biologique,
b10.sta_nom_statut AS statut_uicn_fr_hivernant,
b11.sta_nom_statut AS statut_uicn_fr_passage
 
FROM t_taxon_observe a 
 
JOIN tj_taxon_a_statut f ON a.tao_id_taxon = f.tao_id_taxon
 
LEFT OUTER JOIN tr_statut b ON f.sta_id_statut = b.sta_id_statut AND b.tst_id_type_statut = 33
LEFT OUTER JOIN tr_statut b1 ON f.sta_id_statut = b1.sta_id_statut AND b1.tst_id_type_statut = 23
LEFT OUTER JOIN tr_statut b2 ON f.sta_id_statut = b2.sta_id_statut AND b2.tst_id_type_statut = 31
LEFT OUTER JOIN tr_statut b3 ON f.sta_id_statut = b3.sta_id_statut AND b3.tst_id_type_statut = 24
LEFT OUTER JOIN tr_statut b4 ON f.sta_id_statut = b4.sta_id_statut AND b4.tst_id_type_statut = 39
LEFT OUTER JOIN tr_statut b5 ON f.sta_id_statut = b5.sta_id_statut AND b5.tst_id_type_statut = 45
LEFT OUTER JOIN tr_statut b6 ON f.sta_id_statut = b6.sta_id_statut AND b6.tst_id_type_statut = 46
LEFT OUTER JOIN tr_statut b7 ON f.sta_id_statut = b7.sta_id_statut AND b7.tst_id_type_statut = 47
LEFT OUTER JOIN tr_statut b8 ON f.sta_id_statut = b8.sta_id_statut AND b8.tst_id_type_statut = 48
LEFT OUTER JOIN tr_statut b9 ON f.sta_id_statut = b9.sta_id_statut AND b9.tst_id_type_statut = 49
LEFT OUTER JOIN tr_statut b10 ON f.sta_id_statut = b10.sta_id_statut AND b10.tst_id_type_statut = 50
LEFT OUTER JOIN tr_statut b11 ON f.sta_id_statut = b11.sta_id_statut AND b11.tst_id_type_statut = 52
 
order by tao_id_taxon

Cette requête retourne les bonnes informations sauf qu'au lieu d'avoir une ligne avec les différentes informations, j'ai autant de ligne par espèce.

tao_id_taxon | statut_uicn_fc | statut_uicn_fr | statut_uicn_monde
   87479          "LC"               ""                 ""
   87479           ""               "LC"                ""
   87479           ""                ""                "LC"

Je voudrais fusionner les informations par l'identifiant tao_id_taxon

87479;"Lc";"LC";"LC"

La question est donc la suivante : comment faire ?

Par avance, merci de votre aide.

En PJ, le MCD et le MPD

sabotage · 20/11/2012, 18h32

Déjuà est-ce que ta requête ça ne serait pas plutot simplement ça ?

Citation:

SELECT tao_id_taxon, sta_lib_statut, sta_code_statut
FROM t_taxon_observe a
JOIN tj_taxon_a_statut f ON a.tao_id_taxon = f.tao_id_taxon
JOIN tr_statut b ON f.sta_id_statut = b.sta_id_statut
WHERE b.tst_id_type_statut IN (33, 23, 31, 24, 39,45, 46, 47 ,48, 49, 50, 52)
order by tao_id_taxon

La présentation des données regroupées ça donnerait ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$tblStatut = array(33, 23, 31, 24, 39,45, 46, 47 ,48, 49, 50, 52);
 
// recupération des résultats de la requête par taxon :
while($row = $sth->fetch(PDO::FETCH_ASSOC)) {
   $tblResult[$row['t_taxon_observe']][$row['sta_code_statut']] = 1;
}
 
// présentation en tableau
echo '<table><tr><th>Taxon</th>';
foreach ($tblStatut as $statut) {
   echo '<th>' . $statut . '</th>';
}
echo '</tr>';
foreach ($tblResult as $taxon->$values) {
    echo '<tr>';
    foreach ($tblStatut as $statut) {
         $lc = isset($values[$statut]) ? 'LC' : '';
         echo '<td>' . $lc . '</td>';
   }
   echo '</tr>';
}
echo '</table>';

Ce code est la pour te donner une idée du principe, je ne l'ai pas testé precisemment et il ne fait pas intervenir le libellé du statut dans un soucis de lisibilité.

gmarsay · 20/11/2012, 18h34

Le résultat n'est pas mieux en ajoutant un GROUP BY a.tao_id_taxon à la fin de la requête SQL ?

heretik25 · 20/11/2012, 19h18

Citation:

Envoyé par sabotage

Déjuà est-ce que ta requête ça ne serait pas plutot simplement ça ?

La présentation des données regroupées ça donnerait ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$tblStatut = array(33, 23, 31, 24, 39,45, 46, 47 ,48, 49, 50, 52);
 
// recupération des résultats de la requête par taxon :
while($row = $sth->fetch(PDO::FETCH_ASSOC)) {
   $tblResult[$row['t_taxon_observe']][$row['sta_code_statut']] = 1;
}
 
// présentation en tableau
echo '<table><tr><th>Taxon</th>';
foreach ($tblStatut as $statut) {
   echo '<th>' . $statut . '</th>';
}
echo '</tr>';
foreach ($tblResult as $taxon->$values) {
    echo '<tr>';
    foreach ($tblStatut as $statut) {
         $lc = isset($values[$statut]) ? 'LC' : '';
         echo '<td>' . $lc . '</td>';
   }
   echo '</tr>';
}
echo '</table>';

Ce code est la pour te donner une idée du principe, je ne l'ai pas testé precisemment et il ne fait pas intervenir le libellé du statut dans un soucis de lisibilité.

Bonsoir et merci pour la réponse exhaustive.

L'idée était d'avoir l'ensemble des statuts de protection pour une espèce sous forme de tableau dès la requête afin de pouvoir exporter le résultat sous format tableur à partir du requêteur...d'où cette requête un peu tarabiscotée.

ericd69 · 20/11/2012, 23h13

salut,

group by puis utilise group_concat pour rassembler les différentes valeurs en une seule colonne sinon le group by ne changera rien et tu auras autan de ligne mais simplement ordonnées par la colonne de regroupement

sabotage · 21/11/2012, 00h04

En fait ta requête de départ, bien que hérétique, devrait fonctionner à vue d'oeil.

Tu pourrais nous fournir un petit dump de la base pour tester ?

heretik25 · 21/11/2012, 08h21

Citation:

Envoyé par ericd69

salut,

group by puis utilise group_concat pour rassembler les différentes valeurs en une seule colonne sinon le group by ne changera rien et tu auras autan de ligne mais simplement ordonnées par la colonne de regroupement

heretik25 · 21/11/2012, 08h30

Citation:

Envoyé par sabotage

En fait ta requête de départ, bien que hérétique, devrait fonctionner à vue d'oeil.

Tu pourrais nous fournir un petit dump de la base pour tester ?

Elle fonctionne seulement cela me renvoi autant de lignes qu'il y a de statuts pour l'espèce.

Regarde en PJ, deux captures d'écrans

Non.jpg te montre ma requête
Oui.jpg te montre ce que j'aimerais obtenir.

rawsrc · 21/11/2012, 09h13

Salut,

MySQL n'est vraiment pas outillé pour ce genre de requête.
L'utilisation de GROUP_CONCAT est bonne sauf que les performances seront catastrophiques. Pour peu que le jeu de données soit conséquent et là le délai de réponse va s'allonger sérieusement.

Il n'y pas 36 soluces à mon avis, mis à part une procédure stockée, tu peux toujours essayer un truc de ce genre :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
SELECT 
    a.tao_id_taxon,
    IF(EXISTS(SELECT 1         FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 33),
       SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 33,
       NULL) AS statut_uicn_fc,
    IF(EXISTS(SELECT 1         FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 23),
       SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 23,
       NULL) AS statut_uicn_fr,
    IF(EXISTS(SELECT 1         FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 31),
       SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 31,
       NULL) AS statut_uicn_monde,
    IF(EXISTS(SELECT 1         FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 24),
       SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 24,
       NULL) AS statut_det_znieff,
    IF(EXISTS(SELECT 1         FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 39),
       SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 39,
       NULL) AS statut_cat_patrimoniale,
    IF(EXISTS(SELECT 1         FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 45),
       SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 45,
       NULL) AS statut_code_atlas,
    IF(EXISTS(SELECT 1         FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 46),
       SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 46,
       NULL) AS statut_sp_invasive,
    IF(EXISTS(SELECT 1         FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 47),
       SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 47,
       NULL) AS statut_sp_grand_territoire,
    IF(EXISTS(SELECT 1         FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 48),
       SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 48,
       NULL) AS statut_orgfh,
    IF(EXISTS(SELECT 1         FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 49),
       SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 49,
       NULL) AS statut_biologique,
    IF(EXISTS(SELECT 1         FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 50),
       SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 50,
       NULL) AS statut_uicn_fr_hivernant,
    IF(EXISTS(SELECT 1         FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 52),
       SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 52,
       NULL) AS statut_uicn_fr_passage
 
FROM t_taxon_observe a  
    INNER JOIN tj_taxon_a_statut f ON f.tao_id_taxon = a.tao_id_taxon
 
ORDER BY 
    tao_id_taxon

Je n'ai rien testé et c'est peut-être factorisable.
Ce que tu cherches à obtenir est proche du résultat d'un tableau croisé dynamique et gérer ça sous MySQL n'est pas chose aisée.
Sans compter en plus l'approche table EAV.
Tu dépasses les limites de ce que MySQL est capable de manipuler facilement...

N'oublie pas de poser des index...

CinePhil · 21/11/2012, 09h15

C'est normal que tu aies plusieurs lignes à cause de la première jointure interne :

Citation:

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
FROM t_taxon_observe a 
 
JOIN tj_taxon_a_statut f ON a.tao_id_taxon = f.tao_id_taxon

Pour une ligne de t_taxon_observe, tu as plusieurs lignes dans tj_taxon_a_statut.

La solution du GROUP BY sera la plus pratique.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT
	a.tao_id_taxon,
	MAX(b.sta_nom_statut) AS statut_uicn_fc,
	MAX(b1.sta_nom_statut) AS statut_uicn_fr,
	MAX(b2.sta_nom_statut) AS statut_uicn_monde,
	MAX(b3.sta_nom_statut) AS statut_det_znieff,
	MAX(b4.sta_nom_statut) AS statut_cat_patrimoniale,
	MAX(b5.sta_nom_statut) AS statut_code_atlas,
	MAX(b6.sta_nom_statut) AS statut_sp_invasive,
	MAX(b7.sta_nom_statut) AS statut_sp_grand_territoire,
	MAX(b8.sta_nom_statut) AS statut_orgfh,
	MAX(b9.sta_nom_statut) AS statut_biologique,
	MAX(b10.sta_nom_statut) AS statut_uicn_fr_hivernant,
	MAX(b11.sta_nom_statut) AS statut_uicn_fr_passage
 
FROM t_taxon_observe a
 
JOIN tj_taxon_a_statut f ON a.tao_id_taxon = f.tao_id_taxon
 
	LEFT OUTER JOIN tr_statut b ON f.sta_id_statut = b.sta_id_statut AND b.tst_id_type_statut = 33
	LEFT OUTER JOIN tr_statut b1 ON f.sta_id_statut = b1.sta_id_statut AND b1.tst_id_type_statut = 23
	LEFT OUTER JOIN tr_statut b2 ON f.sta_id_statut = b2.sta_id_statut AND b2.tst_id_type_statut = 31
	LEFT OUTER JOIN tr_statut b3 ON f.sta_id_statut = b3.sta_id_statut AND b3.tst_id_type_statut = 24
	LEFT OUTER JOIN tr_statut b4 ON f.sta_id_statut = b4.sta_id_statut AND b4.tst_id_type_statut = 39
	LEFT OUTER JOIN tr_statut b5 ON f.sta_id_statut = b5.sta_id_statut AND b5.tst_id_type_statut = 45
	LEFT OUTER JOIN tr_statut b6 ON f.sta_id_statut = b6.sta_id_statut AND b6.tst_id_type_statut = 46
	LEFT OUTER JOIN tr_statut b7 ON f.sta_id_statut = b7.sta_id_statut AND b7.tst_id_type_statut = 47
	LEFT OUTER JOIN tr_statut b8 ON f.sta_id_statut = b8.sta_id_statut AND b8.tst_id_type_statut = 48
	LEFT OUTER JOIN tr_statut b9 ON f.sta_id_statut = b9.sta_id_statut AND b9.tst_id_type_statut = 49
	LEFT OUTER JOIN tr_statut b10 ON f.sta_id_statut = b10.sta_id_statut AND b10.tst_id_type_statut = 50
	LEFT OUTER JOIN tr_statut b11 ON f.sta_id_statut = b11.sta_id_statut AND b11.tst_id_type_statut = 52
 
GROUP BY a.tao_id_taxon
ORDER BY tao_id_taxon

heretik25 · 21/11/2012, 09h40

Merci à tous pour vos réponses

Mention spéciale à Cinephil pour sa requête imparable.

20/11/2012, 23h13	#5
ericd69 Expert Confirmé Eric Dureuil Développeur informatique Inscription : avril 2011 Messages : 1 816 Détails du profil Informations personnelles : Nom : Eric Dureuil Localisation : France, Isère (Rhône Alpes) Informations professionnelles : Activité : Développeur informatique Secteur : High Tech - Multimédia et Internet Informations forums : Inscription : avril 2011 Messages : 1 816 Points : 3 119 Points : 3 119	salut, group by puis utilise group_concat pour rassembler les différentes valeurs en une seule colonne sinon le group by ne changera rien et tu auras autan de ligne mais simplement ordonnées par la colonne de regroupement __________________ soyons pensez à mettre quand votre problème est résolu ou à utiliser pour les réponses pertinentes... ne posez pas de problématique soi-disant simplifiée sur des problèmes que vous n'êtes pas capable de résoudre par respect pour ceux qui planchent dessus... sinon: et à utiliser pour insérer votre code...
	00

20/11/2012, 18h34	#3
gmarsay Membre actif Guillaume Développeur Web Inscription : décembre 2010 Messages : 108 Détails du profil Informations personnelles : Nom : Guillaume Informations professionnelles : Activité : Développeur Web Informations forums : Inscription : décembre 2010 Messages : 108 Points : 166 Points : 166	Le résultat n'est pas mieux en ajoutant un GROUP BY a.tao_id_taxon à la fin de la requête SQL ?
	00

21/11/2012, 00h04	#6
sabotage Modérateur Vincent Inscription : juillet 2005 Messages : 16 861 Détails du profil Informations personnelles : Nom : Vincent Informations forums : Inscription : juillet 2005 Messages : 16 861 Points : 21 924 Points : 21 924	En fait ta requête de départ, bien que hérétique, devrait fonctionner à vue d'oeil. Tu pourrais nous fournir un petit dump de la base pour tester ?
	00

21/11/2012, 09h40	#11
heretik25 Membre confirmé Inscription : avril 2011 Messages : 603 Détails du profil Informations personnelles : Sexe : Informations forums : Inscription : avril 2011 Messages : 603 Points : 256 Points : 256	Merci à tous pour vos réponses Mention spéciale à Cinephil pour sa requête imparable.
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email