mysql查询相邻两条记录时间差

最新推荐文章于 2024-04-19 11:27:55 发布

DaxiaLeeSuper

最新推荐文章于 2024-04-19 11:27:55 发布

阅读量1.8k

点赞数

分类专栏： SQL 文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/DaxiaLeeSuper/article/details/108796976

版权

SQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

思路是，找出奇偶行放在两个表，然后作差，上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;