我有一个用户表和一个评论表,我想从评论表中选择拥有大量评论的顶级用户,并按评论数量排序
表结构
用户
id | username | password
评论
id | text | author_username
使用以下MySQL语句列出评论最多的用户。CommentCount
告诉您特定用户的评论数量。
SELECT
users.username,
COUNT(comments.id) AS CommentCount
FROM
users
INNER JOIN comments ON users.id = comments.author_userid
GROUP BY
users.username
ORDER BY
COUNT(comments.id) DESC
请注意,您必须先将author_userid
更改为author_username
!
我的SQL有点生疏,但像这样的东西应该会给你想要的东西(尽管正如我所提到的,用户ID应该是comments
表中唯一的用户标识符。(
Select count(id), author_username from comments group by author_username
select u.username,count(c.comments) as total
from users as u
left join comments as c
on u.username = c.author_username
group by u.username
order by total desc
我会按照dutchie432的建议更改加入字段。添加一个限制条款,以获得所需的记录数量。