SQL数据同步问题,高手过来帮忙啊!(200分)(200分)

功能介绍同步库存[tDepotGoods.sum(ItemQty) =tDepotVendor.sum(curqty)]<br>&nbsp; 就举例说明:<br>&nbsp; &nbsp;tDepotGoods表 &nbsp;ItemNo &nbsp; &nbsp; ItemQty(库存)<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; &nbsp; 10<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; &nbsp; 60<br><br>&nbsp; tDepotVendor表 &nbsp;ItemNo &nbsp;BatchNo &nbsp; ItemQty &nbsp; &nbsp;CurQty<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; 001 &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; 50<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; 002 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; 20<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; 003 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; 30<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; 001 &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; 50<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; 002 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; 20<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; 003 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; 30<br>现因没同步,主要想把CurQty字段进行同步!正确结果应为:<br>&nbsp; tDepotVendor表 &nbsp;ItemNo &nbsp;BatchNo &nbsp; ItemQty &nbsp; &nbsp;CurQty<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; 001 &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; 0<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; 002 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; 0<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; 003 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; 10<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -------------------------------------10<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; 001 &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; 10<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; 002 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; 20<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; 003 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; 30<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -------------------------------------60<br>想用SQL的游标写出来!按BatchNo批次扣减,先进先出的原则!分数不够再加!

有点问题 &nbsp; ,别人怎么知道那个BATCHNO要分配多少??数据从那里来?<br>用游标肯定可以做出来.<br>但我觉得在数据库中在进出仓的时候直接用触发器什么都解决了.

楼上的大哥,请看清楚!数据就是从tDepotGoods 表来,就是取出A商品的ItemQty=10来,将<br>tDepotVendor表中的CurQty总和应同tDepotGoods 一致!并且要符合先进先出!<br>&nbsp;同步完后才可以在进出仓解决的!

要么在Client写代码，要么用触发器，就这两种方法了。。。

再顶一下!请大家帮忙啊!

自己搞定了!写得不好<br>先 update tDepotVendor set curqty=0 ,再同步<br>declare @itemno varchar(6) ,@itemqty numeric(12,4),@CurQty numeric(12,4),@BatchNo varchar(15), @sumqty numeric(12,4),@IsExec Bit<br><br>declare tDepotGoods_cursor cursor for select itemno,itemqty from tDepotGoods where depotcode='001'<br>open tDepotGoods_cursor fetch next from tDepotGoods_cursor into &nbsp;@itemno,@itemqty<br>while @@fetch_status =0 <br>begin<br>&nbsp; &nbsp; select @IsExec =1<br>&nbsp; &nbsp; select @SumQty=isnull(sum(itemqty),0) from tDepotVendor where itemno=@itemno<br>&nbsp; &nbsp; if @SumQty&gt;0<br>&nbsp; &nbsp; begin<br> declare tDepotVendor_cursor cursor for select itemno,BatchNo,itemqty &nbsp;from tDepotVendor where itemno=@itemno order by BatchNo Desc<br> open tDepotVendor_cursor fetch next from tDepotVendor_cursor into &nbsp;@itemno,@BatchNo,@CurQty<br> while @@fetch_status =0 <br> begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if @IsExec = 1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; begin &nbsp; <br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if (@itemqty &lt;@CurQty)<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;update tDepotVendor set curqty=@itemqty where BatchNo=@BatchNo and Itemno=@itemno<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;select @IsExec = 0<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;update tDepotVendor set curqty=@CurQty where BatchNo=@BatchNo and Itemno=@itemno<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;select @itemqty=@itemqty-@CurQty<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end<br> &nbsp;fetch next from tDepotVendor_cursor into &nbsp;@itemno,@BatchNo,@CurQty<br> end<br> close &nbsp;tDepotVendor_cursor<br> deallocate tDepotVendor_cursor &nbsp; <br>&nbsp; &nbsp; end <br>&nbsp; fetch next from tDepotGoods_cursor into &nbsp;@itemno,@itemqty<br>end<br>close &nbsp;tDepotGoods_cursor<br>deallocate tDepotGoods_cursor

送分了,顶着有分啊!

有参考价值！

顶

既然解决了，那就顶会吧。

接分

结贴!