MySQL选择具有相同条件值的顶行

I don't know how to title this problem. Correct me if you have better words.

我不知道如何标题这个问题。如果你有更好的话,请纠正我。

I have two tables, Users and Posts.

我有两个表,用户和帖子。

Users:

id | username | password | ...

Posts:

id | author_id | title | content | ...

Now I want to list the "most active" users - the users who have written the most posts. And specifically, I want the top 10 result.

现在我想列出“最活跃”的用户 - 撰写帖子最多的用户。具体来说,我想要前十名的结果。

SELECT u.username, COUNT(p.id) AS count 
FROM Posts p, Users u
WHERE u.id=p.author_id
GROUP BY p.author_id 
ORDER BY count DESC
LIMIT 10;

I can get the expected result. However, the ranking may not be "fair" if some users have same number of posts.

我可以得到预期的结果。但是,如果某些用户具有相同数量的帖子,则排名可能不是“公平的”。

E.g., I may get results like:

例如,我可能得到如下结果:

User 1  | 14
User 2  | 13
...
User 9  | 4
User 10 | 4

Here, there are actually several more users who have 4 posts.

实际上,有几个用户有4个帖子。

So, the top 10 could be not exactly 10 results. How can I get a more "fair" result that contains extra rows of users who have 4 posts?

因此,前10名可能不是10个结果。如何获得更“公平”的结果,其中包含有4个帖子的额外用户行?

3 个解决方案

#1

This is the right solution, I think: you need the subquery to know how much post has the 10th place in your top ten. Then, you use the outer query to extract the users with almost that postcount.

我认为这是正确的解决方案:您需要子查询才能知道在前十名中有多少帖子排在第10位。然后,使用外部查询来提取几乎该postcount的用户。

SELECT u.username, COUNT(p.id) AS count 
FROM Posts p
JOIN Users u ON u.id = p.author_id
GROUP BY p.author_id 
HAVING COUNT(p.id) >= 
(
    SELECT COUNT(p.id) AS count 
    FROM Posts p
    JOIN Users u ON u.id = p.author_id
    GROUP BY p.author_id 
    ORDER BY count DESC
    LIMIT 9, 1
)
ORDER BY count DESC

#2

Maybe not the best solution

也许不是最好的解决方案

select u.username, COUNT(p.id) AS count 
FROM Posts p
join Users u on u.id = p.author_id
GROUP BY p.author_id 
having COUNT(p.id) in 
(
    SELECT COUNT(p.id)
    FROM Posts p
    join Users u on u.id = p.author_id
    GROUP BY p.author_id 
    ORDER BY count DESC
    LIMIT 10    
)
ORDER BY count DESC

#3

Try this:

SELECT username, PostCount
FROM (SELECT username, PostCount, IF(@PostCount = @PostCount:=PostCount, @idx:=@idx+1, @Idx:=1) AS idx
      FROM (SELECT u.username, COUNT(p.id) AS PostCount 
            FROM Posts p
            INNER JOIN Users u ON u.id=p.author_id
            GROUP BY p.author_id 
           ) AS A, (SELECT @PostCount:=0, @Idx:=1) AS B
      ORDER BY PostCount DESC
     ) AS A
WHERE idx <= 10;

#1