SQL Datediff - 在行之间查找 datediff
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5728602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Datediff - find datediff between rows
提问by Tyrone2011
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!
谢谢！
回答by AdaTheDev
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
回答by a_horse_with_no_name
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
