SQL中库存中的FIFO实现

Question

这基本上是一个库存项目，通过采购和销售分别跟踪物品的"入库"和"出库"。

库存系统遵循FIFO方法（首先购买的物品总是首先销售）。例如:

如果我们在1月、2月和3月购买了A物品 当顾客来时，我们会提供在1月购买的物品 仅当1月份物品用完后，我们才开始提供2月份的物品，以此类推。

因此，我必须在这里显示我手头的总库存和拆分，以便我可以看到所产生的总成本。

实际表格数据:

我需要获得的结果集:

我的客户坚持认为我不应该使用游标(Cursor)，那么还有其他方法吗？

- Harish

---

哪个SQL Server版本，2012可能吗？ - dean

一个光标解决方案将是迄今为止最有效的解决方案。那么，光标有什么问题吗？ - dean

@dean - 光标有很多问题- http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them 当然也有一些情况下它们可能会被使用，但这几乎不是其中之一。 - Nenad Zivkovic

@Harish - 表格是如何排序的？你提到了月份，但我没有看到它们。如果我的理解是正确的，那么排序顺序非常重要。 - StevieG

还有，只是为了明确，如果我理解要求正确的话，你结果中第一行的“价格”应该是“300.00”，而不是“500.00”吧？ - StevieG

Answer 1

正如一些评论已经提到的，CTE可以解决这个问题。

with cte as (
select item, wh, stock_in, stock_out, price, value
     , row_number() over (partition by item, wh order by item, wh) as rank
from   myTable)
select a.item, a.wh
     , a.stock_in - coalesce(b.stock_out, 0) stock
     , a.price
     , a.value - coalesce(b.value, 0) value
from cte a
     left join cte b on a.item = b.item and a.wh = b.wh and a.rank = b.rank - 1
where a.stock_in - coalesce(b.stock_out, 0) > 0

如果第二个“Item B”的价格有误（IN价格为25，OUT价格为35）。 SQL 2008 fiddle 仅供娱乐，使用SQL Server 2012和引入LEAD和LAG函数可以以更简单的方式实现相同的功能。

with cte as (
select item, wh, stock_in
     , coalesce(LEAD(stock_out) 
                OVER (partition by item, wh order by item, wh), 0) stock_out
     , price, value
     , coalesce(LEAD(value) 
                OVER (partition by  item, wh order by item, wh), 0) value_out
from   myTable)
select item
     , wh
     , (stock_in - stock_out) stock
     , price
     , (value - value_out) value
from   cte
where  (stock_in - stock_out) > 0

SQL2012 fiddle

更新
注意 -> 使用此处之前的两个查询需要数据按正确顺序排列。

如果要每天有多行细节，需要使用可靠的方法对具有相同日期的行进行排序，比如带有时间的日期列，自增ID或类似的方法。不能使用已经编写的查询，因为它们基于数据的位置。

更好的方法是将数据拆分为 IN 和 OUT，按项目、 wh 和日期排序，并在两个数据上应用 rank，如下所示：

SELECT d_in.item
     , d_in.wh
     , d_in.stock_in - coalesce(d_out.stock_out, 0) stock
     , d_in.price
     , d_in.value - coalesce(d_out.value, 0) value
FROM   (SELECT item, wh, stock_in, price, value
             , rank = row_number() OVER 
               (PARTITION BY item, wh ORDER BY item, wh, date)
        FROM   myTable
        WHERE  stock_out = 0) d_in
       LEFT JOIN
       (SELECT item, wh, stock_out, price, value
             , rank = row_number() OVER 
               (PARTITION BY item, wh ORDER BY item, wh, date)
        FROM   myTable
        WHERE  stock_in = 0) d_out
       ON d_in.item = d_out.item AND d_in.wh = d_out.wh 
      AND d_in.rank = d_out.rank
WHERE d_in.stock_in - coalesce(d_out.stock_out, 0) > 0

SQLFiddle

但是，这个查询并不是完全可靠的，同一个顺序组内数据的顺序不稳定。

如果IN.price与OUT.price不同，我没有更改查询以重新计算价格。

- Serpiton

---

对于库存价值，可能会像这样计算：((a.stock_in - coalesce(b.stock_out, 0)) * a.price) as 'Value' - cyan

@cyan，Value是货币，所以(Value_IN - Value_OUT)可能是正确的，价格需要一些计算，例如(Value_IN - Value_OUT) / (stock_in - stock_out)。 - Serpiton

我希望我聪明到足以理解像这样的TSQL解决方案。+1 - Crono

@Serpiton 我们每天会有多个进出。 - Harish

@Serpiton 我们需要细节，否则无法按细节对数据进行分组。 - Harish

Answer 2

如果游标不可用，SQLCLR存储过程可能是一个选择。这样，您可以将原始数据获取到.net对象中，使用c#或vb.net进行操作/排序，并将结果数据设置为过程的输出。这不仅可以得到您想要的，而且根据您的编程背景，甚至可能比在纯T-SQL中尝试相同的操作更容易。

- Crono

---

我想指出，对于那些正在考虑使用SQLCLR的人来说，它会带来安全漏洞。 - BilliD

@BilliD SQLCLR程序集有三个安全级别可供设置。在SAFE级别下，这应该足以解决手头的问题，并且不应该存在任何安全问题。 - Crono