sql - 使用 SQL 在库存中实现 FIFO

Question

这基本上是一个库存项目，分别通过采购和销售跟踪商品的“入库”和“缺货”。

库存系统遵循先进先出法(先购买的元素总是先出售)。例如:

If we purchased Item A in months January, February and March When a customer comes we give away items purchased during January only when the January items are over we starts giving away February items and so on

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

实际表数据:

我需要获取的结果集:

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

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

如果第二个“商品 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 和数据排序，并对这两个数据应用排名，如下所示:

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 不同，我没有更改查询来重新计算价格