Naudotojas:Vpovilaitis/sql

Puslapis iš Enciklopedijos Lietuvai ir Pasauliui (ELIP).
Peršokti į: navigaciją, paiešką
Forumai
vpovilaitis
Vpovilaitis/sql

Turinys

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ų.