MySQL组行查找时差 [英] MySQL group rows to find time difference

问题描述

我有car_history表，我需要查找所选日期中每个car_status之间的时差.

I have car_history table, I need to find time difference between each car_status in selected date.

例如1. to_load - 9 h. 2. to_load - 8 h.等等...

我如何找到 id = 1 to id = 4和id = 8 to id = 10中的条目来计数日志如何变为to_load状态.

How I can find entries from id = 1 to id = 4 and id = 8 to id = 10 to count how log took to_load status.

  --------------------------------------------------------------------
  |  id | car_id | car_status | comments        | entry_date         |
  --------------------------------------------------------------------
  |  1  |   1    | to_load    | preparing       | 2013-06-12 08:00:00|
  |  2  |   1    | to_load    | preparing goods | 2013-06-12 14:10:00|
  |  3  |   1    | to_load    | loading goods   | 2013-06-12 17:00:50|
  |  4  |   1    | loaded     | finding driver  | 2013-06-13 07:00:00|
  |  5  |   1    | loaded     | ready to deliver| 2013-06-13 08:00:00|
  |  6  |   1    | on_road    | delivering goods| 2013-06-13 09:00:00|
  |  7  |   1    | in_garage  | goods delivered | 2013-06-13 15:00:00|
  |  8  |   1    | to_load    | preparing       | 2013-06-14 08:00:00|
  |  9  |   1    | to_load    | preparing goods | 2013-06-14 14:10:00|
  |  10 |   1    | to_load    | loading goods   | 2013-06-14 16:00:50|
  |  11 |   1    | loaded     | finding driver  | 2013-06-12 08:00:00|
  --------------------------------------------------------------------

推荐答案

希望这对您有帮助

首先，我用数据创建了car_history表:

First, i've created car_history table with data:

CREATE TABLE car_history (
  id int(11) NOT NULL AUTO_INCREMENT,
  car_id int(11) ,
  car_status varchar(45),
  comments varchar(45),
  entry_date datetime ,
  PRIMARY KEY (id)
) ;


INSERT INTO car_history (id, car_id,car_status,comments,entry_date) VALUES (1, '1', 'to_load', 'preparing', '2013-06-12 08:00:00');
INSERT INTO car_history (id, car_id,car_status,comments,entry_date) VALUES (2, '1', 'to_load', ' preparing goods', '2013-06-12 14:10:00');
INSERT INTO car_history (id, car_id,car_status,comments,entry_date) VALUES (3, '1', 'to_load', ' loading goods', '2013-06-12 17:00:50');
INSERT INTO car_history (id, car_id,car_status,comments,entry_date) VALUES (4, '1', 'loaded', ' finding driver', '2013-06-13 07:00:00');
INSERT INTO car_history (id, car_id,car_status,comments,entry_date) VALUES (5, '1', 'loaded', ' ready to deliver', '2013-06-13 08:00:00');
INSERT INTO car_history (id, car_id,car_status,comments,entry_date) VALUES (6, '1', 'on_road', ' delivering goods', '2013-06-13 09:00:00');
INSERT INTO car_history (id, car_id,car_status,comments,entry_date) VALUES (7, '1', 'in_garage', ' goods delivered', '2013-06-13 15:00:00');
INSERT INTO car_history (id, car_id,car_status,comments,entry_date) VALUES (8, '1', 'to_load', ' preparing', '2013-06-14 08:00:00');
INSERT INTO car_history (id, car_id,car_status,comments,entry_date) VALUES (9, '1', 'to_load', ' preparing goods', '2013-06-14 14:10:00');
INSERT INTO car_history (id, car_id,car_status,comments,entry_date) VALUES (10, '1', 'to_load', ' loading goods', '2013-06-14 16:00:50');
INSERT INTO car_history (id, car_id,car_status,comments,entry_date) VALUES (11, '1', 'loaded', ' finding driver', '2013-06-12 08:00:00');

然后，我已经执行了该查询

then, i've executed this query

select table1.id, table1.car_id, table1.entry_date startDate,
       table2.entry_date endDate, timediff(table2.entry_date , table1.entry_date) duration
 from 
       (select @i1:=@i1+1 as rownum, temp.*  from (select @i1:=0 ) as temp_iterator,
       (SELECT ch1.id, ch1.car_id, ch1.entry_date FROM car_history ch1 left outer join  car_history ch2 on ch1.id = ch2.id +1 
               left outer join  car_history ch3 on ch1.id = ch3.id -1
               where ch1.car_status = 'to_load' and 
              (ch1.car_status <> ch2.car_status or ch2.car_status is null
               or ch1.car_status <> ch3.car_status or ch3.car_status is null)
               order by ch1.id
        ) as temp) Table1 ,
       (select @i2:=@i2+1 as rownum, temp.* from (select @i2:=0 ) as temp_iterator,
       (SELECT ch1.id, ch1.car_id, ch1.entry_date FROM car_history ch1 left outer join  car_history ch2 on ch1.id = ch2.id +1 
               left outer join  car_history ch3 on ch1.id = ch3.id -1
               where ch1.car_status = 'to_load' and 
              (ch1.car_status <> ch2.car_status or ch2.car_status is null
               or ch1.car_status <> ch3.car_status or ch3.car_status is null)
               order by ch1.id
        ) as temp) Table2
where table1.rownum = table2.rownum-1
and mod(table1.rownum,2) = 1

最后，我得到了这个结果:

and finally, i've got this result:

id  | car_id    | startDate             | endDate               | duration
1   | 1         | 2013-06-12 08:00:00   | 2013-06-12 17:00:50   | 09:00:50
8   | 1         | 2013-06-14 08:00:00   | 2013-06-14 16:00:50   | 08:00:50

这篇关于MySQL组行查找时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！