导读: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 语句 之五