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.rcvdategroup 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.rcvdatefrom stock s1 ,stock s2
where s2.rcvdategroup 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 @orderqtythen 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.idcase when w2.id,case when w3.idfrom 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
