计算sql中条目之间的时间

Guys i have a table that has a column named time. It capture the time of each record entry in to the database. I want to query and return another column displaying the duration between one entry and the entry before it. Example, if i store record for john today at 12:00 pm, and then Ali at 1:10 pm, i want another column that will show 01:10:00 (i.e HH:MM:SS).

伙计们我有一个表有一个名为time的列。它捕获每个记录条目进入数据库的时间。我想查询并返回另一列，显示一个条目和它之前的条目之间的持续时间。例如，如果我今天中午12点为约翰存储记录，然后在下午1:10存储阿里，我想要另一列显示01:10:00（即HH：MM：SS）。

I understand i can query each column number as follows.

据我所知，我可以按如下方式查询每个列号。

SELECT ROW_NUMBER() OVER (ORDER BY [followuptime]) from [dbo].[FollowUp] .

i wanted to query the max row number AS follows but it fails and return error "windowed...."

我想查询最大行号AS，但它失败并返回错误“窗口....”

SELECT MAX(ROW_NUMBER() OVER (ORDER BY [followuptime])) from [dbo].[FollowUp] .

I wanted to use the DATEDIFF(interval,start_time,end_time); function of sql , but as it is now, I am stuck. Please would appreciate your help or any alternative.

我想使用DATEDIFF（interval，start_time，end_time）; sql的功能，但就像现在一样，我被卡住了。非常感谢您的帮助或任何其他选择。

2 个解决方案

#1

Since SQL-Server 2008R2 does not support LAG/LEAD you will need to do a self join using row_number to get the time from previous row:

由于SQL-Server 2008R2不支持LAG / LEAD，因此您需要使用row_number进行自联接以获取上一行的时间：

WITH OrderedResults AS
(   SELECT  [id],
            [followuptime],
            [remark],
            RowNumber = ROW_NUMBER() OVER (ORDER BY [followuptime]) 
    FROM    [dbo].[FollowUp]
)
SELECT  a.ID, 
        a.FollowUpTime, 
        a.Remark, 
        PreviousTime = b.FollowUpTime,
        MinutesDifference = DATEDIFF(MINUTE, b.FollowUpTime, a.FollowUpTime)
FROM    OrderedResults a
        LEFT JOIN OrderedResults b
            ON b.RowNumber = a.RowNumber - 1
ORDER BY a.FollowUpTime;

Example on SQL Fiddle

关于SQL小提琴的例子

#2

You may not apply MAX to ROW_NUMBER. Use a CTE and query that.

您可能不会将MAX应用于ROW_NUMBER。使用CTE并查询。

;WITH MyCTE AS 
(
    SELECT ROW_NUMBER() OVER (ORDER BY [followuptime]) AS RowNum
    FROM [dbo].[FollowUp]
)

SELECT MAX(RowNum)
FROM MyCTE

#1