1.把多条记录，合并为一条记录的多个信息
select a.id,a.schoolid,a.split_key as splitkey,a.`year`,a.num,
replace(SUBSTR(a.cname,1,4),’,’,”) as name1, IF(str_to_date(SUBSTR(a.csd ,1,10), ‘%Y-%m-%d %H’)=’0000-00-00 00:00:00′,null,str_to_date(SUBSTR(a.csd ,1,10), ‘%Y-%m-%d %H’)) as startDate1,IF(str_to_date(SUBSTR(a.ced ,1,10), ‘%Y-%m-%d %H’)=’0000-00-00 00:00:00′,null, str_to_date(SUBSTR(a.ced ,1,10), ‘%Y-%m-%d %H’)) as endDate1,
replace(SUBSTR(a.cname,6,4),’,’,”) as name2, IF(str_to_date(SUBSTR(a.csd ,12,10), ‘%Y-%m-%d %H’)=’0000-00-00 00:00:00′,null,str_to_date(SUBSTR(a.csd ,12,10), ‘%Y-%m-%d %H’)) as startDate2,IF(str_to_date(SUBSTR(a.ced ,12,10), ‘%Y-%m-%d %H’)=’0000-00-00 00:00:00′,null,str_to_date(SUBSTR(a.ced ,12,10), ‘%Y-%m-%d %H’)) as endDate2,
replace(SUBSTR(a.cname,11,4),’,’,”) as name3, IF(str_to_date(SUBSTR(a.csd ,23,10), ‘%Y-%m-%d %H’)=’0000-00-00 00:00:00′,null,str_to_date(SUBSTR(a.csd ,23,10), ‘%Y-%m-%d %H’)) as startDate3,IF(str_to_date(SUBSTR(a.ced ,23,10), ‘%Y-%m-%d %H’)=’0000-00-00 00:00:00′,null,str_to_date(SUBSTR(a.ced ,23,10), ‘%Y-%m-%d %H’)) as endDate3,
replace(SUBSTR(a.cname,16,4),’,’,”) as name4, IF(str_to_date(SUBSTR(a.csd ,34,10), ‘%Y-%m-%d %H’)=’0000-00-00 00:00:00′,null,str_to_date(SUBSTR(a.csd ,34,10), ‘%Y-%m-%d %H’)) as startDate4,IF(str_to_date(SUBSTR(a.ced ,34,10), ‘%Y-%m-%d %H’)=’0000-00-00 00:00:00′,null,str_to_date(SUBSTR(a.ced ,34,10), ‘%Y-%m-%d %H’)) as endDate4
from
(select tn.schoolid,tn.split_key,tn.`year`,tn.id,count(*) as num,GROUP_CONCAT(tn.name)as cname,GROUP_CONCAT(tn.start_date) as csd,GROUP_CONCAT(tn.end_date) as ced
from el_bp_term_new tn where tn.schoolid=#{entity.schoolid}
GROUP BY tn.schoolid,tn.split_key,tn.`year`) a；
2.拆分一条记录的多个信息，到多条记录
select CONCAT(‘insert into el_bp_term_new values(‘,CONCAT(“‘”,IF(a.id is not NULL,a.id,”),”‘,”),CONCAT(“‘”,IF(a.split_key is not NULL,a.split_key,”),”‘,”),CONCAT(“‘”,a.schoolid,”‘,”),CONCAT(“‘”,a.year,”‘,”),CONCAT(“‘”,a.name1,”‘,”),CONCAT(“‘”,a.start_date1,”‘,”),CONCAT(“‘”,a.end_date1,”‘”),’);’) from
(
select CONCAT_WS(‘-‘,schoolid,year,’1’) as id,split_key,schoolid,year,name1,start_date1,end_date1 from el_bp_term t
union all
select CONCAT_WS(‘-‘,schoolid,year,’2’) as id,split_key,schoolid,year,name2,start_date2,end_date2 from el_bp_term t
union all
select CONCAT_WS(‘-‘,schoolid,year,’3’) as id,split_key,schoolid,year,name3,start_date3,end_date3 from el_bp_term t
union all
select CONCAT_WS(‘-‘,schoolid,year,’4’) as id,split_key,schoolid,year,name4,start_date4,end_date4 from el_bp_term t ) a
where a.name1 is not null or a.name1 <> ”
order by a.id