MySQL为表中的每个人选择前X条记录 [英] MySQL select top X records for each individual in table

问题描述

是否有更好的方法从MySQL表中获取多个前X个"结果?当不同的 foo 的数量很少时，我可以通过联合轻松完成此操作:

Is there a better way to get multiple "top X" results from a MySQL table? I'm able to accomplish this easily with a union when the number of different foo is small:

(SELECT foo,score FROM tablebar WHERE (foo = 'abc') ORDER BY score DESC LIMIT 10) 
UNION 
(SELECT foo,score FROM tablebar WHERE (foo = 'def') ORDER BY score DESC LIMIT 10)

我显然可以继续为foo的每个值添加联合.但是，当foo有500多个不同的值并且我需要每个值的前X个时，这是不切实际的.

I could obviously keep adding unions for each value of foo. However, this isn't practical when there are 500+ different values for foo and I need the top X of each.

推荐答案

可以以最伟大的每组"的含义来重新定义这种查询，在这种情况下，您希望每个组"的前10个得分为值的"foo".

This sort of query can be rephrased in a "greatest-n-per-group" sense, where you want the top 10 scores per "group" being values of 'foo'.

我建议您看看

I suggest you have a look at this link that deals with this question marvellously, starting off with a way that makes sense to perform your query and gradually optimising it.

set @num := 0, @foo := '';
select foo, score
from (
   select foo, score,
      @num := if(@foo = foo, @num + 1, 1) as row_number,
      @foo := foo as dummy
  from tablebar
  where foo IN ('abc','def')
  order by foo, score DESC     
) as x where x.row_number <= 10;

如果您想在所有级的foo上执行此操作(即假设执行GROUP BY foo)，则可以省略where foo in ...行.

If you wanted to perform this across all levels of foo (i.e. imagine doing a GROUP BY foo), you can omit the where foo in ... line.

基本上，内部查询(SELECT foo, score FROM tablebar WHERE foo IN ('abc','def') ORDER BY foo, score DESC)从表中获取foo和score，首先按foo排序，然后按降序排序.

Basically the inner query (SELECT foo, score FROM tablebar WHERE foo IN ('abc','def') ORDER BY foo, score DESC) grabs foo and score from the table, ordering first by foo and then score descending.

@num := ...仅增加每一行，对于foo的每个新值重置为1.也就是说，@num只是行号/行数(尝试自行运行内部查询以了解我的意思).

The @num := ... just increases every row, resetting to 1 for each new value of foo. That is, @num is just a row number/rank (try running the inner query on its own to see what I mean).

外部查询然后选择行/行数小于或等于10的行.

The outer query then selects rows where the rank/row number is less than or equal to 10.

注意:

您使用UNION进行的原始查询会删除重复项，因此，如果foo='abc'的前10个分数全为100，则仅返回一行(因为(foo,score)对被复制了10次).这将返回重复项.

Your original query with UNION removes duplicates, so if the top 10 scores for foo='abc' are all 100 then only one row will be returned (since the (foo,score) pair is replicated 10 times). This one will return duplicates.

这篇关于MySQL为表中的每个人选择前X条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！