SQL語(yǔ)句練習(xí)實(shí)例之五 WMS系統(tǒng)中的關(guān)于LIFO或FIFO的問(wèn)題分析

---在倉(cāng)儲(chǔ)管理中經(jīng)常會(huì)碰到的一個(gè)問(wèn)題

一、關(guān)于LIFO與FIFO的簡(jiǎn)單說(shuō)明
---FIFO: First in, First out.先進(jìn)先出。
---LIFO: Last in, First out.后進(jìn)先出。

--如貨物A：本月1日購(gòu)買(mǎi)10件，單價(jià)10元/件，3日購(gòu)買(mǎi)20件，單價(jià)15元/件；10日購(gòu)買(mǎi)10件，單價(jià)8元/件。
--本月15日發(fā)貨35件。

--按FIFO先進(jìn)先出，就是先購(gòu)入的存貨先發(fā)出，所以，先發(fā)1日進(jìn)貨的10件，再發(fā)3日進(jìn)貨的20件，最后發(fā)10日進(jìn)貨的5件，發(fā)出成本共為：10*10+20*15+5*8=440元。
--按LIFO后進(jìn)先出，就是后購(gòu)入的存貨先發(fā)出，所以，先發(fā)10日進(jìn)貨的10件，再發(fā)3日進(jìn)貨的20件，最后發(fā)1日進(jìn)貨的5件，發(fā)出成本共為：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
----此時(shí)如果在2011-8-8賣(mài)出300件產(chǎn)品，那么應(yīng)該如何計(jì)算庫(kù)存銷(xiāo)售的價(jià)值呢？
----1使用當(dāng)前的替換成本，2011-8-8時(shí)每件產(chǎn)品的成本為8，就是說(shuō)你這300件產(chǎn)品，成本價(jià)值為2400
----2使用當(dāng)前的平均成本單價(jià)，一共有420，總成本為6530，平均每件的成本為15.55
----1.LIFO （后進(jìn)先出）
----2011-8-8 110 *8
----2011-7-7 75*17
----2011-6-6 65*30
----2011-5-5 50*10
-----總成本為 4605
-----2.FIFO(先進(jìn)先出)
---- '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
-----找出滿(mǎn)足訂單的、足夠存貨的最近日期。如果運(yùn)氣好的話(huà)，某一天的庫(kù)存數(shù)量正好與訂單要求的數(shù)字完全一樣
-----就可以將總成本作為答案返回。如果訂單止的數(shù)量比庫(kù)存的多，什么也不返回。如果某一天的庫(kù)存數(shù)量比訂單數(shù)量多
---則看一下當(dāng)前的單價(jià)，乘以多出來(lái)的數(shù)量，并減去它。
---下面這些查詢(xún)和視圖只是告訴我們庫(kù)存商品的庫(kù)存價(jià)值，注意，這些查詢(xún)與視圖并沒(méi)有實(shí)際從庫(kù)存中向外發(fā)貨。
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
-----
-----在發(fā)貨之后，實(shí)時(shí)更新庫(kù)存表
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
---------------

-------還有一個(gè)問(wèn)題，如何使用空間最小或最大的倉(cāng)庫(kù)中的貨物來(lái)滿(mǎn)足訂單，假設(shè)倉(cāng)庫(kù)不是順序排列，你可以按鈕希望的順序任意選擇滿(mǎn)足訂單。
---使用最小的倉(cāng)庫(kù)可以為訂單的裝卸工人帶來(lái)最小的工作量，使用最大的倉(cāng)庫(kù)，則可以在倉(cāng)庫(kù)中清理出更多的空間
-------例如：對(duì)于這組數(shù)據(jù)，你可以使用（1，2，3，4，5，6，7）號(hào)倉(cāng)庫(kù)也可以使用（5，6，7，8）號(hào)倉(cāng)庫(kù)中的貨物來(lái)滿(mǎn)足訂單的需求。
----這個(gè)就是裝箱問(wèn)題，它屬于NP完全系統(tǒng)問(wèn)題。對(duì)于一般情況來(lái)說(shuō)，這種問(wèn)題很難解決，因?yàn)橐獓L試所有的組合情況，而且如果數(shù)據(jù)量大的話(huà)，
----計(jì)算機(jī)也很難很快處理。
---所以有了“貪婪算法”，這個(gè)算法算出來(lái)的常常是近乎最優(yōu)的。這個(gè)算法的核心就是“咬最大的一口”直到達(dá)到或超越目標(biāo)。
---
--1. 第一個(gè)技巧，要在表中插入一些空的啞倉(cāng)庫(kù)，如果你最多需要n次挑選，則增加n-1個(gè)啞倉(cāng)庫(kù)
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.id0 then 0 else w1.id end as bin1 ,w1.qty as qty1,
case when w2.id0 then 0 else w2.id end as bin2,w2.qty as qty2
,case when w3.id0 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.使用存儲(chǔ)過(guò)程來(lái)找出滿(mǎn)足或接近某一數(shù)量的挑選組合
--------
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