34,838
社区成员




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
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