| New file |
|---|
| 0,0 → 1,59 |
|---|
| -- TODO: |
| -- fix référentiel: suppression n° de version et uniformisation |
| -- SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM cel_obs GROUP BY nom_referentiel ORDER BY count DESC; |
| |
| -- fix date: NULL pour les dates dans le futur |
| -- SELECT courriel_utilisateur, id_observation, date_observation FROM cel_obs WHERE date_observation > NOW(); |
| |
| |
| |
| DROP TABLE IF EXISTS `cel_references`; |
| |
| CREATE TABLE `cel_references` ( |
| `referentiel` CHAR(5) NOT NULL, -- ENUM ("bdtfx", "bdtfx", "bdtxa", ...), |
| |
| -- bdtfx |
| `num_nom` INT(9) NOT NULL DEFAULT '0', |
| `num_nom_retenu` VARCHAR(9) DEFAULT NULL, |
| |
| `nom_sci` VARCHAR(500) NOT NULL, |
| `auteur` VARCHAR(100) DEFAULT NULL, |
| |
| -- cel_obs |
| -- `nom_ret_nn` DECIMAL(9,0) DEFAULT NULL COMMENT 'Numéro du nom retenu.', |
| -- `nom_ret` VARCHAR(255) DEFAULT NULL, |
| |
| -- nvjfl_v2007 (`nom_vernaculaire` text NOT NULL) |
| `nom_commun` VARCHAR(60) NOT NULL, |
| PRIMARY KEY (`referentiel`, `num_nom`) |
| ) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
| |
| |
| |
| -- INSERT INTO `cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `nom_sci`, `auteur`, `nom_commun`) \ |
| -- SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, n.nom_vernaculaire, MAX(n.num_statut) FROM bdtfx_v1_01 b LEFT JOIN nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon AND n.code_langue = 'fra' ) GROUP BY b.num_nom \ |
| -- UNION \ |
| -- SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, n.nom_vernaculaire, NULL FROM bdtxa_v1_00 b LEFT JOIN nva_v2013_06 n ON (b.num_tax = n.num_taxon AND n.code_langue = 'fra' ) GROUP BY b.num_nom; |
| -- SELECT "isfan", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, NULL FROM isfan_v2013 b; |
| |
| |
| |
| |
| -- déterminé à partir de |
| -- SELECT n.num_taxon, n.nom_vernaculaire, n.num_statut, n2.num_statut FROM nvjfl_v2007 n LEFT JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon) WHERE n.num_taxon < 32 AND n.code_langue = 'fra' GROUP BY n.num_taxon, n.num_statut; -- HAVING n.num_statut = MAX(n2.num_statut) LIMIT 100; |
| DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`; |
| CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \ |
| ( SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n WHERE n.code_langue = 'fra' GROUP BY n.num_taxon, n.num_statut HAVING n.num_statut = MAX(n.num_statut) ); |
| |
| |
| -- uniquement pour la primary key, autrement le LEFT JOIN suivant est trop long |
| DROP TEMPORARY TABLE IF EXISTS `T_nva_v2013_06`; |
| CREATE TEMPORARY TABLE T_nva_v2013_06 ( INDEX(`num_taxon`) ) AS \ |
| ( SELECT n.num_taxon, n.nom_vernaculaire FROM nva_v2013_06 n WHERE n.code_langue = 'fra' GROUP BY n.num_nom); -- aggrégat arbitraire car pas de num_statut |
| |
| |
| INSERT INTO `cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `nom_sci`, `auteur`, `nom_commun`) \ |
| SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, n.nom_vernaculaire FROM bdtfx_v1_01 b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \ |
| UNION \ |
| SELECT "bdtxa", b.num_nom, b.num_nom_retenu, 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); |
| DROP TEMPORARY TABLE T_nvjfl_v2007; |