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

这篇文章主要为大家详细介绍了SQL Server SQL语句练习实例之五 WMS系统中的关于LIFO或FIFO的问题分析，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

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

一、关于LIFO与FIFO的简单说明 
---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 

注：关于SQL Server SQL语句练习实例之五 WMS系统中的关于LIFO或FIFO的问题分析的内容就先介绍到这里，更多相关文章的可以留意