同一表中两次的时差

时间：2021-05-29 18:01:36

(Please note that I have seen a similar question on * recently, however I can not find it anywhere - if anyone can find it please post the link)

(请注意我最近在*上看到过类似的问题,但我无法在任何地方找到它 - 如果有人能找到它,请发布链接)

I have a table that has a datetime field in it. For example:

我有一个表中有一个日期时间字段。例如:

Table data

DateTime date;
int number;

I need to find the difference between date in a row and date in the next row. I have tried with a query:

我需要找到行中的日期和下一行中的日期之间的区别。我试过一个查询:

select date as current_date, date - (select top 1 date where date > current_date from data) as time_difference from date

however this won't work, because of "current_date". Is there a way I can do this with one query?

但是由于“current_date”,这不起作用。有一种方法可以用一个查询来做到这一点吗?

1 个解决方案

#1

3  

SELECT  date AS current_date, next_date - date AS difference
FROM    mytable mo
CROSS APPLY
        (
        SELECT  TOP 1 date AS next_date
        FROM    mytable mi
        WHERE   mi.date > mo.date
        ORDER BY
                date
        ) q

#1

3  

SELECT  date AS current_date, next_date - date AS difference
FROM    mytable mo
CROSS APPLY
        (
        SELECT  TOP 1 date AS next_date
        FROM    mytable mi
        WHERE   mi.date > mo.date
        ORDER BY
                date
        ) q
标签：

相关文章