如何在SQL中实现先进先出（FIFO）

Question

我正在实现SQL中的FIFO（先进先出）算法。

我的应用程序中有“批次号”概念。

如果我在库存上销售，我的应用程序应该告诉我哪个库存是最先进来的。

例如，我在8月4日、8月5日和8月6日购买了库存'A'。

On 4th Aug - A Inventory has batch number   BT002 - 10 (Qty)
On 5th Aug - A's Inventory has batch number BT003 - 15 (Qty)
On 6th Aug - A's Inventory has batch number BT001 - 10 (Qty)

现在我手上有以下库存：

A Inventory
BT002 - 10 - 4-Aug
BT003 - 15 - 5-Aug
BT001 - 10 - 6-Aug

现在，如果我想把这个库存卖给任何人，我的应用程序应该告诉我，我应该首先出售BT002（批号）库存，因为它是最早的。

这就是我在我的应用程序中使用的概念。

现在我想从“ A”（库存）中销售15个数量。

然后输出应该像这样：

BT002 - 10
BT003 - 5

以下是我的查询：

这里是我的查询：

SELECT ISNULL(SUM(qty),0) AS Qty,batch_no,accept_date  FROM RS_GIN_Master 
GROUP BY batch_no,accept_date
HAVING ISNULL(SUM(qty),0) <= 15
ORDER BY accept_date asc

给定查询的输出： 我该如何获得这样的输出：

BT002 - 10
BT003 - 5

任何帮助都将不胜感激。 提前致谢。

- Hardik Parmar

---

每个库存项目有多少行数据？这是运行总计问题的一个变体。 - Martin Smith

它（Value）可以是任何东西，这意味着它不能被判断，因为如果我有100个“a”库存的批次号，而在那个批次中我可以有任何数量（这个数量将是手头的数量）。 - Hardik Parmar

如果您有任何想法，可以分享一下应该采用哪种方法来获得这个输出。 - Hardik Parmar

如果使用SQL 2008，那么递归CTE就可以很好地完成这项工作。只要您的堆栈中还有项目或者还有更多需要取出的项目，就应该从堆栈中提取这些项目。 - Dumitrescu Bogdan

请给我一个例子，不好意思，我误选了那个标签。我的应用程序使用的是SQL 2012。我已经进行了编辑。 - Hardik Parmar

请检查LAG函数的使用。http://msdn.microsoft.com/en-us/library/hh231256(v=sql.110).aspx 这应该是您所需的内容。 - Dumitrescu Bogdan

Answer 1

这对您应该有用：
Fiddle上的工作示例。

CREATE FUNCTION [dbo].[GetBatchAmounts]
(
    @requestedAmount int

)
RETURNS 
@tBatchResults TABLE 
(   
    Batch nvarchar(50),
    Amount int
)
AS
BEGIN
    /*This is just a mock of ersults of your query*/
    DECLARE @RS_GIN_Master TABLE( 

     Qty int,
     batch_no NVARCHAR(max),
     accept_date DATETIME
    )

    insert into @RS_GIN_Master(Qty,batch_no,accept_date)
    SELECT 10,'BT002', CAST(CAST(2014 AS varchar) + '-' + CAST(8 AS varchar) + '-' + CAST(4 AS varchar) AS DATETIME)

    insert into @RS_GIN_Master(Qty,batch_no,accept_date)
    SELECT 10,'BT003', CAST(CAST(2014 AS varchar) + '-' + CAST(8 AS varchar) + '-' + CAST(5 AS varchar) AS DATETIME)

    insert into @RS_GIN_Master(Qty,batch_no,accept_date)
    SELECT 10,'BT001', CAST(CAST(2014 AS varchar) + '-' + CAST(8 AS varchar) + '-' + CAST(6 AS varchar) AS DATETIME)
    /*---------------------------*/

     DECLARE @Qty int
     DECLARE @batch_no NVARCHAR(max)
     DECLARE @accept_date DATETIME


    DECLARE myCursor CURSOR FOR

    SELECT Qty, batch_no, accept_date FROM @RS_GIN_Master ORDER BY accept_date ASC

    OPEN myCursor

    FETCH NEXT FROM myCursor INTO  @Qty, @batch_no,@accept_date

    WHILE (@@FETCH_STATUS = 0 AND @requestedAmount > 0 ) 
    BEGIN

        Declare @actualQty int
        IF @requestedAmount > @Qty
            SET @actualQty = @Qty
        ELSE    
            SET @actualQty = @requestedAmount


        INSERT INTO @tBatchResults (batch, Amount)
        SELECT @batch_no, @actualQty

        set @requestedAmount  = @requestedAmount - @actualQty

        FETCH NEXT FROM myCursor INTO @Qty, @batch_no,@accept_date

    END /*WHILE*/

    CLOSE myCursor
    DEALLOCATE myCursor

    RETURN
END

请确保用您的查询替换函数中标记的部分...

- Avi Turner

---

感谢您的努力，@Avi。您的答案非常有用。只是一个简单的疑问：函数在性能方面比存储过程更好吗？ - Hardik Parmar

@Hardik 请看一下这个SO线程。两者之间不应该有太大的区别。使用函数的好处是可以在查询中使用它（连接，子查询等）。如果您坚持要使用SP，只需将其封装在其中即可... - Avi Turner

Answer 2

