Mysql的股票计算查询

Question

我有如下表数据

Product Reason Qty 
Pepsi IN  10 
Pepsi Out 2 
Pepsi In  15 
Pepsi Out 5 
Coke IN  100 
Coke Out 20 
Coke In  35 
Coke Out 25 
Fanta Out  55 

，我想结果如下图所示：

Product Qty 
Pepsi 18 
Coke 90 
Fanta -55 

我已经写了下面的查询，但我没有在输出端获得芬达。

select indata.Name,indata.PRODUCTS_ID, (indata.QTY - outdata.QTY) as `QTY` from 
(
(select 
    distinct 
    stock.PRODUCTS_ID, 
    stock.Name, 
    stock.QTY 
    from stock where reason in ('IN','REFUND') 
    ) indata, 
(select 
    distinct 
    stock.PRODUCTS_ID, 
    stock.Name, 
    stock.QTY 
    from stock where reason in ('OUT','WASTE') 
) outdata 
); 

Answer 1

它看起来像一个很好的候选人条件聚集：

SELECT Product, 
     SUM(CASE WHEN reason IN ('IN','REFUND') THEN Qty 
       WHEN reason IN ('OUT','WASTE') THEN -Qty 
       ELSE NULL END) AS Qty 
FROM stock 
GROUP BY Product; 

LiveDemo