sql 先进先出 库存

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 

     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 

        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) 

        end 

     end 

     from t a where name=@name and j!=c 

   end 

else 

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

    return 

--测试：

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

select * from t

--drop table t 

--drop proc wsp

/*

(4 行受影响) 

id          name                                               j           c           jdate 

----------- -------------------------------------------------- ----------- ----------- ----------------------- 

1           A                                                  100         100         2007-12-01 00:00:00.000 

2           A                                                  200         200         2008-01-07 00:00:00.000 

3           B                                                  320         0           2007-12-21 00:00:00.000 

4           A                                                  100         30          2008-01-15 00:00:00.000 

5           B                                                  90          0           2008-02-03 00:00:00.000 

6           A                                                  460         0           2008-02-01 00:00:00.000 

7           A                                                  510         0           2008-03-01 00:00:00.000

(7 行受影响) 

*/

CREATE TABLE #tmp 

  ( ID int IDENTITY (1, 1),  

  单价 decimal(18, 2) NOT NULL ,  

  进库数量 decimal(18, 0) NOT NULL ,  

  已出数量 decimal(18, 0) NOT NULL 

  ) 

  insert into #tmp(单价,进库数量,已出数量) values(1.1,50,0) 

  insert into #tmp(单价,进库数量,已出数量) values(1.3,30,0) 

  insert into #tmp(单价,进库数量,已出数量) values(1.4,60,0) 

  insert into #tmp(单价,进库数量,已出数量) values(1.5,20,0) 

  select * from #tmp  

  declare @t decimal(18, 0) --一次出库数量 

,@temp decimal(18, 0) --某一单价的临时出库数量 

  select @t=20 

  update #tmp set @temp= 

  case when @t>进库数量-已出数量 

  then 进库数量-已出数量 --当出库数量大于某一单价的结存数量时，那么此单价的出库数量就是结存数量，也就是说此单价的库存数量全部消耗完。 

  else @t --出库数量小于或等于某一单价的结存数量时，那么此次的出库数量就是实际的出库数量 

  end, 

   @t=@t-@temp,--减去一次针对某个单价的临时出库数量 

   已出数量=@temp+已出数量 --新出库的数量+以前出库的数量 

   where 已出数量<>进库数量 --某个单价出库完了就不参于出库计算，即结存数为零的就排除在外 

   select * from #tmp 

set @t=40 

   update #tmp set @temp= 

   case when @t>进库数量-已出数量 

   then 进库数量-已出数量 

   else @t 

   end, 

   @t=@t-@temp, 

   已出数量=@temp+已出数量 

   where 已出数量<>进库数量 

   go 

   drop table #tmp 

    本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2009/11/22/1608079.html，如需转载请自行联系原作者