--------------------------------------------
-- Author:TravyLee(跟小F姐姐混)
-- Date :2012-05-26 16:00:00
--------------------------------------------
Example One:
-------------------------------------------------------
create table djjx
(
djbh varchar(20), --单据编号 key
spid varchar(20), --商品ID
shl int --数量
)
--其中二行数据:
insert into djjx values('dj0001','sp0001',300)
insert into djjx values('dj0001','sp0002',100)
--表二:
create table sphwph
(
spid varchar(20), --商品ID key
hw varchar(20), --货位 key
ph varchar(20), --批号 key
shl int --数量
)
其中几行数据:
insert into sphwph values('sp0001','hw0001','ph001',50)
insert into sphwph values('sp0001','hw0001','ph002',40)
insert into sphwph values('sp0001','hw0002','ph002',90)
insert into sphwph values
('sp0001','hw0003','ph003',500)
insert into sphwph values
('sp0001','hw0004','ph005',1000)
insert into sphwph values('sp0002','hw0001','ph006',90)
insert into sphwph values
('sp0002','hw0002','ph009',100)
/*
求得到如下结果或一张临时表:
djbh spid hw ph shl
dj0001 sp0001 hw001 ph001 50
dj0001 sp0001 hw001 ph002 40
dj0001 sp0001 hw002 ph002 90
dj0001 sp0001 hw003 ph003 120
dj0001 sp0002 hw001 ph006 90
dj0001 sp0002 hw002 ph009 10
*/
;with t
as(
select id=row_number()over(partition by a.spid order by
getdate()),
a.djbh,a.spid,a.shl as total,b.hw,b.ph,b.shl
from djjx a inner join sphwph b on a.spid=b.spid
),
m as(
select id,djbh,spid,(total-shl) as total,hw,ph,shl from
t where id=1
union all
select a.id,a.djbh,a.spid,b.total-a.shl,a.hw,a.ph,a.shl
from t a
inner join m b on a.id=b.id+1 and a.spid=b.spid
)
select djbh,spid,hw,ph,
case when total<0 then shl-ABS(total) else shl end as
shl
from m where case when total<0 then shl-ABS(total) else
shl end>0 order by spid,id
/*
djbh spid hw ph shl
dj0001 sp0001 hw0001 ph001 50
dj0001 sp0001 hw0001 ph002 40
dj0001 sp0001 hw0002 ph002 90
dj0001 sp0001 hw0003 ph003 120
dj0001 sp0002 hw0001 ph006 90
dj0001 sp0002 hw0002 ph009 10
*/
-------------------------------------------------------
Example Two
---->>TravyLee生成测试数据:
if OBJECT_ID('流水表') is not null
drop table 流水表
go
create table 流水表(
ProductNmae varchar(20),
Counts int,
Dates varchar(10),
Kinds varchar(2)
)
go
if OBJECT_ID('余额表') is not null
drop table 余额表
go
create table 余额表(
ProductNmae varchar(20),
Counts int,
Dates varchar(10)
)
go
insert 余额表
select '轴承',100,'5月1日' union all
select '端盖',200,'6月1日' union all
select '轴承',200,'7月1日' union all
select '轴承',330,'8月1日' union all
select '轴承',400,'9月1日'
-------------------------------------------
-------------------------------------------
---->>>触发器实现对余额表的管理
go
if OBJECT_ID('tri_test')is not null
drop trigger tri_test
go
create trigger tri_test on 流水表
for insert
as
--处理新增类型为'入'的零件的余额表数据更新
/*更新余额表中存在的日期的数据*/
update 余额表
set 余额表.Counts=t.Counts+余额表.Counts from(
select
ProductNmae,sum(Counts) Counts,Dates
from
inserted i
where
exists(select 1 from 余额表 t
where i.ProductNmae=t.ProductNmae and
i.Dates=t.Dates)
and i.Kinds='入'
group by
ProductNmae,Dates
)t
where
余额表.ProductNmae=t.ProductNmae and 余额
表.Dates=t.Dates
/*插入日期在之前余额表中不存在的*/
insert 余额表
select
ProductNmae,sum(Counts),Dates
from
inserted i
where
not exists(select 1 from 余额表 t
where i.ProductNmae=t.ProductNmae and
i.Dates=t.Dates)
and i.Kinds='入'
group by
ProductNmae,Dates
--处理新增类型为'出'的零件的余额表数据更新
/*先对余额表里的数据进行递归累计求和运算*/
;with t
as(
select
px=row_number()over(partition by ProductNmae
order by getdate()),ProductNmae,Counts,Dates
from 余额表
),m
as(
select px,ProductNmae,Counts,Counts as total,Dates
from t
where px=1
union all
select a.px,a.ProductNmae,a.Counts,
a.Counts+b.total,a.Dates
from t a
inner join m b
on a.px=b.px+1 and a.ProductNmae=b.ProductNmae
),
n as(
select m.px,m.ProductNmae,m.Dates,m.Counts,m.total
totalm,b.total totaln
from m
inner join(
select ProductNmae,sum(Counts) as total from inserted
where Kinds='出'
group by ProductNmae
)b on m.ProductNmae=b.ProductNmae
),
o as
(
select ProductNmae,Dates,
case when px in(select px from n where totalm-totaln<0)
then 0
when px=(select min(px) from n where totalm-
totaln>=0) then totalm-totaln
else Counts end as NewCounts
from n
)
update 余额表
set 余额表.Counts=o.NewCounts from o
where 余额表.Dates=o.Dates and 余额
表.ProductNmae=o.ProductNmae
delete from 余额表 where Counts=0
--验证:
--当9月1日增加轴承50只时，9月2日增加端盖30只时表变成
insert 流水表
select '轴承',50,'9月1日','入' union all
select '端盖',30,'9月2日','入'
select * from 余额表
/*
ProductNmae Counts Dates
轴承 100 5月1日
端盖 200 6月1日
轴承 200 7月1日
轴承 330 8月1日
轴承 450 9月1日
端盖 30 9月2日
*/
--当9月3日出货轴承400只时，即是
insert 流水表
select '轴承',400,'9月3日','出'
select * from 余额表
/*
ProductNmae Counts Dates
端盖 200 6月1日
轴承 230 8月1日
轴承 450 9月1日
端盖 30 9月2日
*/
