SQL语句练习实例之五 WMS系统中的关于LIFO或FIFO的问题分析

时间:2022-01-26 07:23:11

代码如下:


---在仓储管理中经常会碰到的一个问题 

一、关于LIFOFIFO的简单说明 
---FIFO: First in, First out.先进先出。 
---LIFO: Last in, First out.后进先出。 

--如货物A:本月1日购买10件,单价10元/件,3日购买20件,单价15元/件;10日购买10件,单价8元/件。 
--本月15日发货35件。 

--按FIFO先进先出,就是先购入的存货先发出,所以,先发1日进货的10件,再发3日进货的20件,最后发10日进货的5件,发出成本共为:10*10+20*15+5*8=440元。 
--按LIFO后进先出,就是后购入的存货先发出,所以,先发10日进货的10件,再发3日进货的20件,最后发1日进货的5件,发出成本共为:10*8+20*15+5*10=430元 


二、示例 

复制代码代码如下:


-------- 
Create table stock 
(Id int not null primary key, 
articleno varchar(20) not null, 
rcvdate datetime not null, 
qty int not null, 
unitprice money not null 

go 
---- 
insert stock 
select 1,'10561122','2011-1-1',15,10 union 
select 2,'10561122','2011-2-2',25,12 union 
select 3,'10561122','2011-3-3',35,15 union 
select 4,'10561122','2011-4-4',45,20 union 
select 5,'10561122','2011-5-5',55,10 union 
select 6,'10561122','2011-6-6',65,30 union 
select 7,'10561122','2011-7-7',75,17 union 
select 8,'10561122','2011-8-8',110,8 

go 
----此时如果在2011-8-8卖出300件产品,那么应该如何计算库存销售的价值呢? 
----1使用当前的替换成本,2011-8-8时每件产品的成本为8,就是说你这300件产品,成本价值为2400 
----2使用当前的平均成本单价,一共有420,总成本为6530,平均每件的成本为15.55 
----1.LIFO (后进先出) 
----2011-8-8 110 *8 
----2011-7-7 75*17 
----2011-6-6 65*30 
----2011-5-5 50*10 
-----总成本为 4605 
-----2.FIFO(先进先出) 
---- '2011-1-1',15*10 
--- '2011-2-2',25*12 
-----'2011-3-3',35*15 
-----'2011-4-4',45*20 
-----'2011-5-5',55*10 
-----'2011-6-6',65*30 
-----'2011-7-7',65*17 
----总成本为5480 

---成本视图 
create view costLIFO 
as 
select unitprice from stock 
where rcvdate= (select MAX(rcvdate) from stock) 
go 
create view costFIFO 
as 
select sum(unitprice*qty)/SUM(qty) as unitprice from stock 

go 
-----找出满足订单的、足够存货的最近日期。如果运气好的话,某一天的库存数量正好与订单要求的数字完全一样 
-----就可以将总成本作为答案返回。如果订单止的数量比库存的多,什么也不返回。如果某一天的库存数量比订单数量多 
---则看一下当前的单价,乘以多出来的数量,并减去它。 
---下面这些查询和视图只是告诉我们库存商品的库存价值,注意,这些查询与视图并没有实际从库存中向外发货。 
create view LIFO 
as 
select s1.rcvdate,s1.unitprice,sum(s2.qty) as qty,sum(s2.qty*s2.unitprice) as totalcost 
from stock s1 ,stock s2 
where s2.rcvdate>=s1.rcvdate 
group by s1.rcvdate,s1.unitprice 

go 
select (totalcost-((qty-300)*unitprice )) as cost 
from lifo as l 
where rcvdate=(select max(rcvdate) from lifo as l2 where qty>=300) 
go 

create view FIFO 
as 
select s1.rcvdate,s1.unitprice,sum(s2.qty) as qty,sum(s2.qty*s2.unitprice) as totalcost 
from stock s1 ,stock s2 
where s2.rcvdate<=s1.rcvdate 
group by s1.rcvdate,s1.unitprice 

go 
select (totalcost-((qty-300)*unitprice )) as cost 
from fifo as l 
where rcvdate=(select min(rcvdate) from lifo as l2 where qty>=300) 
-------- 
go 
----- 
-----在发货之后,实时更新库存表 
create view CurrStock 
as 
select s1.rcvdate,SUM(case when s2.rcvdate>s1.rcvdate then s2.qty else 0 end) as PrvQty 
,SUM(case when s2.rcvdate<=s1.rcvdate then s2.qty else 0 end) as CurrQty 
from stock s1 ,stock s2 
where s2.rcvdate<=s1.rcvdate 
group by s1.rcvdate,s1.unitprice 
go 
create proc RemoveQty 
@orderqty int 
as 
if(@orderqty>0) 
begin 
update stock set qty =case when @orderqty>=(select currqty from CurrStock as c where c.rcvdate=stock.rcvdate) 
then 0 
when @orderqty<(select prvqty from CurrStock c2 where c2.rcvdate=stock.rcvdate) 
then stock.qty 
else (select currqty from CurrStock as c3 where c3.rcvdate=stock.rcvdate) 
-@orderqty end 
end 
-- 
delete from stock where qty=0 
--- 
go 
exec RemoveQty 20 
go 
--------------- 


三、使用“贪婪算法”进行订单配货 

复制代码代码如下:


-------还有一个问题,如何使用空间最小或最大的仓库中的货物来满足订单,假设仓库不是顺序排列,你可以按钮希望的顺序任意选择满足订单。 
---使用最小的仓库可以为订单的装卸工人带来最小的工作量,使用最大的仓库,则可以在仓库中清理出更多的空间 
-------例如:对于这组数据,你可以使用(1,2,3,4,5,6,7)号仓库也可以使用(5,6,7,8)号仓库中的货物来满足订单的需求。 
----这个就是装箱问题,它属于NP完全系统问题。对于一般情况来说,这种问题很难解决,因为要尝试所有的组合情况,而且如果数据量大的话, 
----计算机也很难很快处理。 
---所以有了“贪婪算法”,这个算法算出来的常常是近乎最优的。这个算法的核心就是“咬最大的一口”直到达到或超越目标。 
--- 
--1. 第一个技巧,要在表中插入一些空的哑仓库,如果你最多需要n次挑选,则增加n-1个哑仓库 
insert stock 
select -1,'10561122','1900-1-1',0,0 union 
select -2,'10561122','1900-1-1',0,0 
--select -3,'1900-1-1',0,0 
---- 
go 
create view pickcombos 
as 
select distinct (w1.qty+w2.qty+w3.qty) as totalpick 
,case when w1.id<0 then 0 else w1.id end as bin1 ,w1.qty as qty1, 
case when w2.id<0 then 0 else w2.id end as bin2,w2.qty as qty2 
,case when w3.id<0 then 0 else w3.id end as bin3 ,w3.qty as qty3 
from stock w1,stock w2, stock w3 
where w1.id not in (w2.id,w3.id) 
and w2.id not in (w1.id,w3.id) 
and w1.qty>=w2.qty 
and w2.qty>=w3.qty 
---- 
---1.使用存储过程来找出满足或接近某一数量的挑选组合 
-------- 
go 
create proc OverPick 
@pickqty int 
as 
if(@pickqty>0) 
begin 
select @pickqty,totalpick,bin1,qty1,bin2,qty2,bin3,qty3 
from pickcombos 
where totalpick=(select MIN(totalpick) from pickcombos where totalpick>=@pickqty) 
end 
go 
exec OverPick 180 

---------- 
select * from stock 
drop table stock 
drop view lifo 
drop view fifo 
drop view costfifo 
drop view costlifo 
drop view CurrStock 
drop proc OverPick 
drop proc RemoveQty 
drop view pickcombos