Requête statuts des espèces

**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 ?

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

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

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

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 :

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.

Requête statuts des espèces [MySQL]

PHP & Base de données

Discussions similaires

Partager

Partager