mysql - SUM 多个计数和分组依据

Question

我需要按用户名和帐户名称分组的总操作 ID(通话、 session 和任务)计数

我试过了，但总数不正确

SELECT count(calls.id) + count(meetings.id) + count(tasks.id) AS 'total', users.user_name AS 'name', GROUP_CONCAT(accounts.name) AS 'accounts' 
FROM accounts, calls, users, meetings, tasks
WHERE accounts.id = calls.parent_id 
AND calls.assigned_user_id = users.id
AND accounts.id = meetings.parent_id
AND meetings.assigned_user_id = users.id
AND accounts.id = tasks.parent_id
AND tasks.assigned_user_id = users.id
GROUP BY name

Answer 1

如果没有具有代表性的数据进行测试，我的猜测是连接的 5 个表增加了行数，因此总数不正确。在 COUNT() 中使用 DISTINCT 可能会有所帮助，例如

SELECT
      COUNT(DISTINCT calls.id) 
    + COUNT(DISTINCT meetings.id) 
    + COUNT(DISTINCT tasks.id) AS 'total'
    , users.user_name AS 'name'
    , GROUP_CONCAT(DISTINCT accounts.name) AS 'accounts'
FROM accounts
      INNER JOIN calls ON accounts.id = calls.parent_id
      INNER JOIN users ON calls.assigned_user_id = users.id
      INNER JOIN meetings ON accounts.id = meetings.parent_id
                  AND meetings.assigned_user_id = users.id
      INNER JOIN tasks ON accounts.id = tasks.parent_id
                  AND tasks.assigned_user_id = users.id
GROUP BY
      users.user_name
;

请注意，我已经将通过 where 子句加入的旧方法换成了更现代的方法，您真的应该加入。

另一种可能性是您的计数不正确，因为您使用的是 INNER JOINS，它要求两个表中都存在数据才能返回行。所以也许您需要一些 LEFT OUTER JOIN。

SELECT
      COUNT(DISTINCT calls.id) 
    + COUNT(DISTINCT meetings.id) 
    + COUNT(DISTINCT tasks.id) AS 'total'
    , users.user_name AS 'name'
    , GROUP_CONCAT(DISTINCT accounts.name) AS 'accounts'
FROM accounts
      LEFT OUTER JOIN calls ON accounts.id = calls.parent_id
      LEFT OUTER JOIN users ON calls.assigned_user_id = users.id
      LEFT OUTER JOIN meetings ON accounts.id = meetings.parent_id
                  AND meetings.assigned_user_id = users.id
      LEFT OUTER JOIN tasks ON accounts.id = tasks.parent_id
                  AND tasks.assigned_user_id = users.id
GROUP BY
      users.user_name
;

最终的查询可能是连接的混合，一些 INNER 和一些 LEFT。