我有一张桌子,其结构如下
+---------------------+---------+
| date_time | portNo1 |
+---------------------+---------+
| 2016-01-08 13:15:46 | 1 |
| 2016-01-08 13:16:01 | 2 |
| 2016-01-08 13:16:16 | 1 |
| 2016-01-08 13:16:31 | 2 |
| 2016-01-08 13:16:46 | 1 |
| 2016-01-08 13:17:00 | 2 |
| 2016-01-08 13:17:16 | 1 |
| 2016-01-08 13:17:31 | 1 |
| 2016-01-08 13:17:46 | 0 |
| 2016-01-08 13:18:01 | 0 |
| 2016-01-08 13:18:16 | 0 |
| 2016-01-08 13:18:31 | 0 |
| 2016-01-08 13:18:41 | 0 |
| 2016-01-08 13:19:01 | 0 |
| 2016-01-08 13:19:16 | 0 |
| 2016-01-08 13:19:27 | 0 |
| 2016-01-08 13:19:41 | 0 |
| 2016-01-08 13:19:56 | 0 |
| 2016-01-08 13:20:11 | 0 |
| 2016-01-08 13:20:26 | 0 |
| 2016-01-08 13:20:41 | 0 |
| 2016-01-08 13:20:56 | 0 |
| 2016-01-08 13:21:16 | 0 |
| 2016-01-08 13:21:31 | 0 |
| 2016-01-08 13:21:43 | 0 |
| 2016-01-08 13:22:01 | 0 |
| 2016-01-08 13:22:16 | 0 |
| 2016-01-08 13:22:31 | 0 |
| 2016-01-08 13:22:46 | 0 |
| 2016-01-08 13:23:01 | 0 |
| 2016-01-08 13:23:12 | 0 |
| 2016-01-08 13:23:31 | 0 |
| 2016-01-08 13:23:46 | 2 |
| 2016-01-08 13:24:01 | 2 |
| 2016-01-08 13:24:16 | 2 |
| 2016-01-08 13:24:31 | 1 |
| 2016-01-08 13:24:46 | 2 |
| 2016-01-08 13:25:00 | 2 |
现在,我只需要添加
portNo1
值不是0
的时间这意味着它应将以秒为单位的时间从
13:15:46
添加到13:17:31
,然后在此之后直到13:23:46
值为0
的所有记录都应忽略该时间并继续。然后再次从13:23:46
具有portNo1
值,它应该取13:23:46
和13:23:31
之间的差并将其添加到先前的差中。伙计们,我在perl中尝试了很多,但如果mysql中有任何简单的方法,都无法获得正确的值,请告诉我。 最佳答案
除非我完全理解问题,否则您可以执行以下操作:
SELECT SUM(UNIX_TIMESTAMP(date_time)-UNIX_TIMESTAMP(prevtime))
FROM (
SELECT date_time, portNo1, @prevTime as prevtime,
@prevTime := date_time
FROM MyTable
) t1
WHERE (portNo1 > 0 AND prevtime IS NOT null)
如果当前行中的
portNo1
大于0,这将求和当前行与上一行之间的时间差(以秒为单位)。这是您想要的吗?我在这里做了一个小提琴:http://sqlfiddle.com/#!9/b6dfa/3