Naudotojas:Vpovilaitis/sql
Forumai → |
|
→ Vpovilaitis/sql |
Vpovilaitis/sql
SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.string,'') aut, 'antšeimis' rank, ttp.name par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.taxon_id, tt.`name` FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join author_string tas ON ( td.author_string_id = tas.id ) left join _taxon_tree ttp on (tt.parent_id=ttp.taxon_id) WHERE tt.`parent_id` = 13021881 ORDER BY 1 ASC ***** geras būrys - klasei ***** SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.string,'') aut, ifnull(tas.met,'') met, 'būrys' rank, ifnull(pc.c_name,ttp.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, tp.name par2, Case when tt.`name`='Not assigned' then Case when ttp.`name`='Superfamily unassigned' then tp.name else ttp.`name` end else tt.`name` end name FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join aut_met tas ON ( td.author_string_id = tas.id ) left join _taxon_tree ttp on (tt.parent_id=ttp.taxon_id) left join base.x_col2013taxon pc on (ttp.taxon_id=pc.c_taxon_id) left join _taxon_tree tp on (t.parent_id=tp.taxon_id) WHERE tt.`parent_id` = 13028338 ORDER BY ttp.name ASC, 1 ASC ****** gera šeima - klasei ***** SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.string,'') aut, ifnull(tas.met,'') met, 'šeima' rank, ifnull(pc.c_name,ttp.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, tp.name par2, Case when tt.`name`='Not assigned' then Case when ttp.`name`='Superfamily unassigned' then tp.name else ttp.`name` end else tt.`name` end name FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join aut_met tas ON ( td.author_string_id = tas.id ) left join _taxon_tree ttp on (tt.parent_id=ttp.taxon_id) left join base.x_col2013taxon pc on (ttp.taxon_id=pc.c_taxon_id) left join _taxon_tree tp on (t.parent_id=tp.taxon_id) WHERE t.`parent_id` = 13028338 ORDER BY 1 ASC ***** gera gentis - klasei ***** SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.string,'') aut, ifnull(tas.met,'') met, 'gentis' rank, ifnull(pc.c_name,ttp.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, tp.name par2, t0p.name par3, Case when tt.`name`='Not assigned' then Case when ttp.`name`='Superfamily unassigned' then tp.name else ttp.`name` end else tt.`name` end name FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join `_taxon_tree` t0 on (t0.taxon_id=t.parent_id) left join `_taxon_tree` t1 on (t1.taxon_id=t0.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join aut_met tas ON ( td.author_string_id = tas.id ) left join _taxon_tree ttp on (tt.parent_id=ttp.taxon_id) left join base.x_col2013taxon pc on (ttp.taxon_id=pc.c_taxon_id) left join _taxon_tree tp on (t.parent_id=tp.taxon_id) left join _taxon_tree t0p on (t0.parent_id=t0p.taxon_id) WHERE t0.`parent_id` = 13028338 ORDER BY 1 ASC ***** gera rūšis - klasei ***** SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.aut,'') aut, ifnull(tas.met,'') met, 'gentis' rank, ifnull(pc.c_name,ttp.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, tp.name par2, t0p.name par3, t1p.name par4, Case when tt.`name`='Not assigned' then Case when ttp.`name`='Superfamily unassigned' then tp.name else ttp.`name` end else tt.`name` end name FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join `_taxon_tree` t0 on (t0.taxon_id=t.parent_id) left join `_taxon_tree` t1 on (t1.taxon_id=t0.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join aut_met tas ON ( td.author_string_id = tas.id ) left join _taxon_tree ttp on (tt.parent_id=ttp.taxon_id) left join base.x_col2013taxon pc on (ttp.taxon_id=pc.c_taxon_id) left join _taxon_tree tp on (t.parent_id=tp.taxon_id) left join _taxon_tree t0p on (t0.parent_id=t0p.taxon_id) left join _taxon_tree t1p on (t1.parent_id=t1p.taxon_id) WHERE t1.`parent_id` = 13028338 ORDER BY 1 ASC ***** SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.string,'') aut, 'šeima' rank, ifnull(pc.c_name,ttp.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, tp.name par2, Case when tt.`name`='Not assigned' then Case when ttp.`name`='Superfamily unassigned' then tp.name else ttp.`name` end else tt.`name` end name FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join author_string tas ON ( td.author_string_id = tas.id ) left join _taxon_tree ttp on (tt.parent_id=ttp.taxon_id) left join base.x_col2013taxon pc on (ttp.taxon_id=pc.c_taxon_id) left join _taxon_tree tp on (t.parent_id=tp.taxon_id) WHERE t.`parent_id` = 13021881 ORDER BY ttp.name ASC, 1 ASC SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.aut,'') aut, ifnull(tas.met,'') met, 'rūšis' rank, ifnull(pc.c_name,ttp.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, tp.name par2, t0p.name par3, Case when tt.`name`='Not assigned' then Case when ttp.`name`='Superfamily unassigned' then tp.name else ttp.`name` end else tt.`name` end name FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join `_taxon_tree` t0 on (t0.taxon_id=t.parent_id) left join `_taxon_tree` t1 on (t1.taxon_id=t0.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join aut_met tas ON ( td.author_string_id = tas.id ) left join _taxon_tree ttp on (tt.parent_id=ttp.taxon_id) left join base.x_col2013taxon pc on (ttp.taxon_id=pc.c_taxon_id) left join _taxon_tree tp on (t.parent_id=tp.taxon_id) left join _taxon_tree t0p on (t0.parent_id=t0p.taxon_id) WHERE t1.`parent_id` = 13021881 ORDER BY 1 ASC SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.aut,'') aut, ifnull(tas.met,'') met, 'rūšis' rank, ifnull(pc.c_name,t.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, t0.name par2, t1.name par3, Case when tt.`name`='Not assigned' then Case when t.`name`='Superfamily unassigned' then t0.name else t.`name` end else tt.`name` end name FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join `_taxon_tree` t0 on (t0.taxon_id=t.parent_id) left join `_taxon_tree` t1 on (t1.taxon_id=t0.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join aut_met tas ON ( td.author_string_id = tas.id ) left join base.x_col2013taxon pc on (t.taxon_id=pc.c_taxon_id) WHERE t1.`parent_id` = 13021881 ORDER BY 1 ASC **** Geras ***** SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.aut,'') aut, ifnull(tas.met,'') met, 'rūšis' rank, ifnull(pc.c_name,t.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, ifnull(pc0.c_name,t0.`name`) par2, t1.name par3, Case when tt.`name`='Not assigned' then Case when t.`name`='Superfamily unassigned' then t0.name else t.`name` end else tt.`name` end name FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join `_taxon_tree` t0 on (t0.taxon_id=t.parent_id) left join `_taxon_tree` t1 on (t1.taxon_id=t0.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join aut_met tas ON ( td.author_string_id = tas.id ) left join base.x_col2013taxon pc on (t.taxon_id=pc.c_taxon_id) left join base.x_col2013taxon pc0 on (t0.taxon_id=pc0.c_taxon_id) WHERE t1.`parent_id` = 13021881 ORDER BY 1 ASC SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.aut,'') aut, ifnull(tas.met,'') met, 'porūšis' rank, ifnull(pc.c_name,ttp.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, tp.name par2, t1.name par3, t2.name par4, Case when tt.`name`='Not assigned' then Case when ttp.`name`='Superfamily unassigned' then tp.name else ttp.`name` end else tt.`name` end name FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join `_taxon_tree` t0 on (t0.taxon_id=t.parent_id) left join `_taxon_tree` t1 on (t1.taxon_id=t0.parent_id) left join `_taxon_tree` t2 on (t2.taxon_id=t1.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join aut_met tas ON ( td.author_string_id = tas.id ) left join _taxon_tree ttp on (tt.parent_id=ttp.taxon_id) left join base.x_col2013taxon pc on (ttp.taxon_id=pc.c_taxon_id) left join _taxon_tree tp on (t.parent_id=tp.taxon_id) left join _taxon_tree t0p on (t1.parent_id=t0p.taxon_id) left join _taxon_tree t1p on (t2.parent_id=t1p.taxon_id) WHERE t2.`parent_id` = 13021881 ORDER BY 1 ASC ***** geras ****** SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.aut,'') aut, ifnull(tas.met,'') met, 'porūšis' rank, ifnull(pc.c_name,t.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, ifnull(pc0.c_name,t0.`name`) par2, ifnull(pc1.c_name,t1.`name`) par3, t2.name par4, Case when tt.`name`='Not assigned' then Case when t.`name`='Superfamily unassigned' then t0.name else t.`name` end else tt.`name` end name FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join `_taxon_tree` t0 on (t0.taxon_id=t.parent_id) left join `_taxon_tree` t1 on (t1.taxon_id=t0.parent_id) left join `_taxon_tree` t2 on (t2.taxon_id=t1.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join aut_met tas ON ( td.author_string_id = tas.id ) left join base.x_col2013taxon pc on (t.taxon_id=pc.c_taxon_id) left join base.x_col2013taxon pc0 on (t0.taxon_id=pc0.c_taxon_id) left join base.x_col2013taxon pc1 on (t1.taxon_id=pc1.c_taxon_id) WHERE t2.`parent_id` = 13021881 ORDER BY 1 ASC SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.aut,'') aut, ifnull(tas.met,'') met, 'rūšis' rank, ifnull(pc.c_name,ttp.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, tp.name par2, t0p.name par3, Case when tt.`name`='Not assigned' then Case when ttp.`name`='Superfamily unassigned' then tp.name else ttp.`name` end else tt.`name` end name, tx.original_id, sd.*, uri.* FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join `_taxon_tree` t0 on (t0.taxon_id=t.parent_id) left join `_taxon_tree` t1 on (t1.taxon_id=t0.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join aut_met tas ON ( td.author_string_id = tas.id ) left join _taxon_tree ttp on (tt.parent_id=ttp.taxon_id) left join base.x_col2013taxon pc on (ttp.taxon_id=pc.c_taxon_id) left join _taxon_tree tp on (t.parent_id=tp.taxon_id) left join _taxon_tree t0p on (t0.parent_id=t0p.taxon_id) left join taxon tx on (tt.taxon_id=tx.id) left join source_database sd on (tx.source_database_id=sd.id) left join uri_to_source_database usd on (sd.id=usd.source_database_id) left join uri on (uri.id=usd.uri_id) WHERE t1.`parent_id` = 13021881 ORDER BY 1 ASC SELECT ifnull(c.c_name,tt.`name`) nam, ifnull(tas.aut,'') aut, ifnull(tas.met,'') met, 'gentis' rank, ifnull(pc.c_name,ttp.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, tp.name par2, t0p.name par3, Case when tt.`name`='Not assigned' then Case when ttp.`name`='Superfamily unassigned' then tp.name else ttp.`name` end else tt.`name` end name FROM `_taxon_tree` tt left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join `_taxon_tree` t0 on (t0.taxon_id=t.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join aut_met tas ON ( td.author_string_id = tas.id ) left join _taxon_tree ttp on (tt.parent_id=ttp.taxon_id) left join base.x_col2013taxon pc on (ttp.taxon_id=pc.c_taxon_id) left join _taxon_tree tp on (t.parent_id=tp.taxon_id) left join _taxon_tree t0p on (t0.parent_id=t0p.taxon_id) WHERE t0.`parent_id` = 13021881 AND tt.`number_of_children`>50 ORDER BY 1 ASC select * from ( SELECT COUNT(*) AS sk, `taxon_id` FROM `synonym` GROUP BY `taxon_id` ORDER BY `taxon_id` ) a left join _taxon_tree tt on (a.taxon_id=tt.taxon_id) where a.sk>50 and tt.name>='A' order by tt.name SELECT s.*, concat_ws(' ',(select snee1.name_element from synonym_name_element sne1 left join scientific_name_element snee1 on (sne1.scientific_name_element_id=snee1.id) where sne1.synonym_id=s.id and sne1.taxonomic_rank_id=20), (select snee2.name_element from synonym_name_element sne2 left join scientific_name_element snee2 on (sne2.scientific_name_element_id=snee2.id) where sne2.synonym_id=s.id and sne2.taxonomic_rank_id=83), (select tr3.marker_displayed from synonym_name_element sne3 left join scientific_name_element snee3 on (sne3.scientific_name_element_id=snee3.id) left join taxonomic_rank tr3 on (sne3.taxonomic_rank_id=tr3.id) where sne3.synonym_id=s.id and sne3.taxonomic_rank_id in (19, 88, 104, 129,130,131,132,133,134,135,136,137)), (select snee4.name_element from synonym_name_element sne4 left join scientific_name_element snee4 on (sne4.scientific_name_element_id=snee4.id) where sne4.synonym_id=s.id and sne4.taxonomic_rank_id in (19, 88, 104, 129,130,131,132,133,134,135,136,137))) nam, sas.* FROM `synonym` s left join aut_met sas on (sas.id=s.author_string_id) WHERE s.`taxon_id` = 11484547 SELECT s.id, concat_ws(' ',(select concat(ucase(left(snee1.name_element,1)), mid(snee1.name_element,2,length(snee1.name_element))) from synonym_name_element sne1 left join scientific_name_element snee1 on (sne1.scientific_name_element_id=snee1.id) where sne1.synonym_id=s.id and sne1.taxonomic_rank_id=20), (select snee2.name_element from synonym_name_element sne2 left join scientific_name_element snee2 on (sne2.scientific_name_element_id=snee2.id) where sne2.synonym_id=s.id and sne2.taxonomic_rank_id=83), (select tr3.marker_displayed from synonym_name_element sne3 left join scientific_name_element snee3 on (sne3.scientific_name_element_id=snee3.id) left join taxonomic_rank tr3 on (sne3.taxonomic_rank_id=tr3.id) where sne3.synonym_id=s.id and sne3.taxonomic_rank_id in (19, 88, 104, 129,130,131,132,133,134,135,136,137)), (select snee4.name_element from synonym_name_element sne4 left join scientific_name_element snee4 on (sne4.scientific_name_element_id=snee4.id) where sne4.synonym_id=s.id and sne4.taxonomic_rank_id in (19, 88, 104, 129,130,131,132,133,134,135,136,137))) name FROM `synonym` s create table synonym_name as SELECT s.id, concat_ws(' ',(select concat(ucase(left(snee1.name_element,1)), mid(snee1.name_element,2,length(snee1.name_element))) from synonym_name_element sne1 left join scientific_name_element snee1 on (sne1.scientific_name_element_id=snee1.id) where sne1.synonym_id=s.id and sne1.taxonomic_rank_id=20), (select snee2.name_element from synonym_name_element sne2 left join scientific_name_element snee2 on (sne2.scientific_name_element_id=snee2.id) where sne2.synonym_id=s.id and sne2.taxonomic_rank_id=83), (select tr3.marker_displayed from synonym_name_element sne3 left join scientific_name_element snee3 on (sne3.scientific_name_element_id=snee3.id) left join taxonomic_rank tr3 on (sne3.taxonomic_rank_id=tr3.id) where sne3.synonym_id=s.id and sne3.taxonomic_rank_id in (19, 88, 104, 129,130,131,132,133,134,135,136,137)), (select snee4.name_element from synonym_name_element sne4 left join scientific_name_element snee4 on (sne4.scientific_name_element_id=snee4.id) where sne4.synonym_id=s.id and sne4.taxonomic_rank_id in (19, 88, 104, 129,130,131,132,133,134,135,136,137))) name, (select concat(ucase(left(snee5.name_element,1)), mid(snee5.name_element,2,length(snee5.name_element))) from synonym_name_element sne5 left join scientific_name_element snee5 on (sne5.scientific_name_element_id=snee5.id) where sne5.synonym_id=s.id and sne5.taxonomic_rank_id=20) genus FROM `synonym` s SELECT s.*, sn.name, sas.string, sas.aut, sas.met FROM `synonym` s left join synonym_name sn on (s.id=sn.id) left join aut_met sas on (sas.id=s.author_string_id) WHERE s.`taxon_id` = 11484547 SELECT a.*, trim(trim(BOTH ',' FROM trim(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(a.string, '''', ''), '“', ''), '"', ''), '” ', ', '), ') ', ', '), '] ', ', '), ')', ''), '(', ''), ']', ''), '[', ''), ' in ', ', '), ' and ', ', '), ' et ', ', '), ' ex ', ', '), ' & ', ', ')))) aut_met FROM `author_string` a SELECT sn.name, sas.string, sas.aut, sas.met, ifnull(c.c_name,tt.`name`) nam, ifnull(tas.aut,'') aut, ifnull(tas.met,'') met, 'rūšis' rank, ifnull(pc.c_name,t.`name`) par, round((tt.`number_of_children`+0.5)/2,0)*2 sk, tt.`number_of_children` skt, tt.taxon_id, ifnull(pc0.c_name,t0.`name`) par2, t1.name par3, Case when tt.`name`='Not assigned' then Case when t.`name`='Superfamily unassigned' then t0.name else t.`name` end else tt.`name` end name FROM `_taxon_tree` tt left join synonym s on (s.taxon_id=tt.taxon_id) left join synonym_name sn on (s.id=sn.id) left join aut_met sas on (sas.id=s.author_string_id) left join base.x_col2013taxon c on (tt.taxon_id=c.c_taxon_id) left join `_taxon_tree` t on (t.taxon_id=tt.parent_id) left join `_taxon_tree` t0 on (t0.taxon_id=t.parent_id) left join `_taxon_tree` t1 on (t1.taxon_id=t0.parent_id) left join taxon_detail td on (td.taxon_id=tt.taxon_id) left join aut_met tas ON ( td.author_string_id = tas.id ) left join base.x_col2013taxon pc on (t.taxon_id=pc.c_taxon_id) left join base.x_col2013taxon pc0 on (t0.taxon_id=pc0.c_taxon_id) WHERE t1.`parent_id` = 13021881 and s.taxon_id is not null ORDER BY 5 ASC, 1 ASC
Šiuo metu šiame puslapyje nėra žinučių.