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

本文介绍了MySQL选择具有相同条件值的顶部行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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