分区上的 SQL Sum

Question

我有 6000 件库存商品，库存分为 2 个批处理，分别为 4000 件和 2000 件。

根据销售订单，我试图预测哪一批库存可以满足每个订单。

例如:

SELECT product, order_qty, price, date_required FROM orders Where product = 'X'

这会产生这张表:

product | order_qty | price |  date_required
------------------------------------------------------
 X      | 300       | 1.01  |   01/07/2018
 X      | 500       | 1.00  |   03/07/2018
 X      | 700       | 1.05  |   02/07/2018
 X      | 1000      | 1.00  |   01/08/2018
 X      | 2000      | 1.00  |   20/07/2018
 X      | 600       | 1.06  |   10/07/2018
 X      | 500       | 1.10  |   11/07/2018
 X      | 400       | 1.00  |   10/08/2018

然后我有另一个查询例如:

SELECT batch, product, qty, date_received FROM batches where product = 'X'

这将生成如下表格:

 batch  |product| qty   |  date_received
-------------------------------------------
 ABC123 | X     | 2000  |   01/04/2018
 987ZYX | X     | 4000  |   01/01/2018

因此，我想使用这两个表来预测将从哪一批库存订单中挑选。库存使用 FIFO 系统，因此根据表格，它必须首先使用批处理代码 987ZYX 批处理来完成订单。我正在寻找将其生成为表的查询:

product | order_qty | price |  date_required|   batch
------------------------------------------------------------------
 X      | 300       | 1.01  |   01/07/2018  |   987ZYX
 X      | 500       | 1.00  |   03/07/2018  |   987ZYX  
 X      | 700       | 1.05  |   02/07/2018  |   987ZYX
 X      | 1000      | 1.00  |   01/08/2018  |   ABC123
 X      | 2000      | 1.00  |   20/07/2018  |   987ZYX & ABC123
 X      | 600       | 1.06  |   10/07/2018  |   987ZYX
 X      | 500       | 1.10  |   11/07/2018  |   987ZYX
 X      | 400       | 1.00  |   10/08/2018  |   ABC123

我已经根据要求的日期选择了上面的批处理，例如，需要的第一个订单是 1/7/18 这样将从批处理 987ZYX 中删除 300 件，然后我继续使用此方法直到我必须完成下一批的订单

我知道我可以使用 SUM over 方法，但我如何使用它来确保它不会使用比批处理中可用的库存更多的库存，正如您在上面看到的那样我想显示两个批处理如果如果需要从两个中完成，则可能。

Answer 1

首先，我讨厌日期(部分原因是我的生日是 2 月 29 日，部分原因是我的 'd' 键不能正常工作)，所以我为每个订单和每个批处理创建索引，并且我认为应该处理订单从最小可能的批处理索引开始按索引的升序排列。我也不会在下订单时检查商品是否有货，以及许多其他事情。

正如@GordonLinoff 所建议的，我们将使用累计和。我为表 #orders 和 #batch 创建了具有累积数量的新表(我们只是总结了订单/批处理的所有数量，id 为当前)。然后我们递归地计算批处理......好吧，我们以某种方式找到了必要的批处理。我想我们不需要递归，但我只是学会了如何在 SQL 中使用它，所以我很自豪能在不需要的地方使用它。哦，我也忘了检查批处理和订单中的产品是否相同......

drop table if exists #orders, #batch, #orders_cumulative, #batch_cumulative

create table #orders (id int, product varchar(10), order_qty int, price float, date_required date)
insert into #orders VALUES
(1, 'x', 300, 1.01, '20180107'),
(2, 'y', 500, 1, '20180307'),
(3, 'x', 700, 1.05, '20180207'),
(4, 'x', 1000, 1, '20180108'),
(5, 'x', 2000, 1, '20180402'),
(6, 'x', 600, 1.06, '20180302'),
(7, 'y', 100, 1, '20180203'),
(8, 'x', 100, 1, '20180402')

create table #batch (id int, batch varchar(10), product varchar(10), qty int)
insert into #batch VALUES
(1, 'abc', 'x', 1000),
(2, 'zxc', 'x', 1000),
(3, 'sd', 'x', 2000),
(4, 'eiso', 'y', 10000)

SELECT  o.*
        ,(select sum(order_qty) from #orders where id <= o.id and product = o.product) cumulative_qty
INTO    #orders_cumulative
from    #orders o

select  b.*
        ,isnull((select sum(qty) from #batch where id < b.id and product = b.product), 0) cumulative_ex_qty
        ,(select sum(qty) from #batch where id <= b.id and product = b.product) cumulative_qty
into    #batch_cumulative
FROM    #batch b

select top 10 * from #orders_cumulative
select top 10 * from #batch_cumulative

select  oc.*
        ,case when bc.cumulative_ex_qty > oc.cumulative_qty - oc.order_qty then convert(varchar(10), isnull(b1.batch, '') + ', ' + b2.batch) else b2.batch end batch
from    #orders_cumulative oc 
join    #batch_cumulative bc on oc.cumulative_qty between bc.cumulative_ex_qty and bc.cumulative_qty and oc.product = bc.product
join    #batch b2 on b2.id = bc.id 
left JOIN   #batch b1 on b1.id = bc.id - 1

编辑:除了将日期时间更改为 id 之外，我更正了主要问题(每个产品的单独计算，消除了愚蠢的递归)。