iT邦幫忙

0

請教各位高手 MySql 語法求庫存

請教大家一下 MySql 語句的問題

https://ithelp.ithome.com.tw/upload/images/20200610/20127817SyzvNPs6rW.png

欄位stock_type:

in: 進貨

in_return: 進貨後退貨 (amount要轉為負值)

out: 銷貨 (amount要轉為負值)

out_return: 銷貨後退貨

adjust: 手動調整庫存

transfer: 貨物轉倉 (out_warehouse_id 轉出倉庫id, in_warehouse_id 轉入倉庫id)
例如: id: 42 => 產品id:19 => 1號倉 -30 / 2號倉 +30

select sum( IF(stock_type = 'out' or stock_type = 'in_return', -1 * amount, amount)) as stock_total 
from stock_items where stock_type != 'transfer'
group by warehouse_id, product_id

寫到這裡除了transfer的庫存可以得出來 但transfer就卡住了
transfer的條件不知道該如何加入語句中

想請教高手們 如何求各倉庫的各產品庫存?

感謝!

補充: 隨文附上 SQL insert資料

INSERT INTO `stock_items` (`id`, `stock_id`, `stock_type`, `out_warehouse_id`, `in_warehouse_id`, `warehouse_id`, `product_id`, `amount`)
VALUES
	(1, 16, 'in', NULL, NULL, 1, 1, 200),
	(2, 16, 'in', NULL, NULL, 1, 1, 25.52),
	(3, 16, 'in', NULL, NULL, 1, 6, 22),
	(4, 7, 'in', NULL, NULL, 2, 6, 12),
	(5, 6, 'in', NULL, NULL, 2, 5, 112),
	(7, 1, 'out', NULL, NULL, 2, 5, 11),
	(8, 1, 'out_return', NULL, NULL, 2, 6, 25),
	(10, 1, 'adjust', NULL, NULL, 1, 1, -50),
	(11, 1, 'adjust', NULL, NULL, 1, 1, -20),
	(12, 17, 'in', NULL, NULL, 1, 7, 100),
	(13, 17, 'in', NULL, NULL, 1, 19, 20),
	(14, 18, 'in', NULL, NULL, 1, 19, 398),
	(20, 18, 'in', NULL, NULL, 1, 13, 20),
	(27, 7, 'in_return', NULL, NULL, 1, 19, 1),
	(28, 7, 'in_return', NULL, NULL, 1, 13, 1),
	(29, 2, 'out', NULL, NULL, 1, 19, 20),
	(30, 2, 'out', NULL, NULL, 1, 7, 20),
	(31, 2, 'out_return', NULL, NULL, 1, 19, 5),
	(32, 2, 'out_return', NULL, NULL, 1, 7, 5),
	(35, 21, 'in', NULL, NULL, 1, 5, 12),
	(36, 2, 'adjust', NULL, NULL, 1, 19, -2),
	(37, 2, 'adjust', NULL, NULL, 1, 13, -1),
	(41, 5, 'adjust', NULL, NULL, 1, 19, -1),
	(42, 4, 'transfer', 1, 2, NULL, 19, 30),
	(43, 5, 'transfer', 1, 2, NULL, 19, 10);

看更多先前的討論...收起先前的討論...
資料要有,最好能讓幫友可以很方便的輸入測試驗證的資料.
你用圖片,就只是顯示,不方便使用,最好能補上資料.
kevin0523 iT邦新手 5 級 ‧ 2020-06-10 14:22:13 檢舉
好的 已經補上囉 感謝
難得有出現一個會補資料的.不錯喔.
kevin0523 iT邦新手 5 級 ‧ 2020-06-10 17:30:04 檢舉
這題也可請前輩指教下嗎? :)
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
1

正常我會將 transfer 的資料給拆開計算
畢竟你這樣的資料架構是沒辦法有很好的處理方式

所以整體起來會是如下的樣子

SELECT wid,pid,sum(amount) FROM (
    SELECT warehouse_id AS wid,product_id AS pid,amount
    FROM stock_items 
    WHERE stock_type IN ('in','out_return','adjust')
    UNION 
    SELECT warehouse_id AS wid,product_id AS pid,amount*(-1) AS amount
    FROM stock_items 
    WHERE stock_type IN ('in_return','out')
    UNION 
    SELECT out_warehouse_id AS wid,product_id AS pid,amount*(-1) AS amount
    FROM stock_items 
    WHERE stock_type IN ('transfer')
    UNION 
    SELECT in_warehouse_id AS wid,product_id AS pid,amount
    FROM stock_items 
    WHERE stock_type IN ('transfer')
) AS subdb GROUP BY wid,pid

這是結合用的方式。效能怎麼樣不清楚。
另外還有join用的方式。這可能你能提供資料串我再跑一次看看。
先簡單這樣給你

看更多先前的回應...收起先前的回應...
kevin0523 iT邦新手 5 級 ‧ 2020-06-10 14:28:21 檢舉

好的 謝謝 已提供SQL資料囉

所以您的建議是把transfer再拆出一張表
會比較好操作嗎?

這只是其中的手法。
畢竟你的 transfer 在統計上會算到兩個庫的統計。
不分開計算你很難統計的。

kevin0523 iT邦新手 5 級 ‧ 2020-06-10 16:51:19 檢舉

浩瀚大
把您的SQL帶入查詢 好像查詢出來資料為空
可以麻煩您再指點下嗎? 謝謝

抱歉,我忘了給子查尋as了。
我重新編輯過了

有你的資料測試過了

kevin0523 iT邦新手 5 級 ‧ 2020-06-11 13:34:40 檢舉

謝謝浩瀚大

0
kery1155
iT邦新手 5 級 ‧ 2020-06-10 09:53:17

你有沒有考慮先用子查詢先把amount的正負補上再做sum
例如說
select
...id, sum(total_amount)
from
...(select
......id,
......case
.........when stock_type='in' then amount
.........when stock_type='in_return' then amount*(-1)
........./看有多少種就補多少/
......end as total_amount
...from
......table)
group by id

這樣子查詢應該就變成
id amount
1 200
. .
27 -1

kevin0523 iT邦新手 5 級 ‧ 2020-06-10 14:29:05 檢舉

好的 謝謝 我來試試看

1
REX
iT邦新手 4 級 ‧ 2020-06-10 20:59:27

MSSQL語法,參考看看

select warehouse_id , product_id, sum(amount) as amount from  (select warehouse_id , product_id, 
sum(case when stock_type='in' then amount  
when stock_type='out' then amount*(-1)
when stock_type='in_return' then amount*(-1)
else amount end)  amount from stock_items 
where warehouse_id is not null group by warehouse_id,product_id 
UNION ALL 
select out_warehouse_id as warehouse_id,product_id,amount*(-1) amount from stock_items where stock_type='transfer'
UNION ALL 
select in_warehouse_id as warehouse_id,product_id, amount from stock_items where stock_type='transfer') t1 
group by warehouse_id , product_id order by warehouse_id , product_id

fiddle SQL測試

kevin0523 iT邦新手 5 級 ‧ 2020-06-11 13:33:04 檢舉

感謝提供解答還提供網站測試

我要發表回答

立即登入回答