我有两个表：
用户组
id name created_user
1 gn1 3
2 gn2 3
user_group_has_users
group_id user_id
2 5
2 6
我想计算每个组的用户数，并输出类似于以下内容：
id name user_count
2 gn2 2
1 gn1 0
我尝试了这个查询
select g.*,
count(cg.user_id) as user_count
from user_groups as g
left join user_group_has_users as cg on cg.user_group_id = g.id
where g.created_user = 3
但它只返回一行。我不明白为什么会发生这种事。请帮我一下。
使用此查询
SELECT g.* ,COUNT(cg.user_id) AS user_count
FROM user_groups g
LEFT JOIN user_group_has_users cg ON cg.group_id = g.id
WHERE g.created_user = 3
GROUP BY g.id
ORDER BY user_count DESC ;
左联接，where条件只是内部联接。你可能想要成为
select
ug.id,
ug.name,
count(ughu.user_id) as user_count
from user_groups ug
left join user_groups_has_users ughu on ughu.group_id = ug.id
and ug.created_user = 3
group by ug.id
order by user_count desc ;
如果您仍然希望使用created_user = 3筛选数据，则使用内部联接并将条件移动到where clause
小心使用聚合和JOIN。在某些情况下，数字被夸大了。
看看这是否适合你：
select g.*,
( SELECT count(*)
FROM user_group_has_users
WHERE user_group_id = g.id
) AS user_count
from user_groups as g
where g.created_user = 3