sql 查询 某列数据 相邻行的间隔值

sql 查询 某列数据 相邻行的间隔值

WITH test (年,月,日,气温) AS (
    SELECT 2009,1,1,6.8 UNION ALL
    SELECT 2009,1,2,6.1 UNION ALL
    SELECT 2009,1,3,5.6 UNION ALL
    SELECT 2009,1,4,4.7 UNION ALL
    SELECT 2009,1,5,4 UNION ALL
    SELECT 2009,1,6,3 UNION ALL
    SELECT 2009,1,7,4.5 UNION ALL
    SELECT 2009,1,8,8.9 UNION ALL
    SELECT 2009,1,9,11.2 UNION ALL
    SELECT 2009,1,10,8.7
)
SELECT t1.*,
       t1.气温 - t0.气温 与昨日温差
  FROM test t1
LEFT JOIN test t0
       ON DATEDIFF(day, 
                   DateAdd(day,t0.日-1,DateAdd(month,t0.月-1,DateAdd(year,t0.年-1900,'1900-01-01'))),
                   DateAdd(day,t1.日-1,DateAdd(month,t1.月-1,DateAdd(year,t1.年-1900,'1900-01-01')))
                  ) = 1
	 

查询结果:

年           月           日           气温                                      与昨日温差
----------- ----------- ----------- --------------------------------------- ---------------------------------------
2009        1           1           6.8                                     NULL
2009        1           2           6.1                                     -0.7
2009        1           3           5.6                                     -0.5
2009        1           4           4.7                                     -0.9
2009        1           5           4.0                                     -0.7
2009        1           6           3.0                                     -1.0
2009        1           7           4.5                                     1.5
2009        1           8           8.9                                     4.4
2009        1           9           11.2                                    2.3
2009        1           10          8.7                                     -2.5

(10 行受影响)

实际情况可以参考如此思路。

偶的实际截图：

偶先将 bloodSugarValue 按时间作升序排列，并打上行标记， 然后sql自动计算相邻行的差值 ，赋给 LogDataAscGapValue.

(结束)