mysql - MySQL分组以查找时间差异

  显示原文与译文双语对照的内容
0 0

我有 car_history 表,我需要找到所选日期的每个 car_status 之间的时间差异。

e.g 1. to_load - 9 h. 2. to_load - 8 h. 等等...

如何从以下位置找到条目 id = 1 to id = 4id = 8 to id = 10 计算日志记录 to_load 状态的方式。

 --------------------------------------------------------------------
 | 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|
 --------------------------------------------------------------------
时间:原作者:5个回答

0 0

希望这对你有帮助

首先,我创建了带有数据的car_history表:

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');

然后,我执行了这个查询

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

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

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
原作者:
...