MYSQL中有条件的减法

Question

我想在mysql查询的某些条件下做SUM和subtract

我已经试过了，假设$balance= 655.000.000，则my_table内的总金额= 80.000.000印尼盾和500.000美元

$currency_id = 1;
$balance = 655.000.000;
DB::table('my_table as a')
->join('master_currencies as b', 'a.master_currency_id', 'b.id')
->where('a.id', $id)
->selectRaw("SUM(
CASE
WHEN $currency_id = a.master_currency_id THEN $balance - a.amount
ELSE
a.amount
END) AS net_value, b.symbol, b.id as master_currency_id"
)
->groupBy('b.id')
->get();

上面的代码应该给出结果

[
{"net_value":"575.000.000","symbol":"IDR","master_currency_id":1},
{"net_value":"500.000","symbol":"USD","master_currency_id":2}
]

但是我得到的结果是

[
{"net_value":"1.885.000.000","symbol":"IDR","master_currency_id":1},
{"net_value":"500.000","symbol":"USD","master_currency_id":2}
]

注意:请忽略数字之间的点，在我的数据库中，我不使用这些点。仅供查询使用

Answer 1

如果您有多行IDR，则可能是操作顺序出现问题。

例如，假设你必须值(100.000.000)和(50.000.000)，那么对于IDR SUM(655.000.000-100.000.000, 655.000.000-50.000.000)=1.160.000.000，而不是655.000.000-SUM(100.000.000,50.000.000)=505.000.000，问题在于你聚合的组中的每一行都得到一个干净的$balance。

你可以这样做来解决这个问题:

$currency_id = 1;
$balance = 655.000.000;
DB::table('my_table as a')
->join('master_currencies as b', 'a.master_currency_id', 'b.id')
->where('a.id', $id)
->selectRaw("
(CASE 
WHEN a.id=$currency_id $balance-SUM(a.amount)
ELSE
SUM(a.amount)  
END) AS net_value, b.symbol, b.id as master_currency_id")
->groupBy('b.id')
->get();