猿问

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

我的查询有问题,因为我必须从不同的 stock_code 中扣除产品的数量。

假设客户将以 item_code (I0015) 购买 20 个数量的产品。首先我要在stock_code(ST0016)中减去12个数量,库存变为0,剩下的8个数量在stock_code(ST0012)中扣除,数量的扣除是根据stock_expired的升序。

如何在 MySQL 中查询?太感谢了!高度赞赏答案。我的表的名称是stocks_table

莫回无
浏览 79回答 1
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
随时随地看视频慕课网APP
我要回答