Requête statuts des espèces

Version imprimable

2 pièce(s) jointe(s)

Bonsoir,

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

Code:

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

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:

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é.

20/11/2012, 18h34
gmarsay

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

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:

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.

20/11/2012, 23h13
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
21/11/2012, 00h04
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 ?
21/11/2012, 08h21
heretik25

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

Salut,

Je ne veux pas une seule colonne mais bien autant de colonnes que de statuts le tout regroupé sur une seule ligne.

Soit :

Nom espèce | statut 1 | statut 2| statut 3 |
Espece 1 | aa |null |dd|ff

A noter que les espèces n'ont pas l'ensemble des statuts
21/11/2012, 08h30
heretik25

2 pièce(s) jointe(s)

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.

:ccool:

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:

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...

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

Citation:

Code:

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:

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

21/11/2012, 09h40
heretik25

Merci à tous pour vos réponses :ccool:

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