| Line 64... |
Line 64... |
| 64 |
INDEX (`referentiel`(5)),
|
64 |
INDEX (`referentiel`(5)),
|
| 65 |
INDEX (`num_nom`),
|
65 |
INDEX (`num_nom`),
|
| 66 |
INDEX (`num_taxon`)
|
66 |
INDEX (`num_taxon`)
|
| 67 |
) ENGINE=MyISAM \
|
67 |
) ENGINE=MyISAM \
|
| 68 |
DEFAULT CHARSET=utf8 \
|
68 |
DEFAULT CHARSET=utf8 \
|
| 69 |
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';
|
69 |
COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de TABLEBDTFX, TABLEBDTXA et nvjfl_v2007';
|
| Line 70... |
Line 70... |
| 70 |
|
70 |
|
| 71 |
-- tables temporaires
|
71 |
-- tables temporaires
|
| Line 72... |
Line 72... |
| 72 |
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;
|
72 |
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;
|
| Line 96... |
Line 96... |
| 96 |
`ve_reaction_sol`, `ve_nutriments_sol`, `ve_salinite`, `ve_texture_sol`, `ve_mat_org_sol`, `syntaxon`) \
|
96 |
`ve_reaction_sol`, `ve_nutriments_sol`, `ve_salinite`, `ve_texture_sol`, `ve_mat_org_sol`, `syntaxon`) \
|
| Line 97... |
Line 97... |
| 97 |
|
97 |
|
| 98 |
SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire, \
|
98 |
SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire, \
|
| 99 |
bf.catminat_code, bf.ve_lumiere, bf.ve_temperature, bf.ve_continentalite, bf.ve_humidite_atmos, bf.ve_humidite_edaph, \
|
99 |
bf.catminat_code, bf.ve_lumiere, bf.ve_temperature, bf.ve_continentalite, bf.ve_humidite_atmos, bf.ve_humidite_edaph, \
|
| 100 |
bf.ve_reaction_sol, bf.ve_nutriments_sol, bf.ve_salinite, bf.ve_texture_sol, bf.ve_mat_org_sol, bf.syntaxon
|
100 |
bf.ve_reaction_sol, bf.ve_nutriments_sol, bf.ve_salinite, bf.ve_texture_sol, bf.ve_mat_org_sol, bf.syntaxon
|
| 101 |
FROM `BASESOURCE`.`bdtfx_v1_01` b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \
|
101 |
FROM `BASESOURCE`.`TABLEBDTFX` b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \
|
| Line 102... |
Line 102... |
| 102 |
LEFT JOIN T_basevegflor bf ON (b.num_taxonomique = bf.num_taxon AND b.num_nom = bf.num_nomen);
|
102 |
LEFT JOIN T_basevegflor bf ON (b.num_taxonomique = bf.num_taxon AND b.num_nom = bf.num_nomen);
|
| 103 |
|
103 |
|
| 104 |
|
104 |
|
| Line 105... |
Line 105... |
| 105 |
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_tax IS NOT NULL AND b.num_tax != '')
|
105 |
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_tax IS NOT NULL AND b.num_tax != '')
|
| 106 |
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
|
106 |
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
|
| Line 114... |
Line 114... |
| 114 |
|
114 |
|
| Line 115... |
Line 115... |
| 115 |
|
115 |
|
| 116 |
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;
|
116 |
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;
|
| 117 |
|
117 |
|
| 118 |
SELECT SUM(theorie.a) AS théorie, pratique.a AS total FROM \
|
118 |
SELECT SUM(theorie.a) AS théorie, pratique.a AS total FROM \
|
| 119 |
(SELECT COUNT(1) AS a FROM `BASESOURCE`.`bdtfx_v1_01` UNION ALL \
|
119 |
(SELECT COUNT(1) AS a FROM `BASESOURCE`.`TABLEBDTFX` UNION ALL \
|
| 120 |
SELECT COUNT(1) AS a FROM `BASESOURCE`.`bdtxa_v1_00` UNION ALL \
|
120 |
SELECT COUNT(1) AS a FROM `BASESOURCE`.`TABLEBDTXA` UNION ALL \
|
| 121 |
SELECT COUNT(1) AS a FROM `BASESOURCE`.`isfan_v2013` \
|
121 |
SELECT COUNT(1) AS a FROM `BASESOURCE`.`isfan_v2013` \
|