高效的查询来获取账户余额

Question

我有这样的样品数据：http://sqlfiddle.com/#!9/124b62

什么是获得现金簿账户的开闭平衡的最佳方式（有效的查询）？

一种方法是找到总积分并从总借方中扣除。

SELECT sum(amount) as credit_total 
FROM `cash_book` 
WHERE `type` = 'credit' 
AND `account_holder_id` =1 
AND `created_at` >= '2016-07-31 00:00:00'; 


SELECT sum(amount) as debit_total 
FROM `cash_book` 
WHERE `type` = 'debit' 
AND `account_holder_id` =1 
AND `created_at` >= '2016-07-31 00:00:00'; 

有人可以提出更好的查询吗？

Answer 1

单次运行总

SELECT sum(case when `type` = 'credit' then -amount else amount end) as total 
FROM `cash_book` 
WHERE AND `account_holder_id` =1 and `created_at` >= '2016-07-31 00:00:00';