請教大家一下 MySql 語句的問題
欄位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);
已邀請的邦友 {{ invite_list.length }}/5
正常我會將 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用的方式。這可能你能提供資料串我再跑一次看看。
先簡單這樣給你
你有沒有考慮先用子查詢先把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
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
iThome鐵人賽
看影片追技術