SQL Datediff - 查找行之间的日期差异

Question

我想使用 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

谢谢！

- Tyrone2011

---

抱歉，Microsoft SQL Server管理工具。 - Tyrone2011

Answer 1

如果使用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

另一种方法是使用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

- AdaTheDev

---

我喜欢第一个，它为我节省了很多麻烦，但我的CTE经验不好。对于大表，它们会对数据库造成太大的资源压力。 - stonypaul

有没有一种方法可以在IDCODE相同的两个日期上添加计数，并获取这两行具有相同IDCODE的差异，而不是增量方式？例如：假设前两个日期的IDCODE为1，因此，我只想获取这两行之间的日期差异。 - Nitin Deb

Answer 2

标准的 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

- user330315

Answer 3

正如@a_horse_with_no_name所提到的，使用lag()函数。

我想要以毫秒为单位得到差异。

Select idcode,
       datetime,
       Difference = datediff(millisecond, lag(convert(datetime2,datetime)) over (order by convert(datetime2,datetime)),convert(datetime2,datetime))
From your_table

在我的情况下，我需要将一个字符串转换为datetime2。

- Timmy B