sql 库存先进先出原则，统计库存

create table t(
id int identity(1,1),
mz varchar(50),--煤种
lc varchar(50),--料场
fq varchar(50),--分区
j int, --入库数量
c int, --出库数量
jdate datetime --入库时间
)
insert into t(mz,lc,fq,j,c,jdate) select 'A','LC1','FQ1',100,0,'2019-02-01'
insert into t(mz,lc,fq,j,c,jdate) select 'B','LC1','FQ2',140,0,'2019-07-01'
insert into t(mz,lc,fq,j,c,jdate) select 'C','LC1','FQ2',150,0,'2019-08-11'
insert into t(mz,lc,fq,j,c,jdate) select 'A','LC2','FQ3',300,0,'2019-07-11'
insert into t(mz,lc,fq,j,c,jdate) select 'C','LC1','FQ1',300,0,'2019-07-02'
insert into t(mz,lc,fq,j,c,jdate) select 'A','LC2','FQ1',320,0,'2019-06-30'
insert into t(mz,lc,fq,j,c,jdate) select 'B','LC1','FQ2',160,0,'2019-06-15'
insert into t(mz,lc,fq,j,c,jdate) select 'C','LC1','FQ1',170,0,'2019-06-12'
go

alter proc uto.wsp
@mz varchar(50),--煤种
@lc varchar(50),--料场
@fq varchar(50),--分区
@cost int --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where [email protected] AND [email protected] AND [email protected]

if(@spare>[email protected])
begin
--根据入库日期采用先进先出原则对货物的库存进行处理
update t set c=
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where [email protected] AND [email protected] AND [email protected] and jdate<=a.jdate and j!=c)>=0
then a.j
else
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where [email protected] AND [email protected] AND [email protected] and jdate<a.jdate and j!=c)<0 then 0
else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where [email protected] AND [email protected] AND [email protected] and jdate<a.jdate and j!=c)
end
end
from t a where [email protected] AND [email protected] AND [email protected]  and j!=c
end
else
raiserror('库存不足',16,1)
return
go

--测试：

exec uto.wsp @mz='C',@lc='LC1',@fq='FQ1',@cost=10
select * from t  ORDER BY jdate

drop table t
--drop proc uto.wsp

1    A    LC1    FQ1    100    100    2019-02-01 00:00:00.000
8    C    LC1    FQ1    170    170    2019-06-12 00:00:00.000
7    B    LC1    FQ2    160    0    2019-06-15 00:00:00.000
6    A    LC2    FQ1    320    0    2019-06-30 00:00:00.000
2    B    LC1    FQ2    140    0    2019-07-01 00:00:00.000
5    C    LC1    FQ1    300    300    2019-07-02 00:00:00.000
4    A    LC2    FQ3    300    0    2019-07-11 00:00:00.000
3    C    LC1    FQ2    150    0    2019-08-11 00:00:00.000

每次结果会增加 

来源：http://outofmemory.cn/code-snippet/4515/kucun-xianjin-xianchu-jiandan-example