左连接计数干扰左连接和

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

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（），那么当您使用多行执行多个连接时，您需要小心。

Answer 2

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

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

Answer 3

您需要预先聚合数据：

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会增加行数并影响聚合。