MySQL选择具有相同条件值的顶部行 [英] MySQL select top rows with same condition values

问题描述

我不知道该如何称呼这个问题.如果你有更好的话请纠正我.

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

我有两个表，Users和Posts.

I have two tables, Users and Posts.

用户:

id | username | password | ...

帖子:

id | author_id | title | content | ...

现在，我想列出最活跃"的用户-写得最多的用户.具体来说，我想要的是top 10结果.

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

在这里，实际上有几个用户拥有4个帖子.

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

因此，top 10可能不完全是10结果.我如何获得更多的"公平"结果，其中包含具有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?

推荐答案

我认为这是正确的解决方案:您需要子查询才能知道在前十名中排名第十的职位有多少.然后，您可以使用外部查询来提取具有几乎该后计数的用户.

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.

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

这篇关于MySQL选择具有相同条件值的顶部行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！