根据条件 求相邻的两条数据的某字段的差

请高手帮忙！！！
state的值一样的时候求表中相邻的两条记录的某字段的值之差

表字段 id money state
  1 22.2 0
  5 20 0
  6 35 0
  7 30 1
  8 50 1
  12 30 0
  16 20 1
state的值要一样 第二条减第一条，第三条减第四条，第六条减第五条
求实例.....

你并没有给出一个有规律的减法需求,就这个数据,你说说该得到什么样的结果吧!

帮顶，是不是用窗口函数来实现

用游标做,将差值插入只有一个字段的表中.

SQL code

create table tmp
(
    id int null,
    [money] money null,
    state int null
)
insert into tmp 
select 1,22.2,0
union all select 5,20,0
union all select 6,35,0
union all select 7,30,1
union all select 8,50,1
union all select 12,30,0
union all select 16,20,1

create procedure substract
    @state int
as
begin
    select d.aid,c.amoney,c.sub  from
    (select c.aid,MIN(c.bid) as cbid from (
    select a.money as amoney,a.id as aid,b.id as bid,b.money-a.money as sub from 
    (select ID,[money],state from tmp where state=@state) a,
    (select ID,[money],state from tmp where state=@state) b) c
    where c.bid>c.aid
    group by c.aid) d,
    (select a.money as amoney,a.id as aid,b.id as bid,b.money-a.money as sub from 
    (select ID,[money],state from tmp where state=@state) a,
    (select ID,[money],state from tmp where state=@state) b) c
    where c.aid=d.aid and c.bid=d.cbid
end



--结果
select * from tmp where state=0
exec substract 0


id          money                 state
----------- --------------------- -----------
1           22.20                 0
5           20.00                 0
6           35.00                 0
12          30.00                 0

(4 行受影响)

aid         amoney                sub
----------- --------------------- ---------------------
1           22.20                 -2.20
5           20.00                 15.00
6           35.00                 -5.00

(3 行受影响)