要求: 一个库存表 一个业务表
每次减库存时 按照 自增列的顺序减库存
但是 BH 为编号 KH 为库房号
两个库号里的商品不能互相减库存
目的就是实现 先进先出 减库存法create table #sp
(bh char(8) null default '',
kh char(2) null default '',
plh char(10) null default '',
shl int,
snn int IDENTITY (1, 1) NOT NULL
)create table #ls
(bh char(8) null default '',
kh char(2) null default '',
shl int
)insert into #sp
(bh,kh,plh,shl)
select '100001','1','10111',20
union
select '100001','1','10132',10
union
select '100001','1','10133',20
union
select '100001','1','10134',20
union
select '100001','2','10111',12
union
select '100001','2','10132',1
union
select '100001','2','10234',230
union
select '100001','2','10235',25
union
select '100002','1','10111',20
union
select '100002','1','10132',10
union
select '100002','1','10133',20
union
select '100002','1','10134',20
union
select '100002','2','10111',12
union
select '100002','2','10132',1
union
select '100002','2','10234',230
union
select '100002','2','10235',25
insert into #ls
(bh,kh,shl)
select '100001','1',50
union
select '100001','2',40
union
select '100002','1',30
union
select '100002','2',10select * from #sp
select * from #ls
每次减库存时 按照 自增列的顺序减库存
但是 BH 为编号 KH 为库房号
两个库号里的商品不能互相减库存
目的就是实现 先进先出 减库存法create table #sp
(bh char(8) null default '',
kh char(2) null default '',
plh char(10) null default '',
shl int,
snn int IDENTITY (1, 1) NOT NULL
)create table #ls
(bh char(8) null default '',
kh char(2) null default '',
shl int
)insert into #sp
(bh,kh,plh,shl)
select '100001','1','10111',20
union
select '100001','1','10132',10
union
select '100001','1','10133',20
union
select '100001','1','10134',20
union
select '100001','2','10111',12
union
select '100001','2','10132',1
union
select '100001','2','10234',230
union
select '100001','2','10235',25
union
select '100002','1','10111',20
union
select '100002','1','10132',10
union
select '100002','1','10133',20
union
select '100002','1','10134',20
union
select '100002','2','10111',12
union
select '100002','2','10132',1
union
select '100002','2','10234',230
union
select '100002','2','10235',25
insert into #ls
(bh,kh,shl)
select '100001','1',50
union
select '100001','2',40
union
select '100002','1',30
union
select '100002','2',10select * from #sp
select * from #ls
http://blog.csdn.net/zjcxc/archive/2004/08/17/77449.aspx
select b.*,即时库存数量=ls.shl-b.kcsl from
(select *,kcsl=(select sum(shl) from sp a where a.bh=sp.bh and a.kh=sp.kh and a.snn<=sp.snn) from sp) b
left join ls
on ls.bh=b.bh and ls.kh=b.kh