mysql count排序_MySQL - 按count（）和GROUP BY排序

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)

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

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?

解决方案

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

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