出货单、入货单的单价无所谓,我可以再写一条语句在商品表里算出来的。盘点的动作就是库存表.库存量=库存表.库存量+入货单.入货数量 where 库存表.商品ID=入货单.商品ID库存表.库存量=库存表.库存量-出货单.出货数量 where 库存表.商品ID=出货单.商品ID入货单.是否盘点=已盘点(此字段为bit型)出货单.是否盘点=已盘点最好能够返回入货单和出货单的记录条数。
库存表:Z 商品ID,库存量,商品单价,库存金额 ID CO PRICE AMOUNT 出货单:A 单号,商品ID,出货数量,出货金额,是否盘点 NO ID CO PRICE IS 入货单:B 单号,商品ID,入货数量,入货金额,是否盘点 NO ID CO PRICE ISUPDATE Z SET Z.CO = Z.CO - AA.CO , Z.AMOUNT = Z.AMOUNT - Z.PRICE * AA.CO FROM (SELECT SUM(CO) AS CO FROM A GROUP BY ID) AA WHERE Z.ID = AA.IDUPDATE A SET A.IS = '1' WHERE A.IS = '0'UPDATE Z SET Z.CO = Z.CO + BB.CO , Z.PRICE = ( Z.AMOUNT + BB.PRICE * BB.CO ) / ( Z.CO + BB.CO) , Z.AMOUNT = Z.AMOUNT + BB.PRICE * BB.CO FROM (SELECT SUM(CO) AS CO FROM B GROUP BY ID) BB WHERE Z.ID = BB.IDUPDATE B SET B.IS = '1' WHERE B.IS = '0'
请问 weixiao51(三土) AA和BB是什么东东??
库存量:UPDATE A SET A.库存量=ISNULL(A.库存量,0)+ISNULL(B.入货数量,0)-ISNULL(C.出货数量,0)from 库存表 A left join 入货单 B on B.商品ID=A.商品ID left join 出货单 C on C.商品ID=A.商品ID
使用事務: begin tran--入货盘点 UPDATE A SET A.库存量=ISNULL(A.库存量,0)+ISNULL(B.入货数量,0) from 库存表 A inner join 入货单 B on B.商品ID=A.商品ID WHERE B.是否盘点='N' --表未盘点--更換標志 UPDATE B SET B.是否盘点='Y' from 库存表 A inner join 入货单 B on B.商品ID=A.商品ID WHERE B.是否盘点='N' --表未盘点SELECT @@ROWCOUNT --返回更改記錄數 --出货盘点 UPDATE A SET A.库存量=ISNULL(A.库存量,0)-ISNULL(C.出货数量,0) from 库存表 A inner join 出货单 C on C.商品ID=A.商品ID WHERE C.是否盘点='N' --表未盘点UPDATE C SET C.是否盘点='Y' from 库存表 A inner join 出货单 C on C.商品ID=A.商品ID WHERE C.是否盘点='N' --表未盘点SELECT @@ROWCOUNT --返回更改記錄數 COMMIT
请问 progress99(如履薄冰) 库存量:UPDATE A SET A.库存量=ISNULL(A.库存量,0)+ISNULL(B.入货数量,0)-ISNULL(C.出货数量,0)from 库存表 A left join 入货单 B on B.商品ID=A.商品ID //联合表 left join 出货单 C on C.商品ID=A.商品ID //联合表但是ISNULL(B.入货数量,0)是什么东东? 就是后面那个0???
给你个比较好理解的create procedure 盘点 as begin tran declare @id int,@temp int declare csr1 cursor for select 商品ID from 库存表 open csr1FETCH NEXT FROM csr1 INTO @id while (@@FETCH_STATUS=0) BEGIN select @temp=isnull(sum(出货数量),0) from 出货单 where 商品ID =@id update 库存表 set 库存量=库存量-@temp where 商品ID=@id update 出货单 set 是否盘点=1 select @temp=isnull(sum(入货数量),0) from 入货单 where 商品ID =@id update 库存表 set 库存量=库存量+@temp where 商品ID=@id update 入货单 set 是否盘点=1
fetch next from csr1 into @id END CLOSE csr1 DEALLOCATE csr1 commit
--使用事務:SET XACT_ABORT ON --出錯就回滾 begin tranDECLARE @a numeric(12) DECLARE @b numeric(12)--入货盘点 UPDATE A SET A.库存量=ISNULL(A.库存量,0)+ISNULL(B.入货数量,0) from 库存表 A inner join 入货单 B on B.商品ID=A.商品ID WHERE B.是否盘点='N' --表未盘点 --更換標志 UPDATE B SET B.是否盘点='Y' from 库存表 A inner join 入货单 B on B.商品ID=A.商品ID WHERE B.是否盘点='N' --表未盘点set @a=@@ROWCOUNT --返回更改記錄數 --出货盘点 UPDATE A SET A.库存量=ISNULL(A.库存量,0)-ISNULL(C.出货数量,0) from 库存表 A inner join 出货单 C on C.商品ID=A.商品ID WHERE C.是否盘点='N' --表未盘点UPDATE C SET C.是否盘点='Y' from 库存表 A inner join 出货单 C on C.商品ID=A.商品ID WHERE C.是否盘点='N' --表未盘点set @b= @@ROWCOUNT --返回更改記錄數--返回入货单和出货单更改的记录条数 select @a,@b COMMIT
但是ISNULL(B.入货数量,0)是什么东东? 就是后面那个0???表:當B.入货数量為NULL值時,表達式返回0,在做數值型數據運算處理時,一般要進行NULL值處理,舉例:declare @a int declare @b int set @a=100 select @a+@b --返回NULL select @a+isnull(@b,0) --返回100 select @a+isnull(@b,100) --返回200
商品ID,库存量,商品单价,库存金额
ID CO PRICE AMOUNT
出货单:A
单号,商品ID,出货数量,出货金额,是否盘点
NO ID CO PRICE IS
入货单:B
单号,商品ID,入货数量,入货金额,是否盘点
NO ID CO PRICE ISUPDATE Z SET Z.CO = Z.CO - AA.CO
, Z.AMOUNT = Z.AMOUNT - Z.PRICE * AA.CO
FROM (SELECT SUM(CO) AS CO FROM A GROUP BY ID) AA
WHERE Z.ID = AA.IDUPDATE A SET A.IS = '1' WHERE A.IS = '0'UPDATE Z SET Z.CO = Z.CO + BB.CO
, Z.PRICE = ( Z.AMOUNT + BB.PRICE * BB.CO ) / ( Z.CO + BB.CO)
, Z.AMOUNT = Z.AMOUNT + BB.PRICE * BB.CO
FROM (SELECT SUM(CO) AS CO FROM B GROUP BY ID) BB
WHERE Z.ID = BB.IDUPDATE B SET B.IS = '1' WHERE B.IS = '0'
请问
weixiao51(三土) AA和BB是什么东东??
SET A.库存量=ISNULL(A.库存量,0)+ISNULL(B.入货数量,0)-ISNULL(C.出货数量,0)from 库存表 A
left join 入货单 B on B.商品ID=A.商品ID
left join 出货单 C on C.商品ID=A.商品ID
begin tran--入货盘点
UPDATE A
SET A.库存量=ISNULL(A.库存量,0)+ISNULL(B.入货数量,0)
from 库存表 A
inner join 入货单 B on B.商品ID=A.商品ID
WHERE B.是否盘点='N' --表未盘点--更換標志
UPDATE B
SET B.是否盘点='Y'
from 库存表 A
inner join 入货单 B on B.商品ID=A.商品ID
WHERE B.是否盘点='N' --表未盘点SELECT @@ROWCOUNT --返回更改記錄數
--出货盘点
UPDATE A
SET A.库存量=ISNULL(A.库存量,0)-ISNULL(C.出货数量,0)
from 库存表 A
inner join 出货单 C on C.商品ID=A.商品ID
WHERE C.是否盘点='N' --表未盘点UPDATE C
SET C.是否盘点='Y'
from 库存表 A
inner join 出货单 C on C.商品ID=A.商品ID
WHERE C.是否盘点='N' --表未盘点SELECT @@ROWCOUNT --返回更改記錄數
COMMIT
progress99(如履薄冰)
库存量:UPDATE A
SET A.库存量=ISNULL(A.库存量,0)+ISNULL(B.入货数量,0)-ISNULL(C.出货数量,0)from 库存表 A
left join 入货单 B on B.商品ID=A.商品ID //联合表
left join 出货单 C on C.商品ID=A.商品ID //联合表但是ISNULL(B.入货数量,0)是什么东东?
就是后面那个0???
as
begin tran
declare @id int,@temp int
declare csr1 cursor
for
select 商品ID from 库存表
open csr1FETCH NEXT FROM csr1 INTO @id
while (@@FETCH_STATUS=0)
BEGIN
select @temp=isnull(sum(出货数量),0) from 出货单 where 商品ID =@id
update 库存表 set 库存量=库存量-@temp where 商品ID=@id
update 出货单 set 是否盘点=1 select @temp=isnull(sum(入货数量),0) from 入货单 where 商品ID =@id
update 库存表 set 库存量=库存量+@temp where 商品ID=@id
update 入货单 set 是否盘点=1
fetch next from csr1 into @id
END
CLOSE csr1
DEALLOCATE csr1
commit
begin tranDECLARE @a numeric(12)
DECLARE @b numeric(12)--入货盘点
UPDATE A
SET A.库存量=ISNULL(A.库存量,0)+ISNULL(B.入货数量,0)
from 库存表 A
inner join 入货单 B on B.商品ID=A.商品ID
WHERE B.是否盘点='N' --表未盘点
--更換標志
UPDATE B
SET B.是否盘点='Y'
from 库存表 A
inner join 入货单 B on B.商品ID=A.商品ID
WHERE B.是否盘点='N' --表未盘点set @a=@@ROWCOUNT --返回更改記錄數
--出货盘点
UPDATE A
SET A.库存量=ISNULL(A.库存量,0)-ISNULL(C.出货数量,0)
from 库存表 A
inner join 出货单 C on C.商品ID=A.商品ID
WHERE C.是否盘点='N' --表未盘点UPDATE C
SET C.是否盘点='Y'
from 库存表 A
inner join 出货单 C on C.商品ID=A.商品ID
WHERE C.是否盘点='N' --表未盘点set @b= @@ROWCOUNT --返回更改記錄數--返回入货单和出货单更改的记录条数
select @a,@b
COMMIT
就是后面那个0???表:當B.入货数量為NULL值時,表達式返回0,在做數值型數據運算處理時,一般要進行NULL值處理,舉例:declare @a int
declare @b int
set @a=100
select @a+@b --返回NULL
select @a+isnull(@b,0) --返回100
select @a+isnull(@b,100) --返回200