我有以下查询现在运行良好,我一直在尝试优化它,因为我使用了 4 次相同的子查询。提出更好/更智能的解决方案会很棒。谢谢
这是查询:
选择 invoices.invoice_id ,invoices.invoice_amount ,( SELECT SUM(invoice_payment_amount)为总 FROM invoice_payments 其中invoice_payment_invoice_id = invoices.invoice_id )作为付款 ,圆((invoices.invoice_amount-( SELECT SUM(invoice_payment_amount)为总 FROM invoice_payments 其中invoice_payment_invoice_id =发票.invoice_id )),2) 作为 发票的余额, 其中( round((invoices.invoice_amount - (select SUM(invoice_payment_amount) as total FROM invoice_payments) 其中invoice_payment_invoice_id = invoices.invoice_id) ),2) ) > 0 or ( round((invoices.invoice_amount - (select SUM(invoice_payment_amount) as total FROM invoice_payments where invoice_payment_invoice_id = invoices.invoice_id) ),2) ) IS NULL order by balance
SQL 小提琴:http ://sqlfiddle.com/#!9/aecea/1
只需使用子查询:
select i.invoice_id, i.invoice_amount, i.payments,
round((i.invoice_amount- i.payments), 2) as balance
from (select i.*,
(select sum(ip.invoice_payment_amount)
from invoice_payments ip
where ip.invoice_payment_invoice_id = i.invoice_id
) as payments
from invoices i
) i
where round((i.invoice_amount- i.payments), 2) > 0 or
round((i.invoice_amount- i.payments), 2) is null
order by balance;
为了获得更好的性能,您需要在invoice_payments(invoice_payment_invoice_id, invoice_payment_amount)
.
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句