尊重作者，原文链接：https://blog.csdn.net/liminghui4321/article/details/102763052
原始数据表：
需要将上面的时间段转成每一天的记录：
在原文的基础上作了一些更改：
1、在表sales上增加一个字段bj:alter table sales add column bj int DEFAULT 0 null;
2、存储过程基础上增加了一些更改：
drop PROCEDURE if EXISTS p4;
create procedure p4()
begin
declare id1,gt,it,bjj int;
declare brand varchar(15);
declare sdate,edate,mdate date;
declare flag int default 0;
— 声明游标
declare cur cursor for select *,datediff(enddate,startdate) from sales where bj =0;
declare continue handler for not found set flag = 1;
— select *,datediff(enddate,startdate) from sales where bj=0;
— 打开游标
open cur;
— 获取结果
l2:loop
fetch cur into id1,brand,sdate,edate,bjj,gt;
if flag=1 then — 当无法fetch会触发handler continue
select ‘flag=1 not found’;
leave l2;
end if;
— 逻辑处理,拆解跨天
IF(gt>=0) THEN
set it=0;
select id1,brand,sdate,edate,bjj,gt;
while it<=gt DO
insert into sales2(id,brand,tdate) values(id1, brand,date_add(sdate,interval it day));
set it=it+1;
end while;
update sales set bj=1 where id = id1;
end if;
— 关闭游标
end loop;
close cur;
end;
call p4();
—-
