mysql - 行之间的时间差(MySQL)

Question

我有一个像这样的表格(其他列已删除):

+---------+----------+---------------------+---------------------+
| id      | party_id | begintime           | endtime             |
+---------+----------+---------------------+---------------------+
| 1528604 |    10000 | 2011-09-22 15:33:52 | 2011-09-23 14:09:34 |
| 1528605 |    10000 | 2011-09-23 14:12:48 | 2011-09-23 14:12:50 |
| 1528606 |    10000 | 2011-09-23 14:14:36 | 2011-09-23 15:29:59 |
| 1528607 |    10000 | 2011-09-23 15:33:50 | 2011-09-26 09:52:19 |
| 1528608 |    10000 | 2011-09-26 09:54:59 | 2011-09-26 11:20:55 |
| 1528609 |    10000 | 2011-09-26 11:23:30 | 2011-09-26 11:39:44 |
| 1528610 |    10000 | 2011-09-26 11:47:19 | 2011-09-26 12:45:00 |
| 1528611 |    10000 | 2011-09-26 12:47:22 | 2011-09-26 14:28:53 |
| 1528612 |    10000 | 2011-09-26 14:31:38 | 2011-09-26 15:26:08 |
| 1528613 |    10000 | 2011-09-26 15:29:37 | 2011-09-26 18:15:00 |
| 1528614 |    10000 | 2011-09-26 18:16:48 | 2011-09-26 19:38:11 |
| 1528615 |    10000 | 2011-09-26 19:40:19 | 2011-09-26 22:23:37 |
+---------+----------+---------------------+---------------------+

我需要计算下一行的endtime 和begintime 之间的时间差。预期结果应该是这样的:

194
106
231
......

即194 = timestampdiff(second,'2011-09-23 14:09:34','2011-09-23 14:12:48') 等等。

我已经尝试过这个:

select timestampdiff(second, t1.begintime, t2.endtime) 
    from doses t1 join doses t2
    on t2.id=(select t2.id from doses t2 where t2.id>t1.id limit 1) 
        and t2.party_id=10000
        and t1.party_id=10000;

但是结果有太多值。我做错了什么？

Answer 1

不确定您想要达到的确切结果，但您可以尝试以下操作:

select t1.id, t1.`party_id`, t1.begintime, t1.endtime, timestampdiff(second,t1.endtime, t2.begintime) `timediff`
from (
  select test.*, @rowno1 := @rowno1 + 1 rowno
  from test
  cross join (select @rowno1 := 1) t
  order by id
) t1
left join (
  select test.*, @rowno2 := @rowno2 + 1 rowno
  from test
  cross join (select @rowno2 := 0) t
  order by id
) t2 on t1.rowno = t2.rowno

这里是SQLFiddle Demo.