优化包含重复子查询的 MySQL 查询

摄影机

我有以下查询现在运行良好,我一直在尝试优化它,因为我使用了 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] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章