Changes between Version 7 and Version 8 of Recette SQL SommeEffectifs

Timestamp:

Feb 14, 2010 10:35:25 PM (15 years ago)

Author:

cedric

Comment:

--

Recette SQL SommeEffectifs

@@ -39 +39 @@
 order by Taxon
 }}}
+== Comparaison des effectifs de taxons de deux passes ==
+note : j'ai que des anguilles dans le dc6 et le symétrique ne marche pas (il me manque des noms de taxons)
+{{{
+Select taxon, effectifdc5,effectifdc6 FROM(
+        select tax_nom_latin,
+         sum (lot_effectif) as effectifdc6 
+         FROM iav.vue_lot_ope_car v1
+         WHERE ope_dic_identifiant=6
+         AND ope_date_debut > '2007-01-01 00:00:00'
+         AND ope_date_debut < '2008-01-01 00:00:00'
+         GROUP BY tax_nom_latin) as sub1
+FULL OUTER JOIN
+         (select tax_nom_latin as taxon,
+         sum(lot_effectif)  as effectifdc5
+         FROM iav.vue_lot_ope_car v2 
+         WHERE ope_dic_identifiant=5
+         AND ope_date_debut > '2007-01-01 00:00:00'
+         AND ope_date_debut < '2008-01-01 00:00:00'
+         GROUP BY tax_nom_latin) as sub2
+On sub2.taxon = sub1.tax_nom_latin;
+}}}
+||Alosa||1112||||
+||Anguilla anguilla||28||19652||
+||Ctenopharyngodon idellus||8||||
+||Cyprinus carpio||6||||
+||Petromyzon marinus||115||||
+||Salmo||1||||
+||Silurus glanis||3||||
+||Tinca tinca||1||||