入库表(入库日期、入库单号、货品编号、名称规格,数量),
出库表(出库日期、出库单号、货品编号、名称规格,数量),
问题:当用户在输入出库货品编号时,系统会弹出一对话框,显示库存货品、规格及库存数,以前我每次弹出的内容由存储过程计算出(入仓-出仓),但现在用了一段时间后发现速度较慢了,请问题这个问题怎么解决,谢谢。
出库表(出库日期、出库单号、货品编号、名称规格,数量),
问题:当用户在输入出库货品编号时,系统会弹出一对话框,显示库存货品、规格及库存数,以前我每次弹出的内容由存储过程计算出(入仓-出仓),但现在用了一段时间后发现速度较慢了,请问题这个问题怎么解决,谢谢。
你要有一个库存表
表结构可以如下:
库存表(货品编号,数量)
每次入库就库存表++
每次出库就库存表--
查看库存就是 select from 库存表
还有,你的 入库表和出库表的货品编号要加索引。
库存表的货品编号要加主键
--刪除數據時,減少庫存數量
Update TA_STOGoodsBalance
Set STO_004 = isnull(STO_004,0) - TDel.Qty
FROM TA_STOGoodsBalance as TBal Inner Join
(Select INT_003, INT_004,INT_005 ,SUM(isnull(INT_006,0)) as Qty
From Deleted
Group by INT_003, INT_004,INT_005) as TDel
ON TBal.STO_001 = TDel.INT_003 and
TBal.STO_002 = TDel.INT_004 and
TBal.STO_003 = TDel.INT_005 --插入數據時,增加庫存數量
Update TA_STOGoodsBalance
Set STO_004 = isnull(STO_004,0) + TIns.Qty
FROM dbo.TA_STOGoodsBalance as TBal Inner Join
(Select INT_003, INT_004,INT_005 ,SUM(isnull(INT_006,0)) as Qty
From Inserted
Group by INT_003, INT_004,INT_005) as TIns
ON TBal.STO_001 = TIns.INT_003 and
TBal.STO_002 = TIns.INT_004 and
TBal.STO_003 = TIns.INT_005 Insert Into dbo.TA_STOGoodsBalance(STO_001,STO_002,STO_003,STO_004)
Select INT_003, INT_004, ltrim(rtrim(INT_005)) ,SUM(isnull(INT_006,0))
From Inserted
Where CheckSUM(INT_003,INT_004,ltrim(rtrim(INT_005)) ) NOT IN
(Select CheckSUM( STO_001, STO_002,ltrim(rtrim(STO_003)))
From dbo.TA_STOGoodsBalance )
Group by INT_003, INT_004,ltrim(rtrim(INT_005))