mysql关于group_concat 和union all的使用

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