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

复制代码代码如下:
---在仓储管理中经常会碰到的一个问题

一、关于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