mysql 根据 某个字段 把一行数据拆成多行

时间:2022-02-04 01:06:57


原始表的数据


mysql 根据 某个字段 把一行数据拆成多行


处理成的临时表的数据

mysql 根据 某个字段 把一行数据拆成多行


最终表的数据:


mysql 根据 某个字段 把一行数据拆成多行


代码如下: 仅供参考:


CREATE DEFINER=`data_mining`@`%` PROCEDURE `new_procedure_test`()
BEGIN


declare num int ;
declare vsql longtext;
declare count int;
set vsql='';


drop table if exists crm_excel.customer_clue_detail_temp;


create table crm_excel.customer_clue_detail_temp(id int PRIMARY KEY auto_increment 
, cus_clue_id varchar(5000));


insert into crm_excel.customer_clue_detail_temp(cus_clue_id)
select replace(clue_id,'select ',concat('select ',id,' as id,')) as cus_clue_id
from 
(
select id , 
replace(concat('select ', clue_id  ),',',' as clue_id  union all  select ') as clue_id
from weixin_crm.newcrm_customer a
 where clue_id like '%,%'
 ) a ;
 
select  count(1) into count from crm_excel.customer_clue_detail_temp;
set @str = (select cus_clue_id  from crm_excel.customer_clue_detail_temp where id=1);
set vsql=@str;
set num=2;


while num <= count do 


set @str = (select cus_clue_id  from crm_excel.customer_clue_detail_temp where id=num);
set vsql=concat(vsql,' union all ' , @str);
set num=num+1;
end while;
set vsql=concat('insert into crm_excel.customer_clue_detail(cus_id,clue_id)', vsql );
set @v_sql=vsql;
#select  @v_sql;
prepare stmt from @v_sql;
execute stmt  ;
deallocate prepare stmt;


END