MySQL仅连接选定的行

Question

SELECT invoices.number_formatted, SUM(invoices_elements.netto)
FROM invoices
LEFT JOIN invoices_elements ON invoices_elements_code_invoices_id = invoices_id
WHERE invoices_enable = 1
AND invoices_elements_enable = 1
GROUP BY invoices_elements_code_invoices_id

如果表"invoices_elements“没有任何带有"invoices_elements_enable = 1”的行，则此查询返回NULL -但我想要“数字格式化”。所以我这样做：

SELECT SUM(netto)
FROM (invoices)
LEFT JOIN (SELECT * FROM invoices_elements WHERE invoices_elements_enable = 1) ON invoices_elements_code_invoices_id = invoices_id
WHERE invoices_enable = 1
GROUP BY invoices_elements_code_invoices_id

..。这就是coz的工作原理。但是-有没有更好的方法呢？

Answer 1

你可以做到

SELECT SUM(netto)
FROM   invoices
       LEFT JOIN invoices_elements
         ON invoices_elements_code_invoices_id = invoices_id
            AND invoices_elements_enable = 1
WHERE  invoices_enable = 1
GROUP  BY invoices_elements_code_invoices_id  

注意，限制invoices_elements_enable = 1在ON子句中，以避免将查询转换为inner join。