做过MIS/ERP系统里"库存"这块的朋友请进(问题1) 不要从“物料档案表”里了当前库存,我记得你是要多级库存的,可能不止一个仓库,另外用来来记每个物料在各个仓库的当前库存月库存表可以保留。日库存表就不必了,日库存表根据出入库单据和月库存表来实时计算得出来。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 不要从“物料档案表”里取当前库存,我记得你是要多级库存的,可能不止一个仓库,另外用表来记每个物料在各个仓库的当前库存。月库存表可以保留。日库存表就不必了,日库存表根据出入库单据和月库存表来实时计算得出来。 我做过的一个misproduct表( [productno] [char] (8) COLLATE Chinese_PRC_BIN NOT NULL , [pr_name] [char] (40) COLLATE Chinese_PRC_BIN NOT NULL , [vencode] [char] (13) COLLATE Chinese_PRC_BIN NULL , --供应商 [pr_style] [char] (1) COLLATE Chinese_PRC_BIN NOT NULL , [pr_buyer] [char] (4) COLLATE Chinese_PRC_BIN NOT NULL , [pr_zone] [char] (4) COLLATE Chinese_PRC_BIN NOT NULL , [purprice] [numeric](8, 2) NOT NULL , --采购价 [purcost] [numeric](10, 4) NOT NULL ,--成本 [wsaleprice] [numeric](10, 4) NOT NULL , [saletax] [numeric](4, 2) NOT NULL , [purtax] [numeric](4, 2) NOT NULL , [normaldis] [numeric](4, 2) NULL , [specdis] [numeric](4, 2) NULL , [agent_per] [numeric](4, 2) NULL , [commision] [numeric](7, 2) NULL , [stop_in] [char] (1) COLLATE Chinese_PRC_BIN NOT NULL ,--是否终止使用 [stoptime] [datetime] NULL , [newdate] [datetime] NOT NULL , [orderspecs] [char] (10) COLLATE Chinese_PRC_BIN NOT NULL , [orderunit] [char] (2) COLLATE Chinese_PRC_BIN NOT NULL , [order_per] [numeric](4, 0) NOT NULL , [outspecs] [char] (10) COLLATE Chinese_PRC_BIN NOT NULL , [outunit] [char] (2) COLLATE Chinese_PRC_BIN NOT NULL , [out_per] [numeric](4, 0) NOT NULL , [saleunit] [char] (2) COLLATE Chinese_PRC_BIN NOT NULL , [trade] [char] (3) COLLATE Chinese_PRC_BIN NOT NULL , [badtype] [tinyint] NOT NULL , [salegroup] [tinyint] NOT NULL , [pr_sign] [tinyint] NOT NULL , ) ON [PRIMARY]GO实时库存表 ( [deptno] [char] (5) COLLATE Chinese_PRC_BIN NOT NULL , [productno] [char] (8) COLLATE Chinese_PRC_BIN NOT NULL , [scost_tot] [numeric](14, 4) NOT NULL , [snotax_tot] [numeric](14, 4) NOT NULL , [amt] [numeric](9, 0) NOT NULL ,--库存数量 [shop_amt] [numeric](7, 2) NOT NULL , [clubprice] [numeric](7, 2) NOT NULL , [netprice] [numeric](7, 2) NOT NULL , [saleprice] [numeric](7, 2) NOT NULL , [savecost] [numeric](9, 4) NOT NULL , [take_or] [bit] NOT NULL , [salegrade] [char] (1) COLLATE Chinese_PRC_BIN NOT NULL , [si_safeamt] [numeric](7, 2) NULL , [safecycle] [tinyint] NULL , [bigestamt] [numeric](7, 2) NULL ) ON [PRIMARY]GO在库存表上建立触发器。把对库存的更新记录在一个log表中,可以方便对帐REATE TRIGGER td_tr ON dbo.库存表FOR INSERT, UPDATEAS declare @mcount int if @@rowcount=0 return if update(shop_amt) insert into log表 select a.shopno,a.productno,amt,0, scost_tot ,snotax_tot,savecost,0,0,0,0,0,saleprice,getdate(),user from inserted a return日志表 ( [deptno] [char] (5) COLLATE Chinese_PRC_BIN NOT NULL , [productno] [char] (8) COLLATE Chinese_PRC_BIN NOT NULL , [ware_amt] [numeric](9, 2) NOT NULL , [badamt] [numeric](7, 2) NOT NULL , [wcost_tot] [numeric](14, 4) NOT NULL , [wnotax_tot] [numeric](14, 4) NOT NULL , [wavecost] [numeric](9, 4) NOT NULL , [handlock] [numeric](7, 2) NOT NULL , [autolock] [numeric](7, 2) NOT NULL , [salegrade] [char] (1) COLLATE Chinese_PRC_BIN NOT NULL , [safe_amt] [numeric](7, 2) NOT NULL , [safecycle] [tinyint] NOT NULL , [price] [numeric](7, 0) NOT NULL , [mdate] [datetime] NOT NULL , [username] [char] (10) COLLATE Chinese_PRC_BIN NULL ) ON [PRIMARY] 请问这个查询已经如何实现? (700高分)求購一套從用戶需求調研到產品發布的開發管理文檔 (四) 关于索引的疑问,一个表上性别字段需要建立索引吗? 如何恢复analysis services 数据库 100分紧急求助。相烦进来看看~ access转换到SQL中碰到的问题 我想开发一个类似金山词霸的查询类的应用软件 数据库并发控制问题? 添加列引来的问题: 问一个关于班里年龄统计的算法! windows Server 2003 Enterprise Edition 版上装SQL SERVER2000不能创建库,有人遇到这个问题么? 关于异构数据库的访问
(
[productno] [char] (8) COLLATE Chinese_PRC_BIN NOT NULL ,
[pr_name] [char] (40) COLLATE Chinese_PRC_BIN NOT NULL ,
[vencode] [char] (13) COLLATE Chinese_PRC_BIN NULL , --供应商
[pr_style] [char] (1) COLLATE Chinese_PRC_BIN NOT NULL ,
[pr_buyer] [char] (4) COLLATE Chinese_PRC_BIN NOT NULL ,
[pr_zone] [char] (4) COLLATE Chinese_PRC_BIN NOT NULL ,
[purprice] [numeric](8, 2) NOT NULL , --采购价
[purcost] [numeric](10, 4) NOT NULL ,--成本
[wsaleprice] [numeric](10, 4) NOT NULL ,
[saletax] [numeric](4, 2) NOT NULL ,
[purtax] [numeric](4, 2) NOT NULL ,
[normaldis] [numeric](4, 2) NULL ,
[specdis] [numeric](4, 2) NULL ,
[agent_per] [numeric](4, 2) NULL ,
[commision] [numeric](7, 2) NULL ,
[stop_in] [char] (1) COLLATE Chinese_PRC_BIN NOT NULL ,--是否终止使用
[stoptime] [datetime] NULL ,
[newdate] [datetime] NOT NULL ,
[orderspecs] [char] (10) COLLATE Chinese_PRC_BIN NOT NULL ,
[orderunit] [char] (2) COLLATE Chinese_PRC_BIN NOT NULL ,
[order_per] [numeric](4, 0) NOT NULL ,
[outspecs] [char] (10) COLLATE Chinese_PRC_BIN NOT NULL ,
[outunit] [char] (2) COLLATE Chinese_PRC_BIN NOT NULL ,
[out_per] [numeric](4, 0) NOT NULL ,
[saleunit] [char] (2) COLLATE Chinese_PRC_BIN NOT NULL ,
[trade] [char] (3) COLLATE Chinese_PRC_BIN NOT NULL ,
[badtype] [tinyint] NOT NULL ,
[salegroup] [tinyint] NOT NULL ,
[pr_sign] [tinyint] NOT NULL ,
) ON [PRIMARY]
GO实时库存表 (
[deptno] [char] (5) COLLATE Chinese_PRC_BIN NOT NULL ,
[productno] [char] (8) COLLATE Chinese_PRC_BIN NOT NULL ,
[scost_tot] [numeric](14, 4) NOT NULL ,
[snotax_tot] [numeric](14, 4) NOT NULL ,
[amt] [numeric](9, 0) NOT NULL ,--库存数量
[shop_amt] [numeric](7, 2) NOT NULL ,
[clubprice] [numeric](7, 2) NOT NULL ,
[netprice] [numeric](7, 2) NOT NULL ,
[saleprice] [numeric](7, 2) NOT NULL ,
[savecost] [numeric](9, 4) NOT NULL ,
[take_or] [bit] NOT NULL ,
[salegrade] [char] (1) COLLATE Chinese_PRC_BIN NOT NULL ,
[si_safeamt] [numeric](7, 2) NULL ,
[safecycle] [tinyint] NULL ,
[bigestamt] [numeric](7, 2) NULL
) ON [PRIMARY]
GO在库存表上建立触发器。把对库存的更新记录在一个log表中,可以方便对帐
REATE TRIGGER td_tr ON dbo.库存表
FOR INSERT, UPDATE
AS
declare
@mcount int
if @@rowcount=0
return
if update(shop_amt)
insert into log表
select a.shopno,a.productno,amt,0, scost_tot ,snotax_tot,savecost,0,0,0,0,0,saleprice,getdate(),user
from inserted a return日志表 (
[deptno] [char] (5) COLLATE Chinese_PRC_BIN NOT NULL ,
[productno] [char] (8) COLLATE Chinese_PRC_BIN NOT NULL ,
[ware_amt] [numeric](9, 2) NOT NULL ,
[badamt] [numeric](7, 2) NOT NULL ,
[wcost_tot] [numeric](14, 4) NOT NULL ,
[wnotax_tot] [numeric](14, 4) NOT NULL ,
[wavecost] [numeric](9, 4) NOT NULL ,
[handlock] [numeric](7, 2) NOT NULL ,
[autolock] [numeric](7, 2) NOT NULL ,
[salegrade] [char] (1) COLLATE Chinese_PRC_BIN NOT NULL ,
[safe_amt] [numeric](7, 2) NOT NULL ,
[safecycle] [tinyint] NOT NULL ,
[price] [numeric](7, 0) NOT NULL ,
[mdate] [datetime] NOT NULL ,
[username] [char] (10) COLLATE Chinese_PRC_BIN NULL
) ON [PRIMARY]