mysql库存先进先出_sql 先进先出 库存

createtablet(

idintidentity(1,1), namevarchar(50),--商品名称jint,--入库数量cint,--出库数量jdatedatetime--入库时间)insertintot(name,j,c,jdate)select'A',100,0,'2007-12-01'insertintot(name,j,c,jdate)select'A',200,0,'2008-01-07'insertintot(name,j,c,jdate)select'B',320,0,'2007-12-21'insertintot(name,j,c,jdate)select'A',100,0,'2008-01-15'insertintot(name,j,c,jdate)select'B',90,0,'2008-02-03'insertintot(name,j,c,jdate)select'A',460,0,'2008-02-01'insertintot(name,j,c,jdate)select'A',510,0,'2008-03-01'gocreateprocwsp@namevarchar(50),--商品名称@costint--销售量as--先得出该货物的库存是否够declare@sparefloat--剩余库存select@spare=sum(j)-sum(c)fromtwherename=@nameif(@spare>=@cost)begin--根据入库日期采用先进先出原则对货物的库存进行处理updatetsetc=casewhen(select@cost-isnull(sum(j),0)+isnull(sum(c),0)fromtwherename=@nameandjdate<=a.jdateandj!=c)>=0thena.jelsecasewhen(select@cost-isnull(sum(j),0)+isnull(sum(c),0)fromtwherename=@nameandjdate

id          name                                               j           c           jdate

----------- -------------------------------------------------- ----------- ----------- -----------------------

1           A                                                  100         100         2007-12-01 00:00:00.000

2           A                                                  200         200         2008-01-07 00:00:00.000

3           B                                                  320         0           2007-12-21 00:00:00.000

4           A                                                  100         30          2008-01-15 00:00:00.000

5           B                                                  90          0           2008-02-03 00:00:00.000

6           A                                                  460         0           2008-02-01 00:00:00.000

7           A                                                  510         0           2008-03-01 00:00:00.000

(7 行受影响)*/CREATETABLE#tmp

( IDintIDENTITY(1,1),

单价decimal(18,2)NOTNULL,

进库数量decimal(18,0)NOTNULL,

已出数量decimal(18,0)NOTNULL)insertinto#tmp(单价,进库数量,已出数量)values(1.1,50,0)insertinto#tmp(单价,进库数量,已出数量)values(1.3,30,0)insertinto#tmp(单价,进库数量,已出数量)values(1.4,60,0)insertinto#tmp(单价,进库数量,已出数量)values(1.5,20,0)select*from#tmpdeclare@tdecimal(18,0)--一次出库数量,@tempdecimal(18,0)--某一单价的临时出库数量select@t=20update#tmpset@temp=casewhen@t>进库数量-已出数量then进库数量-已出数量--当出库数量大于某一单价的结存数量时，那么此单价的出库数量就是结存数量，也就是说此单价的库存数量全部消耗完。else@t--出库数量小于或等于某一单价的结存数量时，那么此次的出库数量就是实际的出库数量end,@t=@t-@temp,--减去一次针对某个单价的临时出库数量已出数量=@temp+已出数量--新出库的数量+以前出库的数量where已出数量<>进库数量--某个单价出库完了就不参于出库计算，即结存数为零的就排除在外select*from#tmpset@t=40update#tmpset@temp=casewhen@t>进库数量-已出数量then进库数量-已出数量else@tend,@t=@t-@temp,

已出数量=@temp+已出数量where已出数量<>进库数量select*from#tmpgodroptable#tmp