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

女爷i 2022-12-10 10:12 335阅读 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;