導讀:1建站知識SQL語句練習實例之五 WMS系統中的關于LIFO或FIFO的問題分析，需要的朋友可以參考下。網站推廣優化seo網站優化seo培訓。

復制代碼 代碼如下:

---在倉儲管理中經常會碰到的一個問題 一、關于LIFO與FIFO的簡單說明 ---FIFO: First in, First out.先進先出。 ---LIFO: Last in, First out.后進先出百度seo網站優化。 --如貨物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 ----- -網站優化seo培訓----在發貨之后，實時更新庫存表 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 sto如何seo優化推廣網站ck 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 ---------------

關鍵詞標簽: SQL 語句 之五