php - Mysql:SUM 连接表值

Question

我在连接和 SUM() 方面遇到问题。

我有两个表，agents(id, name) 和 orders(id, agent_id,total)。

现在，我需要一份所有代理的列表，计算他们下的订单数量以及所有订单的总数。这是我的基本查询:

SELECT
    agents.*,
    COUNT(DISTINCT orders.id) total_orders,
    SUM(orders.total) total_amount
FROM agents
LEFT JOIN orders ON agents.id = orders.agent_id
GROUP BY agents.id

total_orders 正确，但 total_amount 不正确。每个代理都有错误的 SUM()，甚至那些没有订购任何东西的代理也有一个值。

是否可以在一个查询中实现？我不想在循环中再次查询。

Answer 1

您不需要左连接，请将 LEFT JOIN 替换为 JOIN:

SELECT
    agents.*,
    COUNT(DISTINCT orders.id) total_orders,
    SUM(orders.total) total_amount
FROM agents
JOIN orders ON agents.id = orders.agent_id
GROUP BY agents.id

您得到的错误结果是由于 左连接 将从 orders 表中检索一行，即使没有记录具有相同的 agent_id到代理.id

另一方面，单个联接不会检索没有订单的代理。

哦，那么您需要所有代理，并且希望那些没有订单的代理总数为 0...那么左连接应该可以工作。否则你可以这样做:

SELECT
    agents.*,
    COUNT(DISTINCT orders.id) total_orders,
    SUM(CASE WHEN orders.id IS NULL then 0 ELSE orders.total END) total_amount
FROM agents
LEFT JOIN orders ON agents.id = orders.agent_id
GROUP BY agents.id