MySQL QUERY 查找每个用户的帖子数

Question

我有一个包含以下两个表的数据库，USERS，POSTS 我正在寻找一种方法来计算用户拥有多少帖子(postCount)，如果用户没有任何帖子，postCount 将等于零

 Users            Posts
 +----+------+    +----+---------+-----------+
 | ID | Name |    | ID | user_id | Name      |
 +----+------+    +----+---------+-----------+
 | 1  | Bob  |    | 1  | 1       | Blargg... | 
 | 2  | Jim  |    | 2  | 1       | Blargg... | 
 | 3  | Jo   |    | 3  | 2       | Blargg... | 
 +----+------+    +----+---------+-----------+

最后我想要这样的东西:

 +----+------+ 
 | ID | Count|
 +----+------+
 | 1  |  2   |  
 | 2  |  1   |  
 | 3  |  0   |  
 +----+------+

Answer 1

试试这个:

SELECT u.ID, u.Name, COUNT(p.Name)
FROM Users AS u
LEFT JOIN Posts AS p ON u.ID = p.user_id
GROUP BY u.ID, u.Name