各位大哥，现有两个临时表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