查询交易中的更新库存数量 [英] Query for update stock quantity in transaction

问题描述

我在查询时遇到了麻烦，因为我必须从不同的stock_code中扣除产品的数量。

I'm having trouble with my query because I have to deduct the quantity of the product from a different stock_code.

假设客户将用以下价格购买产品20个数量的item_code（I0015）。首先，我想将12个数量扣除到stock_code（ST0016）中，库存将变为0，其余8个数量将在stock_code中扣除（ST0012），数量的减少基于stock_expired的升序日期。

Let say the customer will buy a product in item_code (I0015) for 20 quantities. First I want to deduct the 12 quantities to the stock_code (ST0016) and the stock will become 0, and the remaining 8 quantities will deduct in stock_code (ST0012), the deduction of quantity is based on the ascending date of stock_expired.

如何在MySQL中查询呢？非常感谢！高度赞赏答案。我桌子的名字是stocks_table

How do I query that in MySQL? Thank you so much! The answer is highly appreciated. The name of my table is stocks_table

推荐答案

代码是否在解决方案下方（ fiddle ）？

Is code below the solution (fiddle)?

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

这篇关于查询交易中的更新库存数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！