datediff大于

Question

我有一个跟踪网络中断的数据库，我想运行一个报告，显示持续时间超过30分钟的中断。我可以让它显示所有的停机，但我不确定如何让它只显示那些超过30分钟的停机。这就是我到目前为止所拥有的。

SELECT 
  StartTime.EventTime, 
  Nodes.NodeId,
  Nodes.Caption, 
  Nodes.Location,
  StartTime.Message, 
  DATEDIFF(Mi, StartTime.EventTime, 
    (SELECT TOP 1 EventTime 
     FROM Events AS Endtime 
     WHERE EndTime.EventTime > StartTime.EventTime  AND EndTime.EventType = 5
       AND EndTime.NetObjectType = 'N' 
       AND EndTime.NetworkNode = StartTime.NetworkNode 
     ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes
FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID 
WHERE (StartTime.EventType = 1) 
  AND (StartTime.NetObjectType = 'N') 
  AND eventtime between dateadd(day, -1, getdate()) and getdate()
ORDER BY Nodes.Caption ASC

我得到的结果如下所示。我已经删除了一些信息。

Time of Outage      Name    Location Down Event          Outage Duration
8/3/2012 4:14       x       x        Stopped Responding                2
8/4/2012 5:14       x       x        Stopped Responding                 
8/5/2012 4:14       x                Stopped Responding               52
8/6/2012 4:14       x       x        Stopped Responding               20

Answer 1

一种方法是使查询成为子查询或CTE：

with q as (<your query here without the order by>)
select *
from q
where q.OutageDuragtionInMinutes > 30
order by Caption