| 1,4 → 1,4 |
|---|
| 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, 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, |
| 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, |
| CASE WHEN b.rang > 290 THEN |
| 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),'","', |
| 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),'","', |
| 12,7 → 12,7 |
|---|
| 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 |
| WHERE (b.num_nom_retenu = c.num_nom_retenu) AND b.num_nom = b.num_nom_retenu |
| UNION |
| 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, '' 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, |
| 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, |
| CASE WHEN b.rang > 290 THEN |
| 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),'","', |
| 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),'","', |