--创建入库信息
create table tbin(时间 datetime,数量 int,单价 int,商品ID int)
insert into tbin
select '2003.1.1',10,10,1
union all select '2003.1.5',20,15,1
union all select '2003.1.8',15,20,1
union all select '2003.1.5',20,15,2
union all select '2003.1.8',15,20,2
--创建出库信息
create table tbout(时间 datetime,数量 int,商品ID int)
insert into tbout
select '2003.1.2',5,1
union all select '2003.1.8',260,1
union all select '2003.1.8',26,2
--创建入库临时表
select id=identity(int,1,1),时间,数量,单价,商品ID
into #in from tbin where 商品ID in(select 商品ID from tbout)
order by 商品ID,时间
--创建出库临时表
select id=identity(int,1,1),时间,数量,商品ID,数量 as 数量1
into #out from tbout order by 商品ID,时间
while exists (select 1 from #out)
begin
if exists(select 1 from #in)
begin
truncate table #up
insert into #up
select i.id,o.id,i.时间
,case when i.数量<o.数量 then i.数量 else o.数量 end
,i.单价,i.商品ID,o.数量1
from
(select * from #in a where id=(select min(id) from #in where 商品ID=a.商品ID)) i
,(select * from #out a where id=(select min(id) from #out where 商品ID=a.商品ID)) o
where i.商品ID=o.商品ID
insert into #result
select 入库时间,入库数量,单价,商品ID,出库数量 from #up
update #in set 数量=数量-b.入库数量
from #in a,#up b where a.id=b.inid
delete from #in where 数量=0
or 商品ID not in(select 商品ID from #out)
update #out set 数量=数量-b.入库数量
from #out a,#up b where a.id=b.outid
delete from #out where 数量=0
end
else
begin
--下面这句是增加出库数量>入库数量的记录,如果不需要,就删除此句
insert into #result(入库时间,数量,商品ID,对应出库)
select 时间,-数量,商品ID,数量1 from #out
--清除出库表,结束处理
delete from #out
end
end
select convert(varchar(10),入库时间,102) as 入库时间,数量,单价,商品ID,对应出库
from #result order by 商品ID,入库时间
go
select a.goods_id,sum(a.num) as 出库量,sum(b.sum) as 入库量
sum(b.sum)-sum(a.sum) as 库存量,((sum(b.sum)*b.price-sum(a.sum)*a.price)/sum(b.sum)*b.price) from 出库表 a, 入库表 b
where a.goods_id=b.goods_id
group by a.goods_id