mysql中类似oracle的over分组实现(二)

select id,s_nums 时间s
,str_to_date(istarttimes,'%Y-%m-%d %h:%i:%s') as 开始时间
,end_t as 结束时间 from
(
select case when @knum=dirow then 0 else dirow end as flag,@knum:=dirow,t.* from 
(
select * from (
select t.*,date_sub(end_t, interval totals day_second) as istarttimes from
(
select t.*,@rowid:=@rowid+di as dirow,@sums:=case when di=0 then @sums+s+1 else s end as totals 
,@sums2:=case when di=0 then @sums2+s+0 else s end as s_nums from
(
select t.*,case when preendnum=dstartnum then 0 else rownum end as di

from 
(
select @rownum:=@rownum+1 as rownum,@preEndTime as preendnum,@preEndTime:=dendnum ,t.* from
(
select t.*
,CONCAT(id,'-',date_format(str_to_date(t.START_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s'))as dstartnum
,CONCAT(id,'-',date_format(str_to_date(t.end_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s')+1) as dendnum
,date_format(str_to_date(t.START_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s') istart
,date_format(str_to_date(t.end_T,'%Y-%m-%d %h:%i:%s'),'%Y%m%d%h%i%s') iend
from time_log t
) t,(SELECT @preEndTime:='',@rownum:=0) r
) t
) t,(SELECT @rowid:=0) r
) t
) t order by rownum desc
) t,(SELECT @knum:=-1) r
) t where t.flag<> 0 order by rownum