mysql - 从连接查询中选择 2 个计数

Question

此查询始终返回与 num_of_users 相同的“num_of_accounts”但如果我用lists_user_assignment删除第二个连接num_of_accounts 正确

  select lists.*, count(lists_account_assignment.id) as num_of_accounts, count(lists_user_assignment.id) as num_of_users
from lists
left join lists_account_assignment on lists.id = lists_account_assignment.lists_id
left join lists_user_assignment on lists.id = lists_user_assignment.lists_id
where lists.tenant_id = 1
group by lists.id

不确定这是否是在查询中获取 2 个计数的正确方法。另外，这个查询是否昂贵。希望得到帮助。

Answer 1

尝试使用 COUNT(DISTINCT ..):-

SELECT lists.*, 
       Count(DISTINCT lists_account_assignment.id) AS num_of_accounts, 
       Count(DISTINCT lists_user_assignment.id)    AS num_of_users 
FROM   lists 
       LEFT JOIN lists_account_assignment 
              ON lists.id = lists_account_assignment.lists_id 
       LEFT JOIN lists_user_assignment 
              ON lists.id = lists_user_assignment.lists_id 
WHERE  lists.tenant_id = 1 
GROUP  BY lists.id