SQL高效先进先出算法

出处：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'

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点

    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 

end

else

    raiserror('库存不足',16,1)    

    return 

go

   

--测试：  

exec wsp @name='A',@cost=180

select * 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 

end

else

    raiserror('库存不足',16,1)    

    return 

go    

--测试：  

exec wsp @name='A',@cost=180

select * from t

   

--drop table t

--drop proc wsp