mysql - 根据状态计算时间差异

Question

我有一张 table

ID    | Date     | Time     | Status  
73937 | 3/1/2012 | 17:25:00 | Pending        |     
73938 | 3/1/2012 | 17:27:00 | Pending        | time diff 2 min  
73948 | 3/1/2012 | 17:29:00 | Pending        | time diff 2 min  
73952 | 3/1/2012 | 17:45:00 | Stop Clock     | time diff 16 min   
74967 | 3/1/2012 | 19:15:00 | Stop Clock     | time diff 0 min  
74970 | 3/1/2012 | 20:15:00 | Pending Active | time diff 60 min  
74972 | 3/1/2012 | 20:21:00 | Active         | time diff 6 min

如何在状态不是Stop Clock的情况下计算时间差异并在 MySql 中汇总时间差异？
您可以查看状态是否为 Stop Clock 将停止计数(0 分钟)。

Answer 1

这个查询:

select id, date, time, status, hour(diff) * 60 + minute(diff) Diff from (
  select id, date,
    if(@active or status != 'StopClock',
       timediff(addtime(date, time), @prevTime), 0) Diff,
    time(@prevTime := addtime(date, time)) time,
    @active := status != 'StopClock',
    status
  from t, (select @prevTime := 0, @active := false) init
  order by id
) final

会导致:

+-------+------------+----------+---------------+------+
|  ID   |    DATE    |   TIME   |    STATUS     | DIFF |
+-------+------------+----------+---------------+------+
| 73937 | 2012-03-01 | 17:25:00 | Pending       |      |
| 73938 | 2012-03-01 | 17:27:00 | Pending       |    2 |
| 73948 | 2012-03-01 | 17:29:00 | Pending       |    2 |
| 73952 | 2012-03-01 | 17:45:00 | StopClock     |   16 |
| 74967 | 2012-03-01 | 19:15:00 | StopClock     |    0 |
| 74970 | 2012-03-01 | 20:15:00 | PendingActive |   60 |
| 74972 | 2012-03-01 | 20:21:00 | Active        |    6 |
+-------+------------+----------+---------------+------+

请注意，出于某种原因，您没有考虑秒数。如果您对此有任何疑问，请告诉我。