sql查询基于日期时间的当前行和上一行之间的差异 [英] sql query for difference between current row and previous row based on datetime

问题描述

我在sql查询中有一个小问题。我正在使用SQL Server 2008数据库，我想基于 datetime 计算当前行与上一行的columnValues值之间的差异，在这种情况下，我需要通过 siteid ，<我需要获取数据的两天之内的strong>从日期和至今。表格如下：

i have a small issue in sql query. I am using SQL Server 2008 Database, i want to calculate the difference between current row and previous row of column cumulativeValue based on datetime in where condition i need to pass siteid, from date and todate in between days i need to get the data. the table is below:

ID        siteId                readtime                cumulativeValue
1       GAF1608187          2017-10-18 12:59:03.000         20
2       GAF1604620          2017-10-18 11:38:33.000         10
3       GAF1608187          2017-10-19 14:59:00.000         40
4       GAF1608187          2017-10-19 16:07:46.000         65
5       GAF1604620          2017-10-19 11:41:49.000         55
6       GAF1604620          2017-10-20 10:52:35.000         120
7       GAF1608187          2017-10-20 16:10:15.000         90
8       GAF1608187          2017-10-21 16:13:19.000         120
9       GAF1604620          2017-10-21 11:44:42.000         180
10      GAF1604620          2017-10-22 11:05:50.000         230

输出应为：

ID        siteId                readtime                cumulativeValue   difference

2       GAF1604620          2017-10-18 11:38:33.000         10              10  
5       GAF1604620          2017-10-19 11:41:49.000         55              45
6       GAF1604620          2017-10-20 10:52:35.000         120             75  
9       GAF1604620          2017-10-21 11:44:42.000         180             60
10      GAF1604620          2017-10-22 11:05:50.000         230             50

请在此查询中帮助我，谢谢：-）

Please help me in this query thank you :-)

推荐答案

在SQL Server 2012+，可以使用 lag（）。在SQL Server 2008中，使用 apply ：

In SQL Server 2012+, you can use lag(). In SQL Server 2008, use apply:

select t.*,
       coalesce(t.cumulativeValue - tprev.cumulativeValue, t.cumulativeValue) as diff
from t outer apply
     (select top 1 tprev.*
      from t tprev
      where tprev.siteId = t.siteId and tprev.readtime < t.readtime
      order by tprev.readtime desc
     ) tprev;

这篇关于sql查询基于日期时间的当前行和上一行之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！