MySQL:选择前n个最大值? [英] MySQL: Select top n max values?

问题描述

我真的很困惑查询，该查询需要返回在特定列上具有最大值的前N行.

I am really confused about the query that needing to return top N rows having biggest values on particular column.

例如，如果行N-1, N, N + 1具有相同的值.我必须返回just top N或top N + 1行.

For example, if the rows N-1, N, N + 1 have same values. Must I return just top N or top N + 1 rows.

推荐答案

如果您这样做:

select *
from t
order by value desc
limit N

您将获得前N行.

如果您这样做:

select *
from t join
     (select min(value) as cutoff
      from (select value
            from t
            order by value
            limit N
           ) tlim
    ) tlim
    on t.value >= tlim;

或者您可以这样简单地表达一下:

Or you could phrase this a bit more simply as:

select *
from t join
     (select value
      from t
      order by value
      limit N
    ) tlim
    on t.value = tlim.value;

从概念上讲，以下是您想要做的，但在MySQL中可能不起作用:

The following is conceptually what you want to do, but it might not work in MySQL:

select *
from t
where t.value >= ANY (select value from t order by value limit N)

这篇关于MySQL:选择前n个最大值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！