MySQL查找每个用户的帖子总数 [英] MySQL Find the total amount of posts per user
本文介绍了MySQL查找每个用户的帖子总数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含以下两个表的数据库, USERS,POSTS 我正在寻找一种方法来获取用户拥有多少帖子的计数.
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
+----+------+ +----+---------+-----------+
| ID | Name | | ID | user_id | Name |
+----+------+ +----+---------+-----------+
| 1 | Bob | | 1 | 1 | Blargg... |
+----+------+ +----+---------+-----------+
| 2 | Jim | | 2 | 1 | Blargg... |
+----+------+ +----+---------+-----------+
| 3 | Jo | | 3 | 2 | Blargg... |
+----+------+ +----+---------+-----------+
我尝试了以下SQL命令的许多变体,但均未成功.与其显示单个用户的帖子数,不如显示一行包含所有帖子的数.
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.
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
这篇关于MySQL查找每个用户的帖子总数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文