MySQL仅连接选定的行 [英] MySQL join only selected rows

问题描述

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-但我希望使用数字格式".所以我这样做:

If table "invoices_elements" doesn't have any rows with "invoices_elements_enable = 1" this query return NULL - but i want "number formatted". So i do this:

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

...这很有效.但是-是更好的方法吗?

... and this of coz works. But - is better way to do it?

推荐答案

您可以

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  

请注意， ON 子句中的限制为 invoices_elements_enable = 1 ，以避免将查询转换为内部联接.

Note the restriction invoices_elements_enable = 1 is in the ON clause to avoid converting the query into an inner join.

这篇关于MySQL仅连接选定的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！