create table V_INSTORE (id int,storeid int,billdate datetime,goodsid int,qty decimal(8,1)) insert into V_INSTORE select 1,2,'2008-07-31',1,300.0 union all select 2,2,'2008-07-31',2,10.0 union all select 3,2,'2008-07-31',3,8.0 union all select 4,2,'2008-08-24',1,100.0 union all select 5,2,'2008-08-24',2,100.0 union all select 6,2,'2008-08-24',3,100.0 union all select 7,2,'2008-08-24',4,100.0 union all select 8,3,'2008-07-31',1,350 union all select 9,3,'2008-07-31',2,15.0 union all select 10,3,'2008-08-29',3,800.0create table V_OUTSTORE(storeid int,billdate datetime,goodsid int,qty decimal(8,1)) insert into V_OUTSTORE select 2,'2008-08-13',1,-300 union all select 2,'2008-08-23',1,-11.0 union all select 2,'2008-08-23',2,-12.0 union all select 3,'2008-08-13',1,-300.0 --select * from V_INSTORE godeclare @id int,@sid int,@qty decimal(8,1),@flg int declare decreasestore cursor for select storeid,goodsid,sum(qty) as qty from v_outstore group by storeid,goodsid open decreasestore fetch next from decreasestore into @id,@sid,@qty WHILE @@FETCH_STATUS = 0 BEGIN while @qty<0 begin update v_instore set qty=qty+@qty,@flg=qty where id in(select top 1 id from v_instore where storeid=@id and goodsid=@sid and qty>0) set @qty=@qty+@flg end fetch next from decreasestore into @id,@sid,@qty end close decreasestore DEALLOCATE decreasestore update v_instore set qty=0 where qty<0 go select storeid,billdate,goodsid,qty from V_INSTORE drop table V_INSTORE drop table V_OUTSTORE go /*storeid billdate goodsid qty ----------- ----------------------- ----------- --------------------------------------- 2 2008-07-31 00:00:00.000 1 0.0 2 2008-07-31 00:00:00.000 2 0.0 2 2008-07-31 00:00:00.000 3 8.0 2 2008-08-24 00:00:00.000 1 89.0 2 2008-08-24 00:00:00.000 2 98.0 2 2008-08-24 00:00:00.000 3 100.0 2 2008-08-24 00:00:00.000 4 100.0 3 2008-07-31 00:00:00.000 1 50.0 3 2008-07-31 00:00:00.000 2 15.0 3 2008-08-29 00:00:00.000 3 800.0(10 行受影响) */
谢谢你们的答复, update v_instore set qty=0 where qty<0请问能不能在不影响以下两个表的前提下 ( V_INSTORE ,V_OUTSTORE)把结果写入到表V_INOUT ??谢谢
insert into V_INSTORE select 1,2,'2008-07-31',1,300.0
union all select 2,2,'2008-07-31',2,10.0
union all select 3,2,'2008-07-31',3,8.0
union all select 4,2,'2008-08-24',1,100.0
union all select 5,2,'2008-08-24',2,100.0
union all select 6,2,'2008-08-24',3,100.0
union all select 7,2,'2008-08-24',4,100.0
union all select 8,3,'2008-07-31',1,350
union all select 9,3,'2008-07-31',2,15.0
union all select 10,3,'2008-08-29',3,800.0create table V_OUTSTORE(storeid int,billdate datetime,goodsid int,qty decimal(8,1))
insert into V_OUTSTORE select 2,'2008-08-13',1,-300
union all select 2,'2008-08-23',1,-11.0
union all select 2,'2008-08-23',2,-12.0
union all select 3,'2008-08-13',1,-300.0
--select * from V_INSTORE
godeclare @id int,@sid int,@qty decimal(8,1),@flg int
declare decreasestore cursor for
select storeid,goodsid,sum(qty) as qty from v_outstore group by storeid,goodsid
open decreasestore
fetch next from decreasestore into @id,@sid,@qty
WHILE @@FETCH_STATUS = 0
BEGIN
while @qty<0
begin
update v_instore set qty=qty+@qty,@flg=qty where id in(select top 1 id from v_instore where storeid=@id and goodsid=@sid and qty>0)
set @qty=@qty+@flg
end
fetch next from decreasestore into @id,@sid,@qty
end
close decreasestore
DEALLOCATE decreasestore
update v_instore set qty=0 where qty<0
go
select storeid,billdate,goodsid,qty from V_INSTORE
drop table V_INSTORE
drop table V_OUTSTORE
go
/*storeid billdate goodsid qty
----------- ----------------------- ----------- ---------------------------------------
2 2008-07-31 00:00:00.000 1 0.0
2 2008-07-31 00:00:00.000 2 0.0
2 2008-07-31 00:00:00.000 3 8.0
2 2008-08-24 00:00:00.000 1 89.0
2 2008-08-24 00:00:00.000 2 98.0
2 2008-08-24 00:00:00.000 3 100.0
2 2008-08-24 00:00:00.000 4 100.0
3 2008-07-31 00:00:00.000 1 50.0
3 2008-07-31 00:00:00.000 2 15.0
3 2008-08-29 00:00:00.000 3 800.0(10 行受影响)
*/
update v_instore set qty=0 where qty<0请问能不能在不影响以下两个表的前提下 ( V_INSTORE ,V_OUTSTORE)把结果写入到表V_INOUT ??谢谢
继续请教: 思路能不能进仓单 被出仓单 冲减后的明细就不再下次操作时重新重头进行,这样会不会快些?但是不知道怎么做哦