查询交易中的更新库存数量

Question

我的查询有问题，因为我必须从不同的 stock_code 中扣除产品的数量。假设客户将以 item_code (I0015) 购买 20 个数量的产品。首先我要在stock_code（ST0016）中减去12个数量，库存变为0，剩下的8个数量在stock_code（ST0012）中扣除，数量的扣除是根据stock_expired的升序。如何在 MySQL 中查询？太感谢了！高度赞赏答案。我的表的名称是stocks_table

Answer 1

代码是否低于解决方案（小提琴）？

with cte as(

select * from(

    select *, case when cumulsum <= TotalRequiredQuantity then 0 else cumulsum-TotalRequiredQuantity end NewQuantity 

     from(

       select *, 20 TotalRequiredQuantity,/*Set valid quantitity*/

          sum(stock_quantity) over(partition by item_code order by stock_expired) cumulsum

       from stocks_table

       where item_code = 'I0015'/*Set valid item_code*/

    )q

)q1

where stock_quantity>=NewQuantity)

update stocks_table st

join cte on st.id=cte.id

set st.stock_quantity = NewQuantity

没有公用表表达式：

update stocks_table st

join(

  select * from(

    select *

    ,case when cumulsum <= TotalRequiredQuantity then 0 else cumulsum-TotalRequiredQuantity end NewQuantity 

     from(

       select *, 20 TotalRequiredQuantity,/*Set valid quantitity*/

          sum(stock_quantity) over(partition by item_code order by stock_expired) cumulsum

       from stocks_table

       where item_code = 'I0015'/*Set valid item_code*/

    )q

  )q1

  where stock_quantity>=NewQuantity

)cte on st.id=cte.id

set st.stock_quantity = NewQuantity