mysql选择top n max值

How can you select the top n max values from a table?

如何从表中选择最大n值?

For a table like this:

对于这样的桌子:

column1  column2
   1       foo
   2       foo
   3       foo
   4       foo
   5       bar
   6       bar
   7       bar
   8       bar

For n=2, the result needs to be:

对于n=2，结果为:

The approach below selects only the max value for each group.

下面的方法只选择每个组的最大值。

SELECT max(column1) FROM table GROUP BY column2

Returns:

4
8

4 个解决方案

#1

For n=2 you could

n = 2

SELECT max(column1) m 
FROM table t
GROUP BY column2
UNION
SELECT max(column1) m
FROM table t
WHERE column1 NOT IN (SELECT max(column1) 
                      WHERE column2 = t.column2)

for any n you could use approaches described here to simulate rank over partition.

对于任何n，都可以使用这里描述的方法来模拟等级除以分区。

EDIT: Actually this article will give you exactly what you need.

编辑:事实上，这篇文章将提供你所需要的。

Basically it is something like this

基本上就是这样

SELECT t.*
FROM
   (SELECT grouper,
          (SELECT val 
           FROM table li
           WHERE li.grouper = dlo.grouper
           ORDER BY
                 li.grouper, li.val DESC
           LIMIT 2,1) AS mid
   FROM 
      (
      SELECT DISTINCT grouper
      FROM table
      ) dlo 
   ) lo, table t
WHERE t.grouper = lo.grouper
      AND t.val > lo.mid

Replace grouper with the name of the column you want to group by and val with the name of the column that hold the values.

用您想要分组的列的名称替换grouper，并用包含值的列的名称替换val。

To work out how exactly it functions go step-by-step from the most inner query and run them.

要计算出它是如何从最内部的查询逐步执行并运行它们的。

Also, there is a slight simplification - the subquery that finds the mid can return NULL if certain category does not have enough values so there should be COALESCE of that to some constant that would make sense in the comparison (in your case it would be MIN of domain of the val, in article it is MAX).

同样,有一个轻微的简化——中期发现的子查询可以返回NULL如果某些类别没有足够的值应该有合并的一个常数比较有意义的(在你的情况中分域的val,MAX)条。

EDIT2: I forgot to mention that it is the LIMIT 2,1 that determines the n (LIMIT n,1).

我忘了说极限2 1决定了n(极限n 1)

#2

If you are using mySQl, why don't you use the LIMIT functionality? Sort the records in descending order and limit the top n i.e. :

如果您正在使用mySQl，为什么不使用极限功能呢?按降序对记录进行排序，并限制顶部n，即:

SELECT yourColumnName FROM yourTableName 
ORDER BY Id desc 
LIMIT 0,3

#3

This is how I'm getting the N max rows per group in MySQL

这是MySQL中每组N最大行的方式。

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country AND co.id < ci.id
) < 1
;

how it works:

它是如何工作的:

self join to the table
self加入到表格中
groups are done by co.country = ci.country
组由co.country = ci.country执行
N elements per group are controlled by ) < 1 so for 3 elements - ) < 3
每组N个元素由)< 1控制，因此3个元素-)< 3
to get max or min depends on: co.id < ci.id
- co.id < ci.id - max
- co.id < ci。id -马克斯
- co.id > ci.id - min
- co.id > ci。id -分钟
获取最大值或最小值取决于:co id < ci。id co.id < ci。id - max co.id > ci。id -分钟

Full example here:

完整的例子:

mysql select n max values per group/

mysql选择每个组的n最大值

mysql select max and return multiple values

mysql选择max并返回多个值

Note: Have in mind that additional constraints like gender = 0 should be done in both places. So if you want to get males only, then you should apply constraint on the inner and the outer select

注意:请记住，像gender = 0这样的附加约束应该在这两个地方执行。所以如果你想只得到男性，那么你应该对内部和外部选择施加约束

#4

Starting from MySQL 8.0/MariaDB support window functions which are designed for this kind of operations:

从MySQL 8.0/MariaDB支持窗口函数开始，为此类操作设计:

SELECT *
FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY column2 ORDER BY column1 DESC) AS r
FROM tab) s
WHERE r <= 2
ORDER BY column2 DESC, r DESC;

DB-Fiddle.com Demo

DB-Fiddle.com演示

#1