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

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(&nbsp; &nbsp; select *, case when cumulsum <= TotalRequiredQuantity then 0 else cumulsum-TotalRequiredQuantity end NewQuantity&nbsp;&nbsp; &nbsp; &nbsp;from(&nbsp; &nbsp; &nbsp; &nbsp;select *, 20 TotalRequiredQuantity,/*Set valid quantitity*/&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sum(stock_quantity) over(partition by item_code order by stock_expired) cumulsum&nbsp; &nbsp; &nbsp; &nbsp;from stocks_table&nbsp; &nbsp; &nbsp; &nbsp;where item_code = 'I0015'/*Set valid item_code*/&nbsp; &nbsp; )q)q1where stock_quantity>=NewQuantity)update stocks_table stjoin cte on st.id=cte.idset st.stock_quantity = NewQuantity没有公用表表达式：update stocks_table stjoin(&nbsp; select * from(&nbsp; &nbsp; select *&nbsp; &nbsp; ,case when cumulsum <= TotalRequiredQuantity then 0 else cumulsum-TotalRequiredQuantity end NewQuantity&nbsp;&nbsp; &nbsp; &nbsp;from(&nbsp; &nbsp; &nbsp; &nbsp;select *, 20 TotalRequiredQuantity,/*Set valid quantitity*/&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sum(stock_quantity) over(partition by item_code order by stock_expired) cumulsum&nbsp; &nbsp; &nbsp; &nbsp;from stocks_table&nbsp; &nbsp; &nbsp; &nbsp;where item_code = 'I0015'/*Set valid item_code*/&nbsp; &nbsp; )q&nbsp; )q1&nbsp; where stock_quantity>=NewQuantity)cte on st.id=cte.idset st.stock_quantity = NewQuantity