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

Question

我有以下查询现在运行良好，我一直在尝试优化它，因为我使用了 4 次相同的子查询。想出一个更好/更聪明的解决方案会很棒。谢谢

这里是查询:

  select      invoices.invoice_id    ,invoices.invoice_amount    ,(      select SUM(invoice_payment_amount) as total      FROM invoice_payments      where invoice_payment_invoice_id = invoices.invoice_id     ) as payments    ,round((invoices.invoice_amount-(      select SUM(invoice_payment_amount) as total      FROM invoice_payments      where invoice_payment_invoice_id = invoices.invoice_id     )),2) as balance   from invoices  where (    round((invoices.invoice_amount -          (select SUM(invoice_payment_amount) as total          FROM invoice_payments          where 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 fiddle :http://sqlfiddle.com/#!9/aecea/1

Answer 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) 上建立索引。