php - SUM 或 SUBTRACTION 在 mysql 查询中的条件

Question

我想在 mysql 查询中根据特定条件执行 SUM 或 SUBTRACTION。这是:SQLFiddle

条件:

如果 ce_type 是 IN OR NULL 然后在变量中添加 payment_amount

和

如果 ce_type 是 OUT 那么我想从该变量中减去 payment_amount。

我用这个查询试过了。但是，我不知道如何在此处放置条件。

SELECT SUM(payment_amount) as payment_amount
FROM customer_payment_options
WHERE (real_account_id='11' AND real_account_type='HQ')
AND company_id='1'

Answer 1

如果我理解正确的话，你只需要条件聚合:

SELECT SUM(CASE WHEN ce_type = 'IN' or ce_type is NULL then payment_amount
                WHEN ce_type = 'OUT' then - payment_amount
           END) as payment_amount
FROM customer_payment_options
WHERE real_account_id = '11' AND real_account_type = 'HQ' AND company_id = '1';