sql server 2005的表更新问题

use tempdb
go

set nocount on
go

if object_id (N'mid', N'U') is not null
  begin
  drop table mid
  create table mid
  (
  bs varchar(1) not null,
  total decimal(19, 2) not null,
  )
  end
go

if object_id (N'goal', N'U') is not null
  begin
  drop table goal
  create table goal
  (
  xh int not null,
  bs varchar(1) not null,
  num decimal(19, 2) not null,
  )
  end
go

insert mid (bs, total) values ('a', 3000)
insert mid (bs, total) values ('b', 9000)
insert mid (bs, total) values ('c', 3000)
go

insert goal (xh, bs, num) values (1, 'a', 1000)
insert goal (xh, bs, num) values (2, 'a', 1000)
insert goal (xh, bs, num) values (3, 'a', 1000)
insert goal (xh, bs, num) values (6, 'b', 3000)
insert goal (xh, bs, num) values (7, 'b', 3000)
insert goal (xh, bs, num) values (8, 'b', 3000)
insert goal (xh, bs, num) values (11, 'b', 3000)
insert goal (xh, bs, num) values (16, 'c', 2000)
insert goal (xh, bs, num) values (18, 'c', 2000)
insert goal (xh, bs, num) values (19, 'd', 500)
go

select * from mid
select * from goal

-- 前提: mid表每个bs有个总量total, goal表每个bs有对应的分量, 而且有对应的非连续递增的xh
-- 问题: goal表如何按xh递减的顺序, 用mid表的total冲减其分量(mid表的总量<=goal表分量的和)(就是用mid表的total,去抵消goal表的num, 直到把total抵消成0, 按照goal表xh递减的顺序, 从大到小抵消...)
-- 最终结果:
-- goal表的数据
-- xh bs num
-- 1 a 0 -- 3.经过1和2.a初始total=1000,原num=1000=total=1000,所以total-1000=0,num=0
-- 2 a 0 -- 2.经过1,a初始total=2000,原num=1000<total=2000,所以total-1000=1000,num=0
-- 3 a 0 -- 1.a初始total=3000,原num=1000<total=3000,所以total-1000=2000,然后num=0
-- 6 b 3000 -- 此时total=0, num=3000>total=0,所以不改
-- 7 b 0 -- 同a的说明
-- 8 b 0 -- 同a的说明
-- 11 b 0 -- 同a的说明
-- 16 c 1000 -- 原num=2000>total=1000,所以total将原来的2000冲为0即可,最终total=0,num=1000
-- 18 c 0 -- 同a
-- 19 d 500 -- mid表没有d,不改


------解决方案--------------------

SQL code

declare mycursor cursor
for
select xh,bs,num from goal order by xh desc
open mycursor
declare @xh int,@bs char(1),@num int
fetch next from mycursor into @xh,@bs,@num
while @@fetch_status=0
begin
    declare @total int,@val int
    select @total=total from mid where bs=@bs
    if @total is not null
    begin
        if @total>=@num
            set @val=@num
        else
            set @val=@total
        update goal set num=num-@val where xh=@xh
        update mid set total=total-@val where bs=@bs
    end
    fetch next from mycursor into @xh,@bs,@num
end
close mycursor
deallocate mycursor

select * from mid
select * from goal


-------------------------------

bs   total
---- ---------------------------------------
a    0.00
b    0.00
c    0.00

(3 行受影响)

xh          bs   num
----------- ---- ---------------------------------------
1           a    0.00
2           a    0.00
3           a    0.00
6           b    3000.00
7           b    0.00
8           b    0.00
11          b    0.00
16          c    1000.00
18          c    0.00
19          d    500.00

(10 行受影响)

------解决方案--------------------

SQL code

with c as
(select a.rn,a.xh,a.bs,a.num,b.total
 from 
 (select row_number() over(partition by bs order by xh desc) rn,
  xh,bs,num from goal) a
 left join mid b on a.bs=b.bs
),
d as
(select c.xh,c.bs,
case when c.total is null then c.num
when (c.total-(select isnull(sum(c2.num),0) from c c2 where c2.bs=c