create table t0--物资编码表
(
type varchar(50),--类别
name varchar(50),--名称
dw varchar(50),--单位
gg varchar(50),--规格
py varchar(50),--拼音简码
id int identity(1,1)--物资编号
)
insert into t0 select'机械零件','轴承','盘','50','zc'create table t1--入库表
(
type varchar(50),--类别
name varchar(50),--名称
dw varchar(50),--单位
gg varchar(50),--规格
py varchar(50),--拼音简码
sl int,--入库数量
dj int,--单价
rq datetime,--入库日期
jhdw varchar(50),--进货单位
flags varchar(50),--是否冲销(0代表是,1代表否)
id int --物资编号
)
insert into t1 select'机械零件','轴承','盘','50','zc',10,200,'2008-1-1','A厂家','1',1
insert into t1 select'机械零件','轴承','盘','50','zc',10,50,'2008-1-1','A厂家','0',1create table t2--出库表
(
type varchar(50),--类别
name varchar(50),--名称
dw varchar(50),--单位
gg varchar(50),--规格
py varchar(50),--拼音简码
sl int,--出库数量
dj int,--单价
sales_dj int,--销售单价
rq datetime,--出库日期
lydw varchar(50),--领用单位
gmdw varchar(50),--购买单位
flags varchar(50),--是否冲销(0代表是,1代表否)
logo varchar(50),--领用还是销售(0代表领用,1代表销售)
id int --物资编号
)
insert into t2 select'机械零件','轴承','盘','50','zc',2,200,0,'2008-1-1','本厂','无','1','0',1
insert into t2 select'机械零件','轴承','盘','50','zc',3,200,0,'2008-1-1','本厂','无','0','0',1
insert into t2 select'机械零件','轴承','盘','50','zc',1,200,250,'2008-1-2','无','A客户','1','1',1
insert into t2 select'机械零件','轴承','盘','50','zc',2,200,240,'2008-1-2','无','A客户','0','1',1drop table t0
drop table t1
drop table t2
/*
问题:
1.求一触发器,更新(不要删除)t0表中任意字段的记录项时,在t1和t2表中对应其“id”字段的记录同步更新。
如果t1,t2表中没有记录,则不会出现错误提示。
2.求库存结余表(按日期查询,按类别,名称,单位,规格,单价分组统计,冲销的不计)
类别 名称 单位 规格 单价 数量 物资编号 拼音简码
机械零件 轴承 盘 50 200 7 1 zc
*/
(
type varchar(50),--类别
name varchar(50),--名称
dw varchar(50),--单位
gg varchar(50),--规格
py varchar(50),--拼音简码
id int identity(1,1)--物资编号
)
insert into t0 select'机械零件','轴承','盘','50','zc'create table t1--入库表
(
type varchar(50),--类别
name varchar(50),--名称
dw varchar(50),--单位
gg varchar(50),--规格
py varchar(50),--拼音简码
sl int,--入库数量
dj int,--单价
rq datetime,--入库日期
jhdw varchar(50),--进货单位
flags varchar(50),--是否冲销(0代表是,1代表否)
id int --物资编号
)
insert into t1 select'机械零件','轴承','盘','50','zc',10,200,'2008-1-1','A厂家','1',1
insert into t1 select'机械零件','轴承','盘','50','zc',10,50,'2008-1-1','A厂家','0',1create table t2--出库表
(
type varchar(50),--类别
name varchar(50),--名称
dw varchar(50),--单位
gg varchar(50),--规格
py varchar(50),--拼音简码
sl int,--出库数量
dj int,--单价
sales_dj int,--销售单价
rq datetime,--出库日期
lydw varchar(50),--领用单位
gmdw varchar(50),--购买单位
flags varchar(50),--是否冲销(0代表是,1代表否)
logo varchar(50),--领用还是销售(0代表领用,1代表销售)
id int --物资编号
)
insert into t2 select'机械零件','轴承','盘','50','zc',2,200,0,'2008-1-1','本厂','无','1','0',1
insert into t2 select'机械零件','轴承','盘','50','zc',3,200,0,'2008-1-1','本厂','无','0','0',1
insert into t2 select'机械零件','轴承','盘','50','zc',1,200,250,'2008-1-2','无','A客户','1','1',1
insert into t2 select'机械零件','轴承','盘','50','zc',2,200,240,'2008-1-2','无','A客户','0','1',1drop table t0
drop table t1
drop table t2
/*
问题:
1.求一触发器,更新(不要删除)t0表中任意字段的记录项时,在t1和t2表中对应其“id”字段的记录同步更新。
如果t1,t2表中没有记录,则不会出现错误提示。
2.求库存结余表(按日期查询,按类别,名称,单位,规格,单价分组统计,冲销的不计)
类别 名称 单位 规格 单价 数量 物资编号 拼音简码
机械零件 轴承 盘 50 200 7 1 zc
*/
ON t0
FOR UPDATE
AS
BEGIN
--更新t1
UPDATE a SET a.type=b.type,a.name=b.name,a.dw=b.dw,a.gg=b.gg,a.py=b.py
FROM t1 a
INNER JOIN inserted b
ON a.id=b.id
--更新t2
UPDATE a SET a.type=b.type,a.name=b.name,a.dw=b.dw,a.gg=b.gg,a.py=b.py
FROM t2 a
INNER JOIN inserted b
ON a.id=b.id
END
(SELECT type,name,dw,gg,py,sl FROM t1 WHERE flags=1
UNION
SELECT type,name,dw,gg,py,-sl FROM t2 WHERE flags=1
) x
GROUP BY type,name,dw,gg,py
(
type varchar(50),--类别
name varchar(50),--名称
dw varchar(50),--单位
gg varchar(50),--规格
py varchar(50),--拼音简码
id int identity(1,1)--物资编号
)
insert into t0 select'机械零件','轴承','盘','50','zc' create table t1--入库表
(
sl int,--入库数量
dj int,--单价
rq datetime,--入库日期
jhdw varchar(50),--进货单位
flags varchar(50),--是否冲销(0代表是,1代表否)
id int --物资编号
)
insert into t1 select 10,200,'2008-1-1','A厂家','1',1
insert into t1 select 10,50,'2008-1-1','A厂家','0',1 create table t2--出库表
(
sl int,--出库数量
dj int,--单价
sales_dj int,--销售单价
rq datetime,--出库日期
lydw varchar(50),--领用单位
gmdw varchar(50),--购买单位
flags varchar(50),--是否冲销(0代表是,1代表否)
logo varchar(50),--领用还是销售(0代表领用,1代表销售)
id int --物资编号
)
insert into t2 select 2,200,0,'2008-1-1','本厂','无','1','0',1
insert into t2 select 3,200,0,'2008-1-1','本厂','无','0','0',1
insert into t2 select 1,200,250,'2008-1-2','无','A客户','1','1',1
insert into t2 select 2,200,240,'2008-1-2','无','A客户','0','1',1 drop table t0
drop table t1
drop table t2
t1,t2表中的 type varchar(50),--类别
name varchar(50),--名称
dw varchar(50),--单位
gg varchar(50),--规格
py varchar(50),--拼音简码 这些列全部不要。如果某处要经常读到name或type,那么可以适当冗余。
(
type varchar(50),--类别
name varchar(50),--名称
dw varchar(50),--单位
gg varchar(50),--规格
py varchar(50),--拼音简码
id int identity(1,1)--物资编号
)
insert into t0 select'机械零件','轴承','盘','50','zc' create table t1--入库表
(
sl int,--入库数量
dj int,--单价
rq datetime,--入库日期
jhdw varchar(50),--进货单位
flags varchar(50),--是否冲销(0代表是,1代表否)
id int --物资编号
)
insert into t1 select 10,200,'2008-1-1','A厂家','1',1
insert into t1 select 10,50,'2008-1-1','A厂家','0',1 create table t2--出库表
(
sl int,--出库数量
dj int,--单价
sales_dj int,--销售单价
rq datetime,--出库日期
lydw varchar(50),--领用单位
gmdw varchar(50),--购买单位
flags varchar(50),--是否冲销(0代表是,1代表否)
logo varchar(50),--领用还是销售(0代表领用,1代表销售)
id int --物资编号
)
insert into t2 select 2,200,0,'2008-1-1','本厂','无','1','0',1
insert into t2 select 3,200,0,'2008-1-1','本厂','无','0','0',1
insert into t2 select 1,200,250,'2008-1-2','无','A客户','1','1',1
insert into t2 select 2,200,240,'2008-1-2','无','A客户','0','1',1 drop table t0
drop table t1
drop table t2
--------------------------------------------------------------------------------
不错,这样改就把冗余的字段去掉了,用id列就可以把出入库表的整条记录表示出来,而且不存在更新触发器的问题了。
但是库存结余表该怎样写呢?
求库存结余表(按日期查询,按类别,名称,单位,规格,单价分组统计,冲销的不计)
类别 名称 单位 规格 单价 数量 物资编号 拼音简码
机械零件 轴承 盘 50 200 7 1 zc
create table t0--物资编码表
(
type varchar(50),--类别
name varchar(50),--名称
dw varchar(50),--单位
gg varchar(50),--规格
py varchar(50),--拼音简码
id int identity(1,1)--物资编号
)
insert into t0 select'机械零件','轴承','盘','50','zc' create table t1--入库表
(
sl int,--入库数量
dj int,--单价
rq datetime,--入库日期
jhdw varchar(50),--进货单位
flags varchar(50),--是否冲销(0代表是,1代表否)
id int --物资编号
)
insert into t1 select 10,200,'2008-1-1','A厂家','1',1
insert into t1 select 10,50,'2008-1-1','A厂家','0',1 create table t2--出库表
(
sl int,--出库数量
dj int,--单价
sales_dj int,--销售单价
rq datetime,--出库日期
lydw varchar(50),--领用单位
gmdw varchar(50),--购买单位
flags varchar(50),--是否冲销(0代表是,1代表否)
logo varchar(50),--领用还是销售(0代表领用,1代表销售)
id int --物资编号
)
insert into t2 select 2,200,0,'2008-1-1','本厂','无','1','0',1
insert into t2 select 3,200,0,'2008-1-1','本厂','无','0','0',1
insert into t2 select 1,200,250,'2008-1-2','无','A客户','1','1',1
insert into t2 select 2,200,240,'2008-1-2','无','A客户','0','1',1 select t0.type, t0.name, t0.dw, t0.gg
,dj = isnull(a.dj,b.dj)
,sl = isnull(a.sl,0)-isnull(b.sl,0)
,t0.id,t0.py
from t0,
(select sl=sum(sl),dj=avg(dj),id from t1 where flags=1 group by id) as a, -- 未冲销的入库汇总
(select sl=sum(sl),dj=avg(dj),id from t2 where flags=1 group by id) as b -- 未冲销的出库汇总
where t0.id*=a.id and t0.id*=b.id
-- type,name,dw,gg,dj,sl,id,py
-- 机械零件,轴承,盘,50,200,7,1,zcdrop table t0
drop table t1
drop table t2
http://blog.csdn.net/chinahuyong