Subversion Repositories Applications.referentiel

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
382 mathias 1 
SELECT b.num_nom AS apd_num_nom, b.num_nom_retenu AS apd_num_nom_retenu, b.num_tax_sup AS apd_num_tax_sup, b.rang AS apd_rang, b.nom_sci AS apd_nom_sci, b.auteur AS apd_auteur, b.annee AS apd_annee, b.`biblio_origine` AS apd_biblio, apd_synonymes, b.`nom_supra_generique` AS apd_nom_supra_generique, b.`genre` AS apd_genre, b.`epithete_sp` AS apd_epithete_sp, b.`type_epithete` AS apd_type_epithete, b.`epithete_infra_sp` AS apd_epithete_infra_sp, b.`cultivar` AS apd_cultivar, b.`cultivar_groupe` AS apd_cultivar_groupe, concat('https://beta.tela-botanica.org/test/apd-nn-', b.`num_nom_retenu`) as apd_url,
380 mathias 2 
CASE WHEN b.rang > 290 THEN
3 
concat('["',substr(`genre`, 1, 3),substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), ' ', substr(`epithete_infra_sp`, 1, 1),'","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 1),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 1),'","',
4 
substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), ' ', substr(`epithete_infra_sp`, 1, 2),'","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 2),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 2),'","',
5 
substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), ' ', substr(`epithete_infra_sp`, 1, 3),'","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 3),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 3),'"]')
6 
WHEN b.rang = 220 THEN
7 
concat('["',substr(`genre`, 1, 1), '","', substr(`genre`, 1, 2), '","', substr(`genre`, 1, 3), '","', substr(`genre`, 1, 4), '"]')
8 
WHEN b.rang < 220 THEN
9 
concat('["',substr(`nom_supra_generique`, 1, 1), '","', substr(`nom_supra_generique`, 1, 2), '","', substr(`nom_supra_generique`, 1, 3), '","', substr(`nom_supra_generique`, 1, 4), '"]')
10 
ELSE concat('["',substr(`genre`, 1, 3),substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),'"]')
11 
END as apd_shortcuts
12 
FROM `apd` b, (SELECT num_nom_retenu, concat('[',group_concat('"',nom_sci,' ',auteur,'"'),']') AS apd_synonymes FROM apd WHERE num_nom != num_nom_retenu AND num_nom_retenu != 0 GROUP BY `num_nom_retenu`) as c
13 
WHERE (b.num_nom_retenu = c.num_nom_retenu) AND b.num_nom = b.num_nom_retenu
14 
UNION
382 mathias 15 
SELECT b.num_nom AS apd_num_nom, b.num_nom_retenu AS apd_num_nom_retenu, b.num_tax_sup AS apd_num_tax_sup, b.rang AS apd_rang, b.nom_sci AS apd_nom_sci, b.auteur AS apd_auteur, b.annee AS apd_annee, b.`biblio_origine` AS apd_biblio, '' as apd_synonymes, b.`nom_supra_generique` AS apd_nom_supra_generique, b.`genre` AS apd_genre, b.`epithete_sp` AS apd_epithete_sp, b.`type_epithete` AS apd_type_epithete, b.`epithete_infra_sp` AS apd_epithete_infra_sp, b.`cultivar` AS apd_cultivar, b.`cultivar_groupe` AS apd_cultivar_groupe, concat('https://beta.tela-botanica.org/test/apd-nn-', b.`num_nom_retenu`) as apd_url,
380 mathias 16 
CASE WHEN b.rang > 290 THEN
17 
concat('["',substr(`genre`, 1, 3),substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), ' ', substr(`epithete_infra_sp`, 1, 1),'","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 1),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 1),'","',
18 
substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), ' ', substr(`epithete_infra_sp`, 1, 2),'","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 2),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 2),'","',
19 
substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), ' ', substr(`epithete_infra_sp`, 1, 3),'","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 3),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2),' ', substr(`epithete_infra_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),' ', substr(`epithete_infra_sp`, 1, 3),'"]')
20 
WHEN b.rang = 220 THEN
21 
concat('["',substr(`genre`, 1, 1), '","', substr(`genre`, 1, 2), '","', substr(`genre`, 1, 3), '","', substr(`genre`, 1, 4), '"]')
22 
WHEN b.rang < 220 THEN
23 
concat('["',substr(`nom_supra_generique`, 1, 1), '","', substr(`nom_supra_generique`, 1, 2), '","', substr(`nom_supra_generique`, 1, 3), '","', substr(`nom_supra_generique`, 1, 4), '"]')
24 
ELSE concat('["',substr(`genre`, 1, 3),substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 1), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 2), '","', substr(`genre`, 1, 2),' ',substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 1),'","',  substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 2), '","', substr(`genre`, 1, 3),' ',substr(`epithete_sp`, 1, 3), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 1), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 2), '","', substr(`genre`, 1, 4),' ',substr(`epithete_sp`, 1, 3),'"]')
25 
END as apd_shortcuts
26 
FROM `apd` b WHERE b.num_nom_retenu not in (SELECT distinct num_nom_retenu FROM apd WHERE num_nom != num_nom_retenu and b.num_nom_retenu != 0) AND (b.num_nom = b.num_nom_retenu OR b.num_nom_retenu = "0")