mysql – 找出两条记录之间的时间
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 找出两条记录之间的时间，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4215字，纯文字阅读大概需要7分钟。
内容图文
所以这里是我有一个表格,它记录了设备的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 – 找出两条记录之间的时间全部内容，希望文章能够帮你解决mysql – 找出两条记录之间的时间所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】