drop table #1
drop table #2
create table #1  (tid int ,code int ,qty int )
insert #1 values(1,1,100)
insert #1 values(2,1,20)
insert #1 values(3,2,10)
insert #1 values(4,2,100)
insert #1 values(5,2,200)
insert #1 values(6,3,200)
create table #2(code int , sqty int)
insert #2 values(1,80)
insert #2 values(2,300)
insert #2 values(3,10)

update #1 set qty =
--select
--*
(case when amount < 0 then 0 when amount > #1.qty then #1.qty else amount end)
from #1,
(select tid , code , qty,-tqty as amount
--(case when abs(tqty) < qty then abs(tqty) else qty end)
from ( 
select #1.* , #2.sqty, #2.sqty - 
(select isnull(sum(qty),0) from #1 c where c.tid <= #1.tid and c.code = #1.code ) as Tqty from #1 left join #2 on #1.code = #2.code ) x
--where tqty <0 )
) d where #1.tid = d.tid

select * from #1

发表于 @ 2005年08月26日 11:18:00|评论(loading...)|编辑