今天，看到別人問問題，需求大概是這樣的。
id，總的時間間隔，這個時間段的開始時間，這個時間段的結束時間。
如上面id=1，出來結果應該是1，
1 89 2001-01-01 08:10:20 2001-01-01 08:11:51 1 2 2001-01-01 08:12:51 2001-01-01 08:12:53 =============================================================================
對於上面這個需求，如果用oracle，那麼應該比較好實現。用group by,over,lag的方式很輕松就能搞定。但對於mysql，似乎統計函數比較少。
本來對於MySQL復雜的SQL應用也不算熟悉。於是試著寫了寫。
一、我自己整理了一下思路，第一步目標：
1、需要整理出一個唯一字段分組
2、需要在下一條記錄顯示上一條記錄的結束時間
二、根據第一步整理的目標
1、對第一個小目標分解
1）首先每行的唯一行號，這是形成唯一字段分組可以借用的。
2）標記位要能夠區分不同用戶，比如上一個用戶的結束時間和下一個用戶的開始時間剛好連了起來，要能區分出是兩個用戶。
2、第二個小目標分解
1）把時間轉化為數字或者字符，去掉不必要字符，這樣便於後續處理
三、創建測試
1、添加表
create table time_log( id int, --用戶id s int, --時間間隔 start_t varchar(20), --開始時間 end_t varchar(20) --結束時間 )2、添加測試數據
insert into time_log(id,s,start_t,end_t) values(1,20,'2001-01-01 08:10:20','2001-01-01 08:10:40'); insert into time_log(id,s,start_t,end_t) values(1,9,'2001-01-01 08:10:41','2001-01-01 08:10:50'); insert into time_log(id,s,start_t,end_t) values(1,60,'2001-01-01 08:10:51','2001-01-01 08:11:51'); insert into time_log(id,s,start_t,end_t) values(1,2,'2001-01-01 08:12:51','2001-01-01 08:12:53'); insert into time_log(id,s,start_t,end_t) values(2,51,'2001-01-01 08:10:00','2001-01-01 08:10:51'); insert into time_log(id,s,start_t,end_t) values(2,60,'2001-01-01 08:11:00','2001-01-01 08:12:00'); insert into time_log(id,s,start_t,end_t) values(2,5,'2001-01-01 08:13:00','2001-01-01 08:13:05'); insert into time_log(id,s,start_t,end_t) values(2,15,'2001-01-01 08:13:06','2001-01-01 08:13:21'); insert into time_log(id,s,start_t,end_t) values(2,5,'2001-01-01 08:13:25','2001-01-01 08:13:30');
1）根據第一步目標
出來SQL
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
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 ) t2）最終一步步處理，出來最終SQL
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
