我的查詢有問題，因為我必須從不同的 stock_code 中扣除產品的數量。假設客戶將以 item_code (I0015) 購買 20 個數量的產品。首先我要在stock_code（ST0016）中減去12個數量，庫存變為0，剩下的8個數量在stock_code（ST0012）中扣除，數量的扣除是根據stock_expired的升序。如何在 MySQL 中查詢？太感謝了！高度贊賞答案。我的表的名稱是stocks_table
1 回答
手掌心
TA貢獻1942條經驗 獲得超3個贊
代碼是否低于解決方案（小提琴）？
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
- 1 回答
- 0 關注
- 93 瀏覽
添加回答
舉報
0/150
提交
取消