我有一个包含以下两个表的数据库,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... |
+----+------+ +----+---------+-----------+
我尝试了以下sql命令的许多变体,但没有任何成功.而不是显示单个用户的帖子数,它显示一行,所有帖子都作为计数.
SELECT users.*,COUNT( Posts.user_id )
FROM users
LEFT JOIN Posts ON users.id = Posts.user_id
最后我想要这样的东西
+----+------+
| ID | Count|
+----+------+
| 1 | 2 |
+----+------+
| 2 | 1 |
+----+------+
最佳答案
弄清楚了.砸自己的头脑
SELECT users.*,count( posts.user_id )
FROM posts LEFT JOIN users ON users.id=posts.user_id
GROUP BY posts.user_id