mysql库存先进先出_sql 先进先出 库存

create tablet(

idint 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 procwsp@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

thena.jelse

case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate

then 0

else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate

end

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

else

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

go

--测试：

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

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

( IDint 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#tmpdeclare @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#tmpset @t=40

update #tmp set @temp=

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

end,@t=@t-@temp,

已出数量=@temp+已出数量where 已出数量<>进库数量select * from#tmpgo

drop table #tmp