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

I have a Database with the following two tables, USERS, POSTS I am looking for a way to get the count of how many posts a user has.

我有一个包含以下两个表的数据库，USERS，POSTS我正在寻找一种方法来计算用户拥有的帖子数量。

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

I have tried many variations of the following SQL command with out any success. instead of showing the count of posts for a single user it shows a single row with all the posts as the count.

我已经尝试了以下SQL命令的许多变体，但没有任何成功。而不是显示单个用户的帖子数，它显示一行，所有帖子都作为计数。

SELECT users.* , COUNT( Posts.user_id ) 
FROM users
LEFT JOIN Posts ON users.id = Posts.user_id

In the end I want something like this

最后我想要这样的东西

+----+------+ 
| ID | Count|
+----+------+
| 1  |  2   |  
+----+------+
| 2  |  1   |  
+----+------+

2 个解决方案

#1

Figured it out. Smacks self in head

弄清楚了。砸自己的头脑

SELECT users.*, count( posts.user_id ) 
FROM posts LEFT JOIN users ON users.id=posts.user_id 
GROUP BY posts.user_id

#2

select users.*, count(posts.user_id)

from users, posts
where users.user_id = posts.user_id
group by posts.user_id

But the best way is too add a field to the users table and keep the amount of posts made by each users, and updated it whenever a post is created or deleted. Otherwise, you'll slow down your DB when it grows bigger.

但最好的方法是在users表中添加一个字段，并保留每个用户发布的帖子数量，并在创建或删除帖子时更新它。否则，当数据库变大时，你会放慢速度。