8,8 → 8,7 |
|
|
DROP TABLE IF EXISTS `cel_references`; |
|
CREATE TABLE `cel_references` ( |
CREATE TABLE IF NOT EXISTS `cel_references` ( |
`referentiel` CHAR(5) NOT NULL, -- ENUM ("bdtfx", "bdtfx", "bdtxa", ...), |
|
-- bdtfx |
16,44 → 15,70 |
`num_nom` INT(9) NOT NULL DEFAULT '0', |
`num_nom_retenu` VARCHAR(9) DEFAULT NULL, |
|
-- bdtfx + nvjfl_v2007 |
`num_taxon` int(9) NOT NULL, |
|
-- bdtfx |
`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, |
-- mais NULL à cause de nva |
`nom_commun` VARCHAR(60) NULL, |
PRIMARY KEY (`referentiel`, `num_nom`) |
) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
|
|
/* |
way 1: theory |
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; |
*/ |
|
-- 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étermination des nom vernaculaires meilleurs et uniques: |
|
way 1: |
-- 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; |
-- 12311 |
-- # distinct= |
|
way 2: |
-- SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n INNER JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon AND n.code_langue = n2.code_langue AND n.num_statut > n2.num_statut) WHERE n.code_langue = 'fra' GROUP BY n.num_taxon; |
-- 2680 |
|
-- 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`; |
way 2": |
-- SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n LEFT JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon AND n.code_langue = n2.code_langue AND n.num_statut > n2.num_statut) WHERE n.code_langue = 'fra' AND n2.num_statut IS NOT NULL GROUP BY num_taxon; |
-- 2680, Mais problème ensuite: SELECT n.* from cel_references NATURAL JOIN nvjfl_v2007 n WHERE `nom_commun` = '' AND n.code_langue = 'fra'; |
|
|
Note: 16146 nom communs français distincts, 12312 num_taxon fr, aucun num_statut NULL en français |
*/ |
|
|
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`; |
|
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) ); |
-- ( 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) ); |
( SELECT n.num_taxon, n.nom_vernaculaire, n.num_statut as void, MAX(n.num_statut) as void2 FROM nvjfl_v2007 n WHERE n.code_langue = 'fra' GROUP BY n.num_taxon 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`; |
-- table temporaire uniquement parce qu'il manque un index-key, autrement le LEFT JOIN ci-dessous est bien trop long |
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 |
( SELECT n.num_taxon, n.nom_vernaculaire FROM nva_v2013_06 n WHERE n.code_langue = 'fra' /* DB pb */ AND n.num_taxon IS NOT NULL /* /DB pb */ 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 ) \ |
INSERT INTO `cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \ |
SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, 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; |
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); |
|
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`; |