ITIS生物分类数据库改成类似excle格式sql

上次给大家介绍了ITIS数据库的常用表，由于数据库的存储方式和日常习惯不太一样，因此计划重新组织一下。

-- 等级表

select replace(hierarchy_string,'-',','),a.* from hierarchy a where tsn=769899;   

分类关系是以下面的形式组织的

202423-914154-914155-914158-82696-563886-99208-100500-563890-914213-152741-152864-709253-154344-154310-768180-768205-768289-769899

-- 1、将物种分类拆开，有两个字段：物种ID，物种所在的各级分类的ID

create table bio_hierarchy as

  SELECT a.tsn,b.help_topic_id,substring_index( substring_index( replace(hierarchy_string,'-',','), ',', b.help_topic_id + 1 ), ',',- 1 ) rel_tsn

  FROM hierarchy a

    JOIN mysql.help_topic b ON b.help_topic_id < ( length( replace(hierarchy_string,'-',',') ) - length( REPLACE ( replace(hierarchy_string,'-',','), ',', '' ) ) + 1 ) 

   --where a.tsn=678130

  order by a.tsn,b.help_topic_id

  

create index i_bio_hierarchy on bio_hierarchy(tsn);

select * from bio_hierarchy;

-- 2、将物种的各级分类的名字以及级别查出来

create table bio_fenlei as

select a.*,b.completename ,e.shortauthor,c.rank_id,d.rank_name 

  from bio_hierarchy a 

  left join longnames b 

    on a.rel_tsn=b.tsn 

  left join taxonomic_units c               -- 分类单元

  on b.tsn =c.tsn 

  left join taxon_unit_types d              -- 分类类别

    on c.kingdom_id =d.kingdom_id 

   and c.rank_id =d.rank_id 

  left join strippedauthor e                -- 命名人

    on c.taxon_author_id =e.taxon_author_id

  where a.tsn=678130

 ;

create index i_bio_fenlei_1 on bio_fenlei(tsn);

-- 3、将窄表（一个物种对应多条记录）变为宽表（一个物种对应一条记录）

 -- 亚种

insert into bio_names_zzt (name_code  ,  Subspecies,author        )

select tsn,a.completename,shortauthor from bio_fenlei a where rank_name ='Subspecies';

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Species' set a.Species=b.completename;

 -- 种

insert into bio_names_zzt (name_code  ,  Species,author        )

select tsn,a.completename,shortauthor from bio_fenlei a where rank_name ='Species' 

and not exists (select 1 from bio_fenlei where tsn=a.tsn and rank_name ='Subspecies' );

create index i_bio_names_zzt_1 on bio_names_zzt(name_code);

-- 界

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Kingdom' set a.Kingdom=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Subkingdom' set a.Subkingdom=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Infrakingdom' set a.Infrakingdom=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Superphylum' set a.Superphylum=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Phylum' set a.Phylum=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Subphylum' set a.Subphylum=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Class' set a.Class=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Subclass' set a.Subclass=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Infraclass' set a.Infraclass=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Superorder' set a.Superorder=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='order' set a.order_e=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Suborder' set a.Suborder=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Infraorder' set a.Infraorder=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Superfamily' set a.Superfamily=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Family' set a.Family=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Subfamily' set a.Subfamily=b.completename;

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Tribe' set a.Tribe=b.completename;

-- 属

update bio_names_zzt a left join bio_fenlei b on a.name_code=b.tsn and b.rank_name ='Genus' set a.Genus=b.completename;

select * from bio_fenlei

select Subspecies,Species,a.* from bio_names_zzt a where Subspecies like 'Upupa epops%';

最终格式是这样的，以戴胜鸟（有亚种）为例：

select Subspecies,Species,a.* from bio_names_zzt a where name_code='702522'; 

云斑白条天牛（没有亚种）