mysql选择前n个最大值

2021-11-20 00:00:00 mysql greatest-n-per-group

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

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

对于这样的表格:

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

对于 n=2，结果需要是:

For n=2, the result needs to be:

3    
4    
7    
8

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

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

SELECT max(column1) FROM table GROUP BY column2

返回:

4
8

推荐答案

对于 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)

对于任何 n，您都可以使用 此处所述的方法 模拟分区上的排名.

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

实际上，这篇文章将为您提供所需的一切.

Actually this article will give you exactly what you need.

基本上是这样的

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

将 grouper 替换为要分组的列的名称，将 val 替换为包含值的列的名称.

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.

从最内部的查询开始逐步确定它的功能并运行它们.

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

此外，还有一个轻微的简化 - 如果某些类别没有足够的值，则找到 mid 的子查询可以返回 NULL，因此应该将其与某个常量合并，以便在比较(在您的情况下，它将是 val 域的 MIN，在文章中它是 MAX).

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).

我忘了说，决定 n (LIMIT n,1) 的是 LIMIT 2,1.

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

相关文章