mysql - 任何人都可以简化这个 mysql 查询

Question

任何人都可以简化这个 MYSQL 查询

同一个表，我想将一种类型的值的总和减去另一种类型的值

例如:sum( type1 ) - sum (type 2)

SELECT (SELECT sum(exp_amount) FROM `expenses` WHERE `account_id`  = 1 && `type_id` = 2)
     - (SELECT sum(exp_amount) FROM `expenses` WHERE `account_id`  = 1 && `type_id` = 1) AS balance FROM  `expenses` 
LIMIT 0 , 1

指导我如何在 Cake php 中处理此查询 注意:不直接使用此查询 ->query (...)

Answer 1

您可以使用下面基于案例的聚合通过单次扫描表来获取余额

SELECT Coalesce(sum(CASE WHEN type_id=2 THEN exp_amount END), 0) - Coalesce(sum(CASE WHEN type_id =1 THEN exp_amount END),0) AS balance
FROM expenses
WHERE account_id = 1
  AND type_id IN (1,2)