您需要预先聚合数据：

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 乘以行数并影响聚合。

gordon的答案更具可伸缩性，但对于这个特定的查询，您只需要一个子查询，这也可能会提高性能，因为预聚合数据上的连接可能无法使用索引。

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

重要的是要掌握，如果你需要一个聚合函数的结果，比如 SUM() 或者 COUNT() ，执行多行的多个联接时需要小心。

您可以隔离聚合统计信息，然后连接结果。

WITH 
tranStats AS (
    SELECT company_id, SUM(amount) AS total_billed
    FROM tbl_company_transactions
    GROUP BY company_id
),
loadStats AS (
    SELECT company_id, COUNT(1) AS load_count
    FROM tbl_loads
    GROUP BY company_id
)
SELECT id, total_billed, load_count
FROM tbl_companies c
LEFT JOIN tranStats t ON t.company_id = c.id
LEFT JOIN loadStats l ON l.company_id = c.id