MySQL 基于可用库存的行 Netty 结算

Question

我有一个 MySQL 数据库，其中包含订单表和具有可用库存的零件号表。

例如

==Orders Table==
part_number
order_date
qty

==Stock Table==
part_number
stock_qty

假设可用库存将分配给最近的订单，我如何从订单表中选择没有库存的行。

订单表可能有 1.5 到 200 万条记录，因此将整个数据集读入内存并首先在 PHP 中进行处理并不理想。

===更新和最终答案===

戈登的回答为我提供了足够的信息，以获得对我有用的确切结果。我已将其包含在下面。

select * from (
select o.*,s.stock_qty,(@netted_stock := if(@pn = o.part_number,@netted_stock-sumqty,s.stock_qty-sumqty)) as netted_stock
from (select o.*,
             (@sumqty := if(@pt = part_number, @sumqty + qty,
                            if(@pt := part_number, qty, qty)
                           )
             ) as sumqty



      from orders o cross join
           (select @pn := -1, @sumqty := 0, @netted_stock:= 0) params
      order by part_number, order_date desc
     ) o join
     stock s
     on o.part_number = s.part_number
     ) s

这是它给我的结果。

Answer 1

您需要每个订单的累积库存量。你可以通过变量得到它。剩下的只是简单的逻辑:

select o.*
from (select o.*,
             (@sumqty := if(@pt = part_number, @sumqty + qty,
                            if(@pt := part_number, qty, qty)
                           )
             ) as sumqty
      from orders o cross join
           (select @pn := -1, @sumqty := 0) params
      order by part_number, order_date desc
     ) o join
     stock s
     on o.part_number = s.part_number
where s.stock_qty < o.sumqty;