表中关键有三个字段,其中一条记录为id:532601908000000237,start:2017-03-04 12:30:00 end:2017-03-07 00:21:00 dur:59.85需要实现如下效果:
1、当dur字段值大于24时,按单条记录的start与end时间间隔<=24小时拆分,计算拆分后的记录数,即int(59.85/24)+1=3条 ;
2、将该条记录拆分,拆分后第一条的id不变,start不变,end等于start+24小时后的时间;
3、第二条记录的id不变,start等于第一条记录的end, end等于start+24小时后的时间;
4、条三条记录的的id不变,start等于第二条记录的end, end等于原始记录的end;
1、当dur字段值大于24时,按单条记录的start与end时间间隔<=24小时拆分,计算拆分后的记录数,即int(59.85/24)+1=3条 ;
2、将该条记录拆分,拆分后第一条的id不变,start不变,end等于start+24小时后的时间;
3、第二条记录的id不变,start等于第一条记录的end, end等于start+24小时后的时间;
4、条三条记录的的id不变,start等于第二条记录的end, end等于原始记录的end;
drop table if exists tb;
CREATE TABLE `tb` (
`id` varchar(20) DEFAULT NULL,
`start` datetime DEFAULT NULL,
`end` datetime DEFAULT NULL,
`dur` decimal(12,2) DEFAULT NULL
);
insert tb values
('532601908000000237', '2017-03-04 12:30:00', '2017-03-07 00:21:00', 59.85),
('532601908000000238', '2017-03-04 12:30:00', '2017-03-07 00:21:00', 59.85);
select a.id,
a.start,
case when id.id = 1 then a.start else a.start + interval 24 * id.id hour end as start_new,
a.`end`,
case when id.id = cast(a.dur/24 as signed)+1 then a.`end` else a.start + interval 24 *id.id hour end as end_new,
a.dur,
cast(a.dur/24 as signed)+1 as split_rows
from tb a,
(
select @id:=@id+1 as id
from information_schema.tables a,
information_schema.tables b,
(select @id:=0) i
) id
where id.id <= cast(a.dur/24 as signed)+1
order by a.id, id.id
;