首页 > 解决方案 > Where 子句改变了我的 datediff 列的结果,我该如何解决这个问题?

问题描述

我正在尝试获取 st1=5 时经过的时间。这是我目前拥有的,它为我提供了每个状态更改的 datediff 时间。我的问题是,当我添加 where st1=5 子句时,datediff 显示状态 = 5 的实例之间的时间差异,而不是状态为 5 的经过时间。

select timestamp,st1,st2,st3,st4,
  datediff(second, timestamp, lead(timestamp) 
    over (order by timestamp)) as timediff
from A6K_status
Order By Timestamp DESC


+-----+-----+-----+-----+---------------------+----------+
| st1 | st2 | st3 | st4 | TimeStamp           | TimeDiff |
+-----+-----+-----+-----+---------------------+----------+
| 3   | 3   | 3   | 3   | 2018-07-23 07:51:06 |          |
+-----+-----+-----+-----+---------------------+----------+
| 5   | 5   | 5   | 5   | 2018-07-23 07:50:00 | 66       |
+-----+-----+-----+-----+---------------------+----------+
| 0   | 0   | 10  | 10  | 2018-07-23 07:47:19 | 161      |
+-----+-----+-----+-----+---------------------+----------+
| 5   | 5   | 5   | 5   | 2018-07-23 07:39:07 | 492      |
+-----+-----+-----+-----+---------------------+----------+
| 3   | 3   | 10  | 10  | 2018-07-23 07:37:48 | 79       |
+-----+-----+-----+-----+---------------------+----------+
| 3   | 3   | 10  | 10  | 2018-07-23 07:37:16 | 32       |
+-----+-----+-----+-----+---------------------+----------+

我试图总结station1的状态为5的时间。从上面的这张表(我现在拥有的)中,如果我可以将timediff相加,其中st1 = 5可以完美地工作。但是通过在我的查询中添加“where st1=5”可以得到 state = 5 的实例之间的时间差。

任何帮助将非常感激。我觉得非常接近我想要达到的结果。谢谢。

编辑这是我想要实现的

+-----+------------+----------+
| st1 | TimeStamp  | TimeDiff |
+-----+------------+----------+
| 5   | 2018-07-23 | 558      |
+-----+------------+----------+

标签: sqlsql-serversubquerydatediff

解决方案


您将使用子查询(或 CTE):

select sum(timediff)
from (select timestamp, st1, st2, st3, st4,
             datediff(second, timestamp, lead(timestamp) over (order by timestamp)) as timediff
      from A6K_status
     ) s
where st1 = 5;

推荐阅读