MySQL reporting查询

Question

我有以下db模式(我只提到查询所需的每个表的列)：

bills
-------
id
amount (decimal)
collection_case_id


collection_case
------------------
id


payments
----------
id
bill_id
amount (decimal)
type (char(2)) - "CH" stands for check payment, field used for polymorphism 

check_payments
---------------
payment_id

对于一组收款箱，我需要获得从支票付款中获得的总利息。在检索了我的一组收款案例标准的所有账单记录后，我目前正在内存中进行这些计算。对于每个账单，如果收到的支票总数大于账单金额，我只需添加收到的支票总数-账单金额。

例如，我收到的总查询如下所示(收集案例1和2)：

select sum(payment.amount) as received 
from bills bill
cross join payments payment inner join check_payments checkpayment 
on 
payment.id=checkpayment.payment_id 
where payment.type='CH'
 and payment.bill_id=bill.id 
 and (bill.collection_case_id in (1 , 2))

Answer 1

我不太确定这是不是你的意思。

select IF(sum(payment.amount) > bill.amount, sum(payment.amount)-bill.amount, 0) as interest  
from bills bill
cross join payments payment inner join check_payments checkpayment 
on 
payment.id=checkpayment.payment_id 
where payment.type='CH'
 and payment.bill_id=bill.id 
 and (bill.collection_case_id in (1 , 2))