Mysql左连接，计数唯一值在一个字段不工作 - Mysql left-join, count by unique value in a field not working

Mysql left-join, count by unique value in a field not working

我有一个像下面这样的表格。

我希望能够获得group_id的计数，然后由group_id组，然后左加入组表，其中groups_user.group_id = groups.id，但我只得到一个结果。

我想按唯一的group_id分组，然后计数每个重复的group_id。到目前为止，我的查询如下:

SELECT 'groups.group' ,COUNT('groups_users.group_id') as groups_count
FROM `groups_users`
LEFT JOIN groups
ON 'groups_users.group_id' = 'groups.id'
GROUP BY 'groups_users.group_id'

表:

id  group_id    user_id
26  3           1
22  2           1
19  1           1
20  1           2
21  1           4

我错在哪里?

我想你把JOIN弄反了。试一试:

SELECT g.group, COUNT(gu.group_id) AS groups_count
    FROM groups g
        LEFT JOIN groups_users gu
             ON g.id = gu.group_id
    GROUP BY g.group;