sql 怎样进行相邻记录的值累加

[lsp69]

有以下数据表：
weight price ID cu
0.5 10 1 0
0.5 12 2 0
2.5 3 1 0.5
2.5 4 2 0.5
5.5 5 1 0.5
5.5 6 2 0.5

--怎样得出以下结果
weight price ID cu 结果
0.5 10 1 0 10
0.5 12 2 0 12
2.5 3 1 0.5 22 =10+[(2.5-0.5)/0.5]*3=上一条的结果+[(2.5-上一条相邻记录的wt 0.5)/cu 0.5]*price 3
2.5 4 2 0.5
5.5 5 1 0.5 55=22+[(5.5-2.5)/0.5]*5
5.5 6 2 0.5

即同一个ID的相邻记录的结果累加，第一条即最小weight的不需要累加，直接取其price做为结果

[唐诗三百首]

create table 数据表
(weight decimal(5,1),price int,ID int,cu decimal(5,1))

insert into 数据表
 select 0.5,10,1,0 union all
 select 0.5,12,2,0 union all
 select 2.5,3,1,0.5 union all
 select 2.5,4,2,0.5 union all
 select 5.5,5,1,0.5 union all
 select 5.5,6,2,0.5


with t as(
select *,rn=row_number() over(partition by ID order by getdate()) from 数据表),
u as(
select a.weight,a.price,a.ID,a.cu,a.rn,结果=cast(a.price as decimal(10,0))
  from t a
  where a.rn=1
union all
select a.weight,a.price,a.ID,a.cu,a.rn,结果=case when a.cu<>0 then cast(b.结果+((a.weight-b.weight)/a.cu)*a.price as decimal(10,0)) else 0 end
  from t a
  inner join u b on a.ID=b.ID and a.rn=b.rn+1
  where a.rn>1)
select weight,price,ID,cu,结果 from u order by ID,rn

/*
weight                                  price       ID          cu                                      结果
--------------------------------------- ----------- ----------- --------------------------------------- ---------------------------------------
0.5                                     10          1           0.0                                     10
2.5                                     3           1           0.5                                     22
5.5                                     5           1           0.5                                     52
5.5                                     6           2           0.5                                     6
2.5                                     4           2           0.5                                     -18
0.5                                     12          2           0.0                                     0

(6 row(s) affected)
*/