66,7 → 66,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_01, nvjfl_v2007 et bdtxa_v1_00'; |
COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de TABLEBDTFX, TABLEBDTXA et nvjfl_v2007'; |
|
-- tables temporaires |
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`; |
98,13 → 98,13 |
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 `BASESOURCE`.`bdtfx_v1_01` b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \ |
FROM `BASESOURCE`.`TABLEBDTFX` 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 `BASEEDIT`.`cel_references` (`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 `BASESOURCE`.`bdtxa_v1_00` b LEFT JOIN T_nva_v2013_06 n ON (b.num_tax = n.num_taxon); |
SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.num_tax, b.nom_sci, b.auteur, n.nom_vernaculaire FROM `BASESOURCE`.`TABLEBDTXA` b LEFT JOIN T_nva_v2013_06 n ON (b.num_tax = n.num_taxon); |
|
|
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`) \ |
116,8 → 116,8 |
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`; |
|
SELECT SUM(theorie.a) AS théorie, pratique.a AS total FROM \ |
(SELECT COUNT(1) AS a FROM `BASESOURCE`.`bdtfx_v1_01` UNION ALL \ |
SELECT COUNT(1) AS a FROM `BASESOURCE`.`bdtxa_v1_00` UNION ALL \ |
(SELECT COUNT(1) AS a FROM `BASESOURCE`.`TABLEBDTFX` UNION ALL \ |
SELECT COUNT(1) AS a FROM `BASESOURCE`.`TABLEBDTXA` UNION ALL \ |
SELECT COUNT(1) AS a FROM `BASESOURCE`.`isfan_v2013` \ |
) AS theorie, \ |
(SELECT COUNT(1) AS a FROM tb_cel.cel_references) AS pratique; |