| 76,7 → 76,7 |
|---|
| INDEX (`num_taxon`) |
| ) ENGINE=MyISAM \ |
| DEFAULT CHARSET=utf8 \ |
| COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de bdtfx_v1_02, nvjfl_v2007 et bdtxa_v1_00'; |
| COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de bdtfx_v1_01, nvjfl_v2007 et bdtxa_v1_00'; |
| |
| -- tables temporaires |
| DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`; |
| 100,6 → 100,7 |
|---|
| |
| |
| -- INSERTIONS |
| -- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_taxonomique != '') |
| INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`, \ |
| `catminat_code`, `ve_lumiere`, `ve_temperature`, `ve_continentalite`, `ve_humidite_atmos`, `ve_humidite_edaph`, \ |
| `ve_reaction_sol`, `ve_nutriments_sol`, `ve_salinite`, `ve_texture_sol`, `ve_mat_org_sol`, `syntaxon`) \ |
| 107,13 → 108,28 |
|---|
| SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire, \ |
| bf.catminat_code, bf.ve_lumiere, bf.ve_temperature, bf.ve_continentalite, bf.ve_humidite_atmos, bf.ve_humidite_edaph, \ |
| bf.ve_reaction_sol, bf.ve_nutriments_sol, bf.ve_salinite, bf.ve_texture_sol, bf.ve_mat_org_sol, bf.syntaxon |
| FROM bdtfx_v1_02 b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \ |
| LEFT JOIN T_basevegflor bf ON (b.num_taxonomique = bf.num_taxon AND b.num_nom = bf.num_nomen) WHERE b.num_taxonomique != ''; |
| FROM bdtfx_v1_01 b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \ |
| LEFT JOIN T_basevegflor bf ON (b.num_taxonomique = bf.num_taxon AND b.num_nom = bf.num_nomen); |
| |
| |
| -- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_tax IS NOT NULL AND b.num_tax != '') |
| INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \ |
| SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.num_tax, b.nom_sci, b.auteur, n.nom_vernaculaire FROM bdtxa_v1_00 b LEFT JOIN T_nva_v2013_06 n ON (b.num_tax = n.num_taxon) \ |
| WHERE b.num_tax IS NULL AND b.num_tax != ''; |
| SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.num_tax, b.nom_sci, b.auteur, n.nom_vernaculaire FROM bdtxa_v1_00 b LEFT JOIN T_nva_v2013_06 n ON (b.num_tax = n.num_taxon); |
| |
| |
| INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`) \ |
| SELECT "isfan", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur FROM isfan_v2013 b; |
| |
| |
| |
| |
| DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`; |
| |
| SELECT count(1), "résultat théorique: 104422" FROM @dst; |
| SELECT SUM(theorie.a) AS théorie, pratique.a AS total FROM \ |
| (SELECT COUNT(1) AS a FROM tb_eflore.bdtfx_v1_01 UNION ALL \ |
| SELECT COUNT(1) AS a FROM tb_eflore.bdtxa_v1_00 UNION ALL \ |
| SELECT COUNT(1) AS a FROM tb_eflore.isfan_v2013 \ |
| ) AS theorie, \ |
| (SELECT COUNT(1) AS a FROM tb_cel.cel_references) AS pratique; |
| -- bdtfx+bdtxa+isfan: 141181 (2013/07/23) |
| |