有三张表，进货表、销售表、库存表

只要你一个商~~设置商号是主键 你的三张表 里也上商品编号~并且把商品编号作为商品表里商品编号的外键~~一切都解决了。--------给你一个我以前做的库-------------开始创建数据库create database teston primary(name='test_mdf',filename='f:\sql server\test.mdf',size=5,maxsize=10,filegrowth=1)log on(name='test_ldf',filename='f:\sql server\test.ldf',size=5,maxsize=10,filegrowth=1)--以上创建了一个数据库名字为“test”，路径为“f:\sql server\xxx”其他不做解释。use testgo--开始使用数据库。--开始建表--共7个表，大部分都是not **，只有极少数后面做触发器的字段没做not **--1.下面开始创建货物表，名字为“goods”，此表给客户看，好让他们花钱啊。--字段为:--货品编号goods_id(主键约束)，货品名称goods_name，货品单位goods_unit，货品介绍goods_txt。create table goods (goods_id int constraint pk_good_id primary key,goods_name varchar(20) not **,goods_unit varchar(6) not **,goods_txt text not **)go--2.创建用户表，名字为“goods_user”，用此表给分配权限，或者说是限制使用select，必须登陆才能看某项表（思考中暂时不做了,准备在前台实现。）--字段为：--用户编号(登录用)，用户密码，人员姓名，用户类别,用户类别只有在数据库中手动修改，不许擅自改动。create table goods_user (userid int constraint fk_user_id primary key,user_password varchar(20) not **,user_name varchar(10),user_type varchar(10) not **)go--3.创建进货表，名字为“goods_in”,为仓库管理员使用。--字段为：--进货批号in_id(主键约束)，货品编号goods_id(外键关联货物表"goods")，进货单价in_price(约束>0)，--计量单位goods_unit，进货数量in_sum(约束>0)，总价值in_money，收货人编号(仓库管理员)in_who(外键联goods_user的userid)--再加个时间in_time，默认当前就好。create table goods_in (in_id int constraint pk_in_id primary key,goods_id int constraint fk_goods_id foreign key references goods(goods_id) not **,in_price money constraint ck_in_price check(in_price>0) not **,goods_unit varchar(6),in_sum int constraint ck_in_sum check(in_sum>0) not **,in_money money,in_who int constraint fk_in_who foreign key references goods_user(userid) not **,in_time datetime constraint df_in_time default getdate())go--4.创建出货表，名字为“goods_out”，为销售人员使用。--字段为：--销售编号out_id(主键约束)，货品编号goods_id(外键关联货物表"goods")，销售单价out_price(约束>0)，--计量单位goods_unit，销售数量out_sum(约束>0)，总价格out_money，销售人员编号out_who(外键联goods_user的userid)--建个时间out_time默认当前。--增加一个状态字段goods_now，目的是为了给退货商品做个标记。create table goods_out (out_id int constraint pk_out_id primary key,goods_id int constraint fk_goods_id1 foreign key references goods(goods_id) not **,out_price money constraint ck_out_price check(out_price>0) not **,goods_unit varchar(6),out_sum int constraint ck_out_sum check(out_sum>0) not **,out_money money,out_who int constraint fk_in_who1 foreign key references goods_user(userid) not **,out_time datetime constraint df_out_time default getdate(),goods_now varchar(4))go--5.创建库存表，名字为“goods_h**e”，此表内容准备让系统自动生成。--字段为：--货品编号goods_id，库存数量goods_sum(约束>=0)，计量单位goods_unit，库存价值goods_money(约束>=0)create table goods_h**e (goods_id int constraint fk_goods_id3 foreign key references goods(goods_id) not **,goods_sum int constraint ck_goods_sum check(goods_sum>=0) not **,goods_unit varchar(6) not **,goods_money money constraint ck_goods_money check(goods_money>=0))go--6.创建利润表，名字为“goods_win”，此表只允许老板看。--字段为：--货品编号goods_id，计量单位goods_unit，销售总额win_money(约束>=0)，进货金额lose_money(约束>=0)，--利润率go_wincreate table goods_win (goods_id int constraint fk_goods_id4 foreign key references goods(goods_id) not **,goods_unit varchar(6) not **,win_money money constraint ck_win_money check(win_money>=0),lose_money money constraint ck_lose_money check(lose_money>=0),go_win money)go--7.补充一个退货表，或者称为反库表“goods_return”--字段为：--退货批号return_id(主键约束)，购货编号out_id(外键连接goods_out的“out_id”)，退货原因return_why，退货时间return_timecreate table goods_return (return_id int constraint pk_return_id primary key,out_id int constraint fk_in_id foreign key references goods_out(out_id) not **,return_why text not **,return_time datetime constraint df_return_time default getdate())go--下面创建存储过程1产品表的；2进货表的；3出货表的；4用户表(注册用户可以看到的更详细，老板可以查看利润等全部表，员工可以查看与增加自己的出货表，仓库管理员可以查看增加进货表)--共6个存储过程--1.货品表增存储过程：--@goods_id编号，@goods_name货品名，@goods_unit货品单位，@goods_txt货品说明create procedure procedure_goods_insert (@goods_id int,@goods_name varchar(20),@goods_unit varchar(6),@goods_txt text) as insert into goods values(@goods_id,@goods_name,@goods_unit,@goods_txt)go--2.货品表删除货品的存储过程：--@goods_id编号，@goods_name货品名(必须同时编号和货物名称都填对了才可能删除)create procedure procedure_goods_delete (@goods_id int,@goods_name varchar(20)) as delete from goods where goods_id=@goods_id and goods_name=@goods_namego--3.货品表修改货品说明的存储过程：--@goods_id编号，@goods_txt货品说明create procedure procedure_goods_update (@goods_id int,@goods_txt text) as update goods set goods_txt=@goods_txt where goods_id=@goods_idgo --4.创建进货表增加存储过程(进货表不允许出错另外为了防止仓库管理员舞弊，不做删除与修改进货表的存储过程)：--@in_id进货批号，@goods_id货品编号，@in_price进货单价，@goods_unit货品单位，@in_sum进货数量，@in_money合计金额--@in_who收货人编号（仓库管理员），最后附加当前时间create procedure procedure_goods_in (@in_id int,@goods_id int,@in_price money,@in_sum int,@in_money money,@in_who int) as insert into goods_in values(@in_id,@goods_id,@in_price,(select goods_unit from goods where goods.goods_id=@goods_id),@in_sum,@in_money,@in_who,getdate())go--5.创建出货表增加存储过程(出货表不允许出错另外为了防止销售人员舞弊，不做删除与修改出货表的存储过程)：--@out_id销售编号，@goods_id货品编号，@out_price销售单价，@goods_unit货品单位，@out_sum销售数量，@out_money合计金额--@out_who销售人员编号，最后附加当前时间create procedure procedure_goods_out (@out_id int,@goods_id int,@out_price money,@out_sum int,@out_money money,@out_who int) as insert into goods_out values(@out_id,@goods_id,@out_price,(select goods_unit from goods where goods.goods_id=@goods_id),@out_sum,@out_money,@out_who,getdate(),**)go --6.补充一个存储过程退货表的。--@return_id退货批号，@out_id购货编号，@return_why退货原因create procedure procedure_goods_return (@return_id int,@out_id int,@return_why text) as insert into goods_return values(@return_id,@out_id,@return_why,getdate())go--下面创建触发器--共4个触发器--1.创建进货表与出货表的触发器，同时修改库存表的某产品数量和此种产品的总金额；--为了防止销售人员或者仓库管理员造假或误填货品价值。--另外触发器在这里面还实现了另一个目的：让goods表中的goods_nuit字段内容直接赋予goods_in的相应字段值避免填写错误或麻烦。create trigger t_insert_goods_in on goods_in for insert as begin transaction--准备修改进货表的总金额数量，以防工作人员出现误差。 update goods_in set in_money=in_price*in_sum,goods_unit=(select goods_unit from goods where goods_id in (select goods_id from inserted)) where in_id in (select in_id from inserted) if @@error<>0 rollback transaction else--准备修改库存表，关键修改此次进货后的某产品总量和总价值。 update goods_h**e set goods_sum=goods_sum+(select in_sum from inserted),goods_money=goods_money+((select in_price from inserted)*(select in_sum from inserted )) where goods_id in (select goods_id from inserted) if @@error<>0 rollback transaction else--准备执行了 commit transaction go--进货表单次某品种进货价值--触发器在上面--后期又附加了同时修改库存表的语句。--2.出货表单次某品种进货价值--触发器在下面--看来也得加上修改库存的语句了。--另外触发器在这里面还实现了另一个目的：让goods表中的goods_nuit字段内容直接赋予goods_out的相应字段值。create trigger t_insert_goods_out on goods_out for insert as begin transaction--防错系统启动，就算销售人员填错了也会纠正过来。 update goods_out set out_money=out_price*out_sum,goods_unit=(select goods_unit from goods where goods_id in (select goods_id from inserted)) where out_id in (select out_id from inserted) if @@error<>0 rollback transaction else--库存变更 update goods_h**e set goods_sum=goods_sum-(select out_sum from inserted),goods_money=goods_money-((select goods_money/goods_sum from goods_h**e where goods_id in (select goods_id from inserted))*(select out_sum from inserted)) where goods_id in (select goods_id from inserted) if @@error<>0 rollback transaction else--营业额统计、成本核算、利润率计算 update goods_win set win_money=win_money+((select out_price from inserted)*(select out_sum from inserted )),lose_money=lose_money+((select goods_money/goods_sum from goods_h**e where goods_id in (select goods_id from inserted))*(select out_sum from inserted)),go_win=(win_money+((select out_price from inserted)*(select out_sum from inserted ))-(lose_money+((select goods_money/goods_sum from goods_h**e where goods_id in (select goods_id from inserted))*(select out_sum from inserted))))/(lose_money+((select goods_money/goods_sum from goods_h**e where goods_id in (select goods_id from inserted))*(select out_sum from inserted)))*100 where goods_id in (select goods_id from inserted) commit transaction go--3.新品上市的时候，由于利润表和库存表里面都没有新产品的编号等信息，所以必须同时产生一些进货额、出货额都是0的数据。create trigger t_insert_goods on goods for insert as begin transaction insert into goods_win values((select goods_id from inserted),(select goods_unit from inserted),0,0,0) if @@error<>0 rollback transaction else insert into goods_h**e values((select goods_id from inserted),0,(select goods_unit from inserted),0) if @@error<>0 rollback transaction else commit transaction go--4.补充退货时的触发器，当退货时，对销售表产生一个小标记m**s，另外库存有影响，利润表也有影响create trigger t_insert_goods_return on goods_return for insert as begin transaction--退货后，销售表标记m**s在相应的字段上 update goods_out set goods_now='m**s' where out_id in (select out_id from inserted) if @@error<>0 rollback transaction else--修改库存表，库存+相应的退货量，金额+相应平均成本*退货数量 update goods_h**e set goods_sum=goods_sum+(select out_sum from goods_out where out_id in (select out_id from inserted)) ,goods_money=goods_money+(select goods_money from goods_h**e where goods_id in (select goods_id from goods_out where out_id in (select out_id from inserted)))/(select goods_sum from goods_h**e where goods_id in (select goods_id from goods_out where out_id in (select out_id from inserted)))*(select out_sum from goods_out where out_id in (select out_id from inserted)) where goods_id in (select goods_id from goods_out where out_id in (select out_id from inserted)) if @@error<>0 rollback transaction else--利润率也计算出来了--（问题是这次的利润率为什么不要*100就得到百分比的了）------ --此处增加if判断,如果销售额是‘0’，则，费用和利润率都为‘0’ if ((select win_money from goods_win where goods_id in (select goods_id from goods_out where out_id in (select out_id from inserted)))-(select out_money from goods_out where out_id in (select out_id from inserted)))=0 update goods_win set win_money=0,lose_money=0,go_win=0 where goods_id in (select goods_id from goods_out where out_id in(select out_id from inserted)) else update goods_win set win_money=win_money-(select out_money from goods_out where out_id in (select out_id from inserted)),lose_money=lose_money-(select out_sum from goods_out where out_id in (select out_id from inserted))*(select goods_money from goods_h**e where goods_id in (select goods_id from goods_out where out_id in (select out_id from inserted)))/(select goods_sum from goods_h**e where goods_id in (select goods_id from goods_out where out_id in (select out_id from inserted))),go_win=((win_money-(select out_money from goods_out where out_id in (select out_id from inserted)))-(lose_money-(select out_sum from goods_out where out_id in (select out_id from inserted))*(select goods_money from goods_h**e where goods_id in (select goods_id from goods_out where out_id in (select out_id from inserted)))/(select goods_sum from goods_h**e where goods_id in (select goods_id from goods_out where out_id in (select out_id from inserted))))/(lose_money-(select out_sum from goods_out where out_id in (select out_id from inserted))*(select goods_money from goods_h**e where goods_id in (select goods_id from goods_out where out_id in (select out_id from inserted)))/(select goods_sum from goods_h**e where goods_id in (select goods_id from goods_out where out_id in (select out_id from inserted)))))*1 where goods_id in (select goods_id from goods_out where out_id in(select out_id from inserted)) if @@error<>0 rollback transaction else commit transaction go--以上进货，出货都搞定了。退货还不是很完整。--库存表思路：编号自己找(在后面整体编组group by)，数量=进货数-出货数，价值=进货价*数量--利润表思路：编号自己找，销售额=sum(销售表的销售额)，进货费用=(进货表的金额*销售表的销售数量)，利润率=(销售额-进货费用)/进货费用--最后决定库存表的触发器同时放在上面的进货和出货的存储过程中了。下面解释一下：--进货时库存表一定发生变化。我们就找差值就好了数量，总价值；--同理，出货时，库存表也要发生变化。--为了方便测试在此处做几个数据--必须要先填的是goods ,goods_user这两个表，否则其他的表都无法正常测试。insert into goods values(1001,'rice','kg','good,if you h**e had it,you will want it again.')insert into goods values(1002,'water','tin','very good')insert into goods_user values(95001,'001','lucy','xs')insert into goods_user values(95002,'002','tom','ck')insert into goods_user values(95003,'003','lily','rs')insert into goods_user values(95004,'004','admin','lb') 20210311