思路是,找出奇偶行放在两个表,然后作差,上sql:
有bug,只能算出一半的差值
#取出奇数行数据
select * from
(select @rownum1:=@rownum1+1 as row_num,t1.tract_time
from table_name t1,(select @rownum1:=0)tmp_table)tt1
where tt1.row_num%2=1;
#取出偶数行数据
select * from
(select @rownum1:=@rownum1+1 as row_num,t1.tract_time
from table_name t1,(select @rownum1:=0)tmp_table)tt1
where tt1.row_num%2=0;
#把两个表合并,计算相邻两条记录的时间差
select ttt1.*,ttt2.*,ttt2.tract_time-ttt1.tract_time as time_diff
from
(select * from
(select @rownum1:=@rownum1+1 as row_num,t1.tract_time as tract_time
from table_name t1,(select @rownum1:=0)tmp_table)tt1
where tt1.row_num%2=1) ttt1,
(select * from
(select @rownum2:=@rownum2+1 as row_num,t1.tract_time as tract_time
from table_name t1,(select @rownum2:=0)tmp_table)tt1
where tt1.row_num%2=0) ttt2
where ttt2.row_num=ttt1.row_num+1;
哈哈,来更新了,修复bug
#取出所有数据从1开始
select @rownum1:=@rownum1+1 as row_num,t1.tract_time
from table_name t1,(select @rownum1:=0)tmp_table)tt1;
#取出所有数据从2开始
select @rownum1:=@rownum1+1 as row_num,t1.tract_time
from table_name t1,(select @rownum1:=1)tmp_table)tt2;
#将行数相同的作差
select * from(
select tt1.row_num,tt1.tract_time as time1,tt2.row_num,tt2.tract_time as time2,(tt2.tract_time-tt1.tract_time) as diff from (
select @rownum1:=@rownum1+1 as row_num,t1.tract_time
from table_name t1,(select @rownum1:=0)tmp_table) tt1,
(select @rownum1:=@rownum1+1 as row_num,t1.tract_time
from table_name t1,(select @rownum1:=1)tmp_table) tt2;
)
where tt2.row_num=tt2.row_num) f where diff > 100;