求教：Sql如何数据库求相邻两行的差值？

[zczhi_121]

现有表table
----------------------------------------------------------
年 月 日 气温
2009 1 1 6.8
2009 1 2 6.1
2009 1 3 5.6
2009 1 4 4.7
2009 1 5 4
2009 1 6 3
2009 1 7 4.5
2009 1 8 8.9
2009 1 9 11.2
2009 1 10 8.7
---------------------------------------------------------
通过运算求相邻两列行中“气温”的差值，即得到以下表：
----------------------------------------------------------
年 月 日 气温 与昨日温差
2009 1 1 6.8
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.7
2009 1 6 3 -1
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
---------------------------------------------------------
请问该如何实现？最好能附代码，谢谢

[--小F--]

select
   a.*,b.气温-a.气温 as 与昨日温差
from
   (select *,row_number()over(order by getdate()) as id,* from tb) as a
inner join
   (select *,row_number()over(order by getdate()) as id,* from tb) as b
on
   a.id=b.id-1

--TRY

[Tiger_Zhao]

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

[zczhi_121]

楼上正解，谢谢！结贴给分！