mysql - 按方向列分组和求和数量

Question

Good Day 伙计们，我有没有办法按项目分组并使用方向列汇总其数量？

我想添加新列positive_quantity（方向是1、4、5）和negative_quantity（方向是2、3、6）

这是我的代码：

SELECT 
    il.warehouse_id,
    w.code as warehouse_code,
    w.name as warehouse_name,
    il.item_id, 
    i.code as item_code,
    i.name as item_name,
    il.lot, 
    il.date_expiry, 
    il.direction,
    il.location_id,
    //SUM(il.qty), as posive_quantity (base on direction 1, 4, 5)
   //SUM(il.qty), as negative_quantity (base on direction 2, 3, 6)
FROM warehouses w
INNER JOIN inventory_logs il
ON w.id = il.warehouse_id
INNER JOIN items as i
ON il.item_id = i.id
WHERE il.location_id IN (1,3) AND il.date_posted BETWEEN '2019-01-01' AND '2019-01-31'
GROUP BY 
il.warehouse_id,
il.item_id, 
il.lot, 
il.date_expiry, 
il.location_id,
direction

这是我想要的输出：

先感谢您。我也尝试过使用临时表，但它给了我错误。就像使用相同的临时错误一样。

标签： mysql

Answer 1

您可以使用条件总和：

SELECT 
    il.warehouse_id,
    w.code as warehouse_code,
    w.name as warehouse_name,
    il.item_id, 
    i.code as item_code,
    i.name as item_name,
    il.lot, 
    il.date_expiry, 
    il.location_id,
    sum( if( il.direction in (1,4,5), il.qty, 0 ) ) as positive_quantity,
    sum( if( il.direction in (2, 3, 6), il.qty, 0 ) ) as negative_quantity
FROM warehouses w
INNER JOIN inventory_logs il
ON w.id = il.warehouse_id
INNER JOIN items as i
ON il.item_id = i.id
WHERE il.location_id IN (1,3) AND il.date_posted BETWEEN '2019-01-01' AND '2019-01-31'
GROUP BY 
il.warehouse_id,
il.item_id, 
il.lot, 
il.date_expiry, 
il.location_id,

顺便说一句，您的图像与查询匹配。最好将问题发布为文本而不是图像，最好是 SQLFiddle。