sql ²éѯÏȽøÏȳö

declare @tb3 table (ÉÌÆ·±àºÅ nvarchar(10),Åú´ÎºÅ nvarchar(10),¿â´æÊýÁ¿ int,³ö¿âÊýÁ¿ int)
declare @tb1 table (ÉÌÆ·±àºÅ nvarchar(10),Åú´ÎºÅ nvarchar(10),¿â´æÊýÁ¿ int)
insert into @tb1 select '0001','090801',200
      union all  select '0001','090501',50
      union all  select '0002','090101',30
      union all  select '0002','090701',200
declare @tb2 table (ÉÌÆ·±àºÅ nvarchar(10),¶©»õÊýÁ¿ int)
insert into @tb2 select '0001',60
      union all  select '0002',20
--declare @var int
declare c_sor cursor
for
select ÉÌÆ·±àºÅ,sum(¶©»õÊýÁ¿)¶©»õÊýÁ¿ from @tb2 group by ÉÌÆ·±àºÅ
declare @bh nvarchar(10), @dh int,@bh1 nvarchar(10),@pc nvarchar(10), @kc int
open  c_sor
fetch next from c_sor into @bh,@dh
while @@fetch_status=0
begin
declare sor cursor for select * from @tb1 where ÉÌÆ·±àºÅ=@bh order by Åú´ÎºÅ
open sor
fetch next from sor into @bh1,@pc,@kc
insert into @tb3 select @bh, @pc,@kc,case when @kc>@dh then @dh else @kc end
set @dh=@dh-@kc
while @dh>0
 begin
 fetch next from sor into @bh1,@pc,@kc
 insert into @tb3 select @bh, @pc,@kc,case when @kc>=@dh then @dh else @kc end
 set @dh=@dh-@kc
 end
close sor
deallocate sor
fetch next from c_sor into @bh,@dh
end
close c_sor
deallocate c_sor
select * from @tb3  order by ÉÌÆ·±àºÅ,Åú´ÎºÅ desc