这个sql语句咋整啊？

[iamwho]

这个表
fee id difference
100 135 0
200 136 0
287 135 0
422 136 0
400 137 0

类似这样的记录，需要做的的是，把id相同的记录的后一条的fee字段的值减去前一个记录的值，并把这个值保存在前一个的difference字段中，执行完的之后的值应该是这样的

fee id difference
100 135 187
200 136 222
287 135 0
422 136 0
400 137 0

各位高手，帮帮我，先谢过了

[playyuer]

后一条的fee字段的值减去前一个记录的值!

后一条的标准是啥?
如果是以录入先后为顺序,最好再有一个自动号!

[programbcb]

最好在录入时就确定

[stone_lin]

9494,首先要知道按什么排序

[pengdali]

create table # (fee int,id int,difference int)
insert # values(100, 135 , 0)
insert # values(200 , 136 , 0)
insert # values(287 , 135 , 0)
insert # values(422 ,136 , 0)
insert # values(400 ,137, 0 )

ALTER TABLE # ADD 编号1 bigint identity(1,1) not null



exec('update # set difference=isnull((select fee from # a where a.id=#.id and 编号1=(select min(编号1) from # b where b.id=#.id and 编号1>#.编号1 ))-fee,0)')

select * from #
go
drop table #

[pengdali]

create table # (fee int,id int,difference int)
insert # values(100, 135 , 0)
insert # values(200 , 136 , 0)
insert # values(287 , 135 , 0)
insert # values(422 ,136 , 0)
insert # values(400 ,137, 0 )

ALTER TABLE # ADD 编号1 bigint identity(1,1) not null

exec('update # set difference=isnull((select fee from # a where a.id=#.id and 编号1=(select min(编号1) from # b where b.id=#.id and 编号1>#.编号1 ))-fee,0)')

ALTER TABLE # DROP COLUMN 编号1

select * from #
go
drop table #

[happydreamer]

update #
set difference=a.diff
from
(select min(a.编号1) 编号1,a.id,sum(a.fee-b.fee) diff
from # a join # b on a.id=b.id and a.编号1>=b.编号1
group by a.id )a
where #.编号1=a.编号1

[CrazyFor]

TRY:
select identity(int,1,1) as iid,fee,cast(id as int) id,difference into #temp from table
select a.fee,a.id,(case when b.fee is not null then b.fee-a.fee else 0 end) as difference from #temp a left join #temp b on a.id=b.id and a.iid<b.iid