SQL查询获取最后两条记录的DateDiff

Question

我有一个名为“Event”的表，其中 eventNum 作为主键，日期作为 SQL Server 2008 R2 中的 datetime2(7)。我试图获取表中最后两行的日期并以分钟为单位获取差异。这是我目前拥有的:

Select DATEDIFF(MI, e.date,(Select e2.date from Event e2 where eventNum = (Select MAX(e2.eventNum))))
    From Event e
    Where eventNum = (Select MAX(e.eventNum)-1 from e)

我收到此错误:

Invalid column name 'Select eventNum from Event Where eventNum = Select MAX(eventNum) from Event'.

我已经改变了 100 次，但无法让它工作。有什么帮助吗？

Answer 1

您可以使用ROW_NUMBER

WITH CTE AS 
(
   SELECT RN = ROW_NUMBER() OVER (ORDER BY eventNum DESC)
        , date
   FROM Event 
)
SELECT Minutes = DATEDIFF(minute, 
           (SELECT date FROM CTE WHERE RN = 2),
           (SELECT date FROM CTE WHERE RN = 1))

fiddle :http://www.sqlfiddle.com/#!3/3e9c8/17/0