出处：http://bbs.csdn.net/topics/370041944
适用于：库存处理、积分使用等，应该优于游票的处理
作者blog: http://blog.csdn.net/fredrickhu
--库存先进先出简单例子： create table t(id int identity(1,1),name varchar(50),--商品名称j int, --入库数量c int, --出库数量jdate datetime --入库时间)insert into t(name,j,c,jdate) select 'A',100,0,'2007-12-01'insert into t(name,j,c,jdate) select 'A',200,0,'2008-01-07'insert into t(name,j,c,jdate) select 'B',320,0,'2007-12-21'insert into t(name,j,c,jdate) select 'A',100,0,'2008-01-15'insert into t(name,j,c,jdate) select 'B',90,0,'2008-02-03'insert into t(name,j,c,jdate) select 'A',460,0,'2008-02-01'insert into t(name,j,c,jdate) select 'A',510,0,'2008-03-01'gocreate proc wsp@name varchar(50),--商品名称@cost int --销售量as--先得出该货物的库存是否够declare @spare float --剩余库存select @spare=sum(j)-sum(c) from t where name=@name if(@spare>=@cost)begin --根据入库日期采用先进先出原则对货物的库存进行处理 update t set c= case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0--
A点 then a.j else case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 --
B点then 0 else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c) --
C点 end end from t a where name=@name and j!=c endelse raiserror('库存不足',16,1) return go--测试： exec wsp @name='A',@cost=180select * from t--drop table t--drop proc wsp
【个人总结】：
这个写法比较精辟经几翻思考下觉得有几个点要注意：
A点 - 加上这笔进货后，仍不足以抵销之前出货+本次出货, 所以本笔进货数全用上了
B点 - 之前的进货已抵销了之前的出货+本次出货, 所以本笔记录无需出货
C点 - 之前的进货加这笔一部份就可以抵消之前的出货+本次出货, 所以本笔只要出一部分数就可以了
另外，这个jdate(日期)作为排序（比较）的依据非常重要，要保证它的相对唯一性，否则就出不了正确的结果了。
（我们特意在执行wsp前加一行insert
into t(name,j,c,jdate) select 'A',100,0,'2007-12-01'就可以看到了错误的结果。）
解决的加法是用jdate + id作为排序（比较）的依据。
加上id作为依据后的写法如下（红色斜体部分为改写内容）：
--库存先进先出简单例子： create table t(id int identity(1,1),name varchar(50),--商品名称j int, --入库数量c int, --出库数量jdate datetime --入库时间)insert into t(name,j,c,jdate) select 'A',100,0,'2007-12-01'insert into t(name,j,c,jdate) select 'A',200,0,'2008-01-07'insert into t(name,j,c,jdate) select 'B',320,0,'2007-12-21'insert into t(name,j,c,jdate) select 'A',100,0,'2008-01-15'insert into t(name,j,c,jdate) select 'B',90,0,'2008-02-03'insert into t(name,j,c,jdate) select 'A',460,0,'2008-02-01'insert into t(name,j,c,jdate) select 'A',510,0,'2008-03-01'insert
into t(name,j,c,jdate) select 'A',100,0,'2007-12-01'
go create proc wsp@name varchar(50),--商品名称@cost int --销售量as--先得出该货物的库存是否够declare @spare float --剩余库存select @spare=sum(j)-sum(c) from t where name=@name if(@spare>=@cost)begin --根据入库日期采用先进先出原则对货物的库存进行处理 update t set c= case --when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0--
A点when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and
(case when jdate<a.jdate then 1 when jdate=a.jdate and id<=a.id then 1 else 0 end)=1 and j!=c)>=0--
A点 then a.j else case --when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 --
B点
when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and (jdate<a.jdate
or (jdate=a.jdate and id<a.id)) and j!=c)<0 --B点then 0 else --(select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c) --
C点
(select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and (jdate<a.jdate
or (jdate=a.jdate and id<a.id)) and j!=c) --
C点 end end from t a where name=@name and j!=c endelse raiserror('库存不足',16,1) return go --测试： exec wsp @name='A',@cost=180select * from t--drop table t--drop proc wsp