我有一个包含每个用户信息的表users
。然后是包含文章信息的表posts
,最后是包含以下列的表user_posts
:
user_id
post_id
...
我正试图获得帖子数最高的用户图表。我提出了这个问题:
选择u.id作为uid,选择u.name作为uname,count(up.id)为up_countFROM users as u JOIN user_posts as up ON up.user_id=u.id ORDER BY vcount DESC LIMIT 25
此查询只返回一个用户和表user_posts
中所有行的总数。
我做错了什么?我需要得到25个用户的列表,按每个用户发布的文章数排序。
提前感谢
您的查询需要有GROUP BY
子句,因为您使用了COUNT()
函数。
SELECT u.id as uid,
u.name as uname,
count(up.id) as up_count
FROM users as u
LEFT JOIN user_posts as up
ON up.user_id = u.id
GROUP BY u.id, u.name
ORDER BY up_count DESC LIMIT 25
您必须按ID
对它们进行分组,否则您将为所有记录单独计算总数结果。还有一件事,使用LEFT JOIN
,这样即使没有帖子的用户仍然可以在你的结果中看到0
的分数。
SELECT
u.id as uid, u.name as uname, count(up.id) as up_count
FROM users as u
JOIN user_posts as up ON up.user_id = u.id
GROUP BY
u.id, u.name
ORDER BY
vcount
DESC LIMIT 25