您需要在数据库中创建一个存储过程，从库存表中获取数量。您还应该拥有每个记录的ID，以更新从哪里取出该数量的记录。

Alter PROCEDURE sp_UpdateStockForSale
    @batchNO varchar(10), 
    @qty decimal(9,3)
AS
BEGIN
    Create Table #tmpOutput(ID int identity(1,1), StockID int, batchNo varchar(10), qty decimal(9,3));
    SET NOCOUNT ON;

    DECLARE @ID int;
    DECLARE @Stock Decimal(9,3);

    DECLARE @TEMPID int;
    Select @TEMPID=(Max(ID)+1) From RS_GIN_Master Where qty > 0 And batch_no = @batchNO;

    While (@qty > 0) BEGIN
        Select @ID=ID, @Stock=qty From RS_GIN_Master Where qty > 0 And batch_no = @batchNO AND ID < @TEMPID Order By accept_date Desc;

        --If Outward Qty is more than Stock
        IF (@Stock < @qty) BEGIN
            SET @qty = @qty - @Stock;
            SET @Stock = 0;
        END
        --If Outward Qty is less than Stock
        ELSE BEGIN          
            SET @Stock = @Stock - @qty;
            SET @qty = 0;
        END    
        Insert Into #tmpOutput(StockID,batchNo,qty)Values(@ID,@batchNO,@Stock);
        SET @TEMPID = @ID;
        --This will update that record don't need it now.
        --Update RS_GIN_Master Set qty = @Stock Where ID=@ID
    END
    Select StockID, batchNo, qty From #tmpOutput;
END
GO

以上示例未经编译，但您可以了解如何按照FIFO方法从库存表中检索记录的逻辑。您可以在RS_GIN_Master表中使用accept_date代替ID。但我更喜欢将其设置为唯一的，这样如果我想获取特定记录，则可能会更容易。

- Shell

---

如果我返回接受日期而不是ID，那么它会出现错误。 - Hardik Parmar

@Hardik，正如我告诉过你的那样，上面给出的示例没有编译。因此，它可能会产生错误。顺便问一下，它会出现什么类型的错误？ - Shell

Answer 3

一个查询..像这样

这应该根据您的情况进行调整，因为您有组和其他内容，仅用于示例目的。

;with qty as (
  select 15 as value
)
,l as (
  select 
    ROW_NUMBER () over (order by accept_date desc) rn
    ,*
  from xxx
)
,q as (
  select 
    batch_no
    ,accept_date
    ,case when value>qty then value-qty else 0 end as remainder
    ,case when value>qty then qty else value end as used
    ,rn
  from l
  cross join qty
  where rn=1
  union all
  select 
    r.batch_no
    ,r.accept_date
    ,case when q.remainder>r.qty then q.remainder-r.qty else 0 end  as remainder
    ,case when q.remainder>r.qty then r.qty else q.remainder end as used
    ,r.rn
  from q 
  join l r
  on q.rn+1 = r.rn
  where  q.remainder!=0
)
select * 
from q
where used != 0

而为此准备的fiffle http://sqlfiddle.com/#!6/9b063/34/0

- Dumitrescu Bogdan

---

感谢@Dumitrescu的努力。您的答案也非常有用。但是，如果我有超过2个批次，则此查询将无法工作。 - Hardik Parmar

好的..这是一个通用查询。我不知道你所有的条件，所以你必须根据自己的需求进行修改。但这就是解决问题的方法。 - Dumitrescu Bogdan

请问在编程中，使用CTE（公共表达式）还是函数更好？ - Hardik Parmar

这是一种更清晰的方式，更加偏向于 SQL 的方法。而且它只需要一个查询，所以只有一个计划，根据数据量的不同，速度也会更快。 - Dumitrescu Bogdan

我已经在查询中进行了更改，因为在某些特定情况下，只返回了第一行。 - Dumitrescu Bogdan

Answer 4

以下内容应该适合您的需求。

Create table RS_GIN_Master
(id int,
 qty int,
 batch_no varchar(5),
 accept_date Datetime
 )
GO
Insert into RS_GIN_Master (id, qty, batch_no, accept_date)
values(1,10,'BT001','2018-04-06')
Insert into RS_GIN_Master (id, qty, batch_no, accept_date)
values(2,10,'BT002','2018-04-04')
Insert into RS_GIN_Master (id, qty, batch_no, accept_date)
values(3,15,'BT003','2018-05-06')
GO
----------------------------
CREATE PROC FIFO
 @TakenQty int
AS
BEGIN
 WITH cte AS (SELECT *, SUM(qty) OVER (ORDER BY accept_date, id ASC) as CumQty FROM RS_GIN_Master WHERE qty>0)
 SELECT TOP ((SELECT COUNT(*) FROM cte WHERE CumQty <@TakenQty)+1) batch_no, accept_date,
     CASE
          WHEN CumQty<@TakenQty THEN qty
              ELSE @TakenQty -(CumQty-Qty)
      END AS TakenOut
 FROM cte
END

结果

| batch_no |          accept_date | TakenOut |
|----------|----------------------|----------|
|    BT002 | 2018-04-04T00:00:00Z |       10 |
|    BT001 | 2018-04-06T00:00:00Z |        5 |

http://www.sqlfiddle.com/#!18/f7ee7/1

- Bui Quoc phong

---

我认为这是最好的答案......简短而精炼。无需循环或游标。 - amackay11