mysql - 如何从右侧表中加入每组的行数

Question

我有两个表 i) team ii) team_member。 team_member 表包含每个团队下的所有成员。

团队表看起来像这样:

+-----------------------+|  team                 ||-----------------------||  id | name   | status ||-----------------------||  1  | Team 1 | Active ||-----------------------||  2  | Team 2 | Active ||-----------------------||  3  | Team 3 | Active ||-----------------------||  4  | Team 4 |Inactive||-----------------------||  5  | Team 5 | Active |+-----------------------+

Team Member Table look like this :

+--------------------------------------+|  team_member                         ||--------------------------------------||tm_id| team_id   | user_id | status   ||--------------------------------------||  1  |     1     |    2    | Inactive ||--------------------------------------||  2  |     1     |    2    | Active   ||--------------------------------------||  3  |     1     |    3    | Active   ||--------------------------------------||  4  |     2     |    4    | Active   ||--------------------------------------||  5  |     2     |    5    | Inactive |+--------------------------------------+

What result I'm expecting is this :

+------------------+|  expected result ||------------------||  id |    count   ||------------------||  1  |      2     ||------------------||  2  |      1     ||------------------||  3  |      0     ||------------------||  5  |      0     |+------------------+

It is basically number of all active member under each group of the team.

My SQL query is this :

SELECT team.id, COUNT( team_member.tm_id ) 
FROM  `team` 
LEFT JOIN team_member ON team_member.team_id = team.id
AND team_member.status =  'Active'
GROUP BY team_member.team_id

现在出现的问题是 SQL 查询部分工作。如果每个团队至少有一名成员，则查询工作正常。如果它找到一个没有成员的组，那么它会将其添加到结果中，但之后停止。

如果 team_member 表为空，我得到的结果是:

+------------------+|  expected result ||------------------||  id |    count   ||------------------||  1  |      0     |+------------------+

任何人都可以在查询错误的地方停下来吗？应该是正确的查询。

谢谢。

Answer 1

按 team.id 而不是 team_member.team_id 对数据进行分组，因为 team_member 表不包含所有团队 ID。

试试这个:

SELECT team.id, COUNT( team_member.tm_id ) 
FROM  `team` 
LEFT JOIN team_member ON team_member.team_id = team.id
AND team_member.status =  'Active'
GROUP BY team.id;

SQL Fiddle 示例:http://sqlfiddle.com/#!9/65a397/2