我有这样的桌子:
PRODUCTID REORDER_QTY TYPE ADJUST_TYPE
------------- --------------- -------- -----------------
4343 2.00 Transfer ADD
4730 2.00 Transfer ADD
4730 2.00 Transfer ADD
4730 1.00 Transfer DEDUCT
3643 20.00 Transfer ADD
3643 3.00 Transfer DEDUCT 我如何编写一个sql来通过REORDER_QTY的PRODUCTID和ADJUST_TYPE来完成它的计算,然后再用添加和减去减去E 210的总和来得到它的平衡
示例sql结果输出:
PRODUCTID TYPE Total_Balance
------------- ---------- ---------------
4730 Transfer ((SUM the REORDER_QTY by ADD) - (SUM the REORDER_QTY by DEDUCT))
3643 Transfer ((SUM the REORDER_QTY by ADD) - (SUM the REORDER_QTY by DEDUCT))
4343 Transfer ((SUM the REORDER_QTY by ADD) - (SUM the REORDER_QTY by DEDUCT))任何帮助都会很好,谢谢
发布于 2014-09-03 02:38:32
您只需要有条件的聚合:
select productid, type,
sum(case when adjust_type = 'ADD' then reorder_qty
when adjust_type = 'DEDUCT' then - reorder_qty
else 0
end) as total_balance
from atable
group by productid, type;发布于 2014-09-03 02:40:41
因为这是mysql,所以可以这样做:
select
productid,
sum((2 * (adjust_type = 'ADD') - 1) * reorder_qty) as total_balance
from mytable
group by productid这是因为在mysql中,布尔条件产生的结果为true为1,false为0,因此(2 * (adjust_type = 'ADD') - 1)为ADD和-1生成1,从而使算法更简单。
https://stackoverflow.com/questions/25635344
复制相似问题