我有一张桌子,其结构如下

 +---------------------+---------+
| 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:4613: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

10-01 15:47
查看更多