mysql - Left join Count 扰乱 left join SUM

Question

当我添加一个左连接来获取外部表的计数时，它将我的其他左连接表的总和值乘以计数，我也不能在这里使用不同的总和，因为两个值可以相同:

SELECT c.id as company_id, SUM(ct.amount) as total_billed, count(l.id) as load_count
FROM tbl_companies c
LEFT JOIN tbl_company_transactions ct ON c.id = ct.company_id
LEFT JOIN tbl_loads l ON c.id = l.company_id
GROUP BY c.id;

Answer 1

您需要预先聚合数据:

SELECT c.id as company_id, ct.total_billed,  
       l.load_count
FROM tbl_companies c LEFT JOIN
     (SELECT ct.company_id, SUM(ct.amount) as total_billed
      FROM tbl_company_transactions ct
      GROUP BY ct.company_id
     ) ct
     ON c.id = ct.company_id LEFT JOIN
     (SELECT l.company_id, COUNT(*) as load_count
      FROM tbl_loads l
      GROUP BY l.company_id
     ) l
     ON c.id = l.company_id;

正如您所观察到的，JOIN 增加了行数并影响了聚合。