本文介绍了计算 MySQL 中的时差但考虑到事件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个这样结构的表格
I have a table structured like this
index - date ------ time ----- status
1 2015-01-01 13:00:00 start
2 2015-01-01 13:10:00 continue
3 2015-01-01 13:20:00 continue
4 2015-01-01 13:30:00 end
5 2015-01-01 13:30:00 ready
6 2015-01-01 13:40:00 start
7 2015-01-01 13:50:00 continue
8 2015-01-01 15:00:00 end
而我想做的是计算开始和结束之间的时间(即索引 1-4 是 30 分钟,6-8 是 20 分钟),但只考虑第一个开始和第一个结束,以便查询不选择索引 1-8 的时间差.哪个查询用于计算两个雕像(开始 - 结束)之间的时间差并显示多个开始 - 结束实例的结果,而不会将它们批处理为一个事件?
and what I would like to do is count the time between start and end (ie. index 1-4 is 30min, 6-8 is 20min), but taking into account only the first start and first end, so that the query doesn't choose the time difference of index 1-8. Which query is used to calculate time difference between two statues (start-end) and show the result for multiple instances of start-end without them getting batched into one event?
推荐答案
考虑以下...
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,dt DATETIME NOT NULL
,status VARCHAR(12) NOT NULL
);
INSERT INTO my_table VALUES
(1 ,'2015-01-01 13:00:00' , 'start'),
(2 ,'2015-01-01 13:10:00' , 'continue'),
(3 ,'2015-01-01 13:20:00' , 'continue'),
(4 ,'2015-01-01 13:30:00' , 'end'),
(5 ,'2015-01-01 13:30:00' , 'ready'),
(6 ,'2015-01-01 13:40:00' , 'start'),
(7 ,'2015-01-01 13:50:00' , 'continue'),
(8 ,'2015-01-01 15:00:00' , 'end');
SELECT x.*
, TIMEDIFF(MIN(y.dt),x.dt)diff
FROM my_table x
JOIN my_table y
ON y.dt >= x.dt
WHERE x.status = 'start'
AND y.status = 'end'
GROUP
BY x.id;
+----+---------------------+--------+----------+
| id | dt | status | diff |
+----+---------------------+--------+----------+
| 1 | 2015-01-01 13:00:00 | start | 00:30:00 |
| 6 | 2015-01-01 13:40:00 | start | 01:20:00 |
+----+---------------------+--------+----------+
这篇关于计算 MySQL 中的时差但考虑到事件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!