单个表中连续两行的时间戳之间的差异 [英] difference between timestamps in two consecutive rows in single table

问题描述

我想要单个查询的波纹管表的两连续行的时间戳之间的时间差:

I want the time difference between timestamps from two consecutive rows of bellow table with single query :

我试过这个查询，但它非常低效并且导致服务器超时...

I tried this query but which is very inefficient and giving server timeout...

SELECT t1.t_id                                           AS id1, 
       t2.t_id                                           AS id2, 
       t1.timestamp                                      AS timestamp1, 
       t2.timestamp                                      AS timestamp2, 
       Timestampdiff(second, t1.timestamp, t2.timestamp) AS diff 
FROM   (SELECT * 
        FROM   `track`) AS t1, 
       (SELECT * 
        FROM   `track` 
        WHERE  `t_id` != (SELECT `t_id` 
                          FROM   `track` 
                          LIMIT  1)) AS t2 
WHERE  ( t1.t_id - 1 ) = t2.t_id 

推荐答案

自己加入表格，像这样(没试过，但你懂的)

Join the table with itself, like this (haven't tried, but you get the idea)

SELECT t1.t_id AS id1, 
   t2.t_id AS id2, 
   t1.timestamp AS timestamp1, 
   t2.timestamp AS timestamp2, 
   Timestampdiff(second, t1.timestamp, t2.timestamp) AS diff
FROM track AS t1
JOIN track AS t2 ON (t2.t_id = t1.t_id - 1)

这篇关于单个表中连续两行的时间戳之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！