除了用游标，应该怎样写SQL语句？
各位大哥，现有两个临时表AA1、AA2，AA1的内容是：  
编号 批次 库存 最大库存量 需补量  
001 A0001 10 100 90  
002 B0001 20 200 180 
003 C0001 30 300 270 

AA2的内容是：  
编号 批次 库存  
001 A0002 50  
001 A0003 10  
001 A0004 100  
001 A0005 200  
002 B0002 200 
003 C0002 100 
003 C0003 50 
003 C0004 200 

除了用游标，可以怎样写SQL语得到以下的结果？  
编号 批次 库存 实补量  
001 A0002 50 50  
001 A0003 10 10  
001 A0004 100 30  
002 B0002 200 180 
003 C0002 100 100 
003 C0003 50 50 
003 C0004 200 120 


------解决方案--------------------
实补量 咋算的？
------解决方案--------------------
create table AA1(id varchar(10),p_id varchar(10),inv int,max_inv int, need int)
insert into AA1 select '001','A0001',10,100,90
insert into AA1 select '002','B0001',20,200,180
insert into AA1 select '003','C0001',30,300,270

create table AA2(id varchar(10),p_id varchar(10),inv int)
insert into AA2 select '001','A0002',50
insert into AA2 select '001','A0003',10
insert into AA2 select '001','A0004',100
insert into AA2 select '001','A0005',200
insert into AA2 select '002','B0002',200
insert into AA2 select '003','C0002',100
insert into AA2 select '003','C0003',50
insert into AA2 select '003','C0004',200


select tmp=identity(int,1,1) ,*,act=0 into #t from AA2 order by id,p_id

update A
set act=case when isnull((select sum(inv) from #t where tmp<=A.tmp and id=A.id),0)<=isnull(B.need,0)
then A.inv
else case when isnull((select sum(inv) from #t where tmp<A.tmp and id=A.id),0)>=isnull(B.need,0)
then 0 
else isnull(B.need,0)-isnull((select sum(inv) from #t where tmp<A.tmp and id=A.id),0) end 
end

from #t A,AA1 B
where A.id=B.id 

select id,p_id,inv,act from #t where act>0
/*
id p_id inv act
---------- ---------- ----------- ----------- 
001 A0002 50 50
001 A0003 10 10
001 A0004 100 30
002 B0002 200 180
003 C0002 100 100
003 C0003 50 50
003 C0004 200 120
*/

drop table AA1,AA2,#t
------解决方案--------------------
是啊。兄弟，具体点，这样才好回答啊。
------解决方案--------------------
--直接select
select * from 
(
select A.id,A.p_id,A.inv,
case when isnull((select sum(inv) from AA2 where id=A.id and p_id<=A.p_id),0)<=isnull(B.need,0)
then A.inv
else case when isnull((select sum(inv) from AA2 where p_id<A.p_id and id=A.id),0)>=isnull(B.need,0)
then 0 
else isnull(B.need,0)-isnull((select sum(inv) from AA2 where p_id<A.p_id and id=A.id),0) end 
end as act
from AA2 A,AA1 B
where A.id=B.id
) T
where act>0
/*
id p_id inv act
---------- ---------- ----------- ----------- 
001 A0002 50 50
001 A0003 10 10
001 A0004 100 30
002 B0002 200 180
003 C0002 100 100
003 C0003 50 50