MySQL - 按count（）和GROUP BY排序 [英] MySQL - ranking by count() and GROUP BY

问题描述

我有我的mysql表 posts ，我的论坛的所有帖子都存储。它是这样的：

I've got my mysql table posts, where all posts of my forum are stored. It's like this:

id      uid      thread      post      title      text      time
(int)   (int)    (varchar)   (int)     (varchar)  (text)    (int)

现在我想显示排名的帖子）。我试过这样：

Now I want to show the rank (ranking of number of posts) on the user profiles. I've tried something like this:

set @rownum := 0;
SELECT @rownum := @rownum + 1 AS rank, uid, count(id)
FROM `posts` GROUP BY uid ORDER BY count(id)

但它返回的数据不正确。 uid和count（id）匹配，但是rank是错误的。
我的条目是：

But it returns not the right data. The uid and count(id) match, but the rank is wrong. My entry is like:

rank     uid     count(id)
  1        1        214

我是用户1，我有214个帖子，但不是排名1.
是其他条目如：

I'm user 1, and I've got 214 posts, but that's not rank 1. There are other entries like:

rank     uid     count(id)
  8       22        674

---

如何获得正确的排名？

---

How can I get the query to put out the right rank?

推荐答案

您需要根据用户ID对整个结果集进行分组，然后进行排序...然后应用排名

You need your entire result set grouped by user ID first and ordered... then apply the ranking

select
      @rownum := @rownum +1 as rank,
      prequery.uid,
      prequery.PostCount
   from
      ( select @rownum := 0 ) sqlvars,
      ( SELECT uid, count(id) postCount
           from posts
           group by uid
           order by count(id) desc ) prequery

要获得特定的人和尝试HAVING然后换行，然后应用一个where ...

To get for a specific person, and problem attempting the "HAVING" clause, I would then wrap it up and then apply a where...

select WrappedQuery.* 
   from ( entire query from above ) WrappedQuery
   where WrappedQuery.uid = SinglePerson

这篇关于MySQL - 按count（）和GROUP BY排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！