SQL Datediff——在行之间查找Datediff

I would like to query a database using sql to show the difference in time between id 1,2,3 and so on. basically it will compare the row below it for all records. any help would be appreciated.

我想使用sql查询一个数据库，以显示id 1、2、3等之间的时间差异。基本上，它将对下面的行进行所有记录的比较。如有任何帮助，我们将不胜感激。

IDCODE  DATE TIME        DIFFERENCE (MINS)
1      02/03/2011 08:00        0
2      02/03/2011 08:10        10
3      02/03/2011 08:23        13
4       02/03/2011 08:25        2
5       02/03/2011 09:25        60
6       02/03/2011 10:20        55
7       02/03/2011 10:34        14

Thanks!

谢谢!

2 个解决方案

#1

If using SQL Server, one way is to do:

如果使用SQL Server，一种方法是:

DECLARE @Data TABLE (IDCode INTEGER PRIMARY KEY, DateVal DATETIME)
INSERT @Data VALUES (1, '2011-03-02 08:00')
INSERT @Data VALUES (2, '2011-03-02 08:10')
INSERT @Data VALUES (3, '2011-03-02 08:23')
INSERT @Data VALUES (4, '2011-03-02 08:25')
INSERT @Data VALUES (5, '2011-03-02 09:25')
INSERT @Data VALUES (6, '2011-03-02 10:20')
INSERT @Data VALUES (7, '2011-03-02 10:34')

SELECT t1.IDCode, t1.DateVal, ISNULL(DATEDIFF(mi, x.DateVal, t1.DateVal), 0) AS Mins
FROM @Data t1
    OUTER APPLY (
        SELECT TOP 1 DateVal FROM @Data t2 
        WHERE t2.IDCode < t1.IDCode ORDER BY t2.IDCode DESC) x

Another way is using a CTE and ROW_NUMBER(), like this:

另一种方法是使用CTE和ROW_NUMBER()，例如:

;WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY IDCode) AS RowNo, IDCode, DateVal FROM @Data)

SELECT t1.IDCode, t1.DateVal, ISNULL(DATEDIFF(mi, t2.DateVal, t1.DateVal), 0) AS Mins
FROM CTE t1
    LEFT JOIN CTE t2 ON t1.RowNo = t2.RowNo + 1
ORDER BY t1.IDCode

#2

Standard ANSI SQL solution. Should work in PostgreSQL, Oracle, DB2 and Teradata:

ANSI SQL标准的解决方案。应该在PostgreSQL、Oracle、DB2和Teradata中工作:

SELECT idcode, 
       date_time, 
       date_time - lag(date_time) over (order by date_time) as difference
FROM your_table

#1