所以这是我有一个表的场景，它记录了来自设备的 gps 数据以及引擎状态(开或关)。我需要找出引擎状态开启和关闭的时间。
vehicle_id | engine_status | time_stamp
1 | on |2014-05-21 07:30:02
2 | off |2014-05-21 07:40:02
1 | on |2014-05-21 07:50:02
1 | on |2014-05-21 08:00:02
1 | on |2014-05-21 08:10:02
2 | on |2014-05-21 08:20:02
1 | off |2014-05-21 08:30:02
1 | off |2014-05-21 08:40:02
2 | on |2014-05-21 08:50:02
1 | off |2014-05-21 09:00:02
2 | on |2014-05-21 09:10:02
1 | off |2014-05-21 09:10:06
1 | off |2014-05-21 09:30:02
2 | on |2014-05-21 09:30:02
1 | off |2014-05-21 09:35:02
2 | on |2014-05-21 09:39:02
1 | off |2014-05-21 09:40:02
1 | off |2014-05-21 09:45:02
2 | off |2014-05-21 09:50:02
1 | on |2014-05-21 09:55:02
1 | on |2014-05-21 09:56:02
2 | off |2014-05-21 09:58:02
1 | on |2014-05-21 09:59:02
1 | on |2014-05-21 09:59:02
1 | on |2014-05-21 10:10:02
2 | off |2014-05-21 09:30:02
最佳答案
考虑以下...
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(vehicle_id INT NOT NULL
,engine_status VARCHAR(12) NOT NULL
,time_stamp TIMESTAMP NOT NULL
);
INSERT INTO my_table VALUES
(1,'on' ,'2014-05-21 07:30:02'),
(2,'off' ,'2014-05-21 07:40:02'),
(1,'on' ,'2014-05-21 07:50:02'),
(1,'on' ,'2014-05-21 08:00:02'),
(1,'on' ,'2014-05-21 08:10:02'),
(2,'on' ,'2014-05-21 08:20:02'),
(1,'off' ,'2014-05-21 08:30:02'),
(1,'off' ,'2014-05-21 08:40:02'),
(2,'on' ,'2014-05-21 08:50:02'),
(1,'off' ,'2014-05-21 09:00:02'),
(2,'on' ,'2014-05-21 09:10:02'),
(1,'off' ,'2014-05-21 09:10:06'),
(1,'off' ,'2014-05-21 09:30:02'),
(2,'on' ,'2014-05-21 09:30:02'),
(1,'off' ,'2014-05-21 09:35:02'),
(2,'on' ,'2014-05-21 09:39:02'),
(1,'off' ,'2014-05-21 09:40:02'),
(1,'off' ,'2014-05-21 09:45:02'),
(2,'off' ,'2014-05-21 09:50:02'),
(1,'on' ,'2014-05-21 09:55:02'),
(1,'on' ,'2014-05-21 09:56:02'),
(2,'off' ,'2014-05-21 09:58:02'),
(1,'on' ,'2014-05-21 09:59:02'),
(1,'on' ,'2014-05-21 09:59:02'),
(1,'on' ,'2014-05-21 10:10:02'),
(2,'off' ,'2014-05-21 09:30:02');
SELECT vehicle_id
, engine_status
, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(end,start)))) total
FROM
(
SELECT a.vehicle_id
, a.engine_status
, a.time_stamp start
, MIN(c.time_stamp) end
FROM
( SELECT *
, CASE WHEN @prev= vehicle_id THEN @i:=@i+1 ELSE @i:=1 END rank
, @prev:=vehicle_id
FROM my_table
, ( SELECT @i:=1,@prev:='')x
ORDER
BY vehicle_id
, time_stamp
) a
LEFT
JOIN
( SELECT *
, CASE WHEN @prev= vehicle_id THEN @i:=@i+1 ELSE @i:=1 END rank
, @prev:=vehicle_id
FROM my_table
, ( SELECT @i:=1,@prev:='')x
ORDER
BY vehicle_id
, time_stamp
) b
ON b.vehicle_id = a.vehicle_id
AND b.engine_status = a.engine_status
AND b.rank = a.rank - 1
LEFT
JOIN
( SELECT *
, CASE WHEN @prev= vehicle_id THEN @i:=@i+1 ELSE @i:=1 END rank
, @prev:=vehicle_id
FROM my_table
, ( SELECT @i:=1,@prev:='')x
ORDER
BY vehicle_id
, time_stamp
) c
ON c.vehicle_id = a.vehicle_id
AND c.engine_status = a.engine_status
AND c.rank >= a.rank
LEFT
JOIN
( SELECT *
, CASE WHEN @prev= vehicle_id THEN @i:=@i+1 ELSE @i:=1 END rank
, @prev:=vehicle_id
FROM my_table
, ( SELECT @i:=1,@prev:='')x
ORDER
BY vehicle_id
, time_stamp
) d
ON d.vehicle_id = c.vehicle_id
AND d.engine_status = c.engine_status
AND d.rank = c.rank + 1
WHERE b.rank IS NULL
AND c.rank IS NOT NULL
AND d.rank IS NULL
GROUP
BY a.vehicle_id
, a.engine_status
, a.rank
) x
GROUP BY vehicle_id, engine_status;
+------------+---------------+----------+
| vehicle_id | engine_status | total |
+------------+---------------+----------+
| 1 | off | 01:15:00 |
| 1 | on | 00:55:00 |
| 2 | off | 00:08:00 |
| 2 | on | 01:10:00 |
+------------+---------------+----------+
关于mysql - 找出两条记录之间的时间，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23777887/