MySQL选择前n个最大值

Question

如何从表中选择出前n个最大值？

对于这样的一个表：

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

当n=2时，结果应该为：

3    
4    
7    
8    

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

SELECT max(column1) FROM table GROUP BY column2

返回：

4
8

- Gabriel Diaconescu

---

你需要一个n=2的解决方案还是一个任意n的通用解决方案？ - Unreason

我实际上更新了解决方案 - 如果您想进一步了解它的工作原理（并且您无法从文章中弄清楚），请告诉我。 - Unreason

@Unreason，非常感谢您提供的解决方案，它非常有效，解释也非常清晰。 - Gabriel Diaconescu

所有的功劳都应归功于Quassnoi的博客。如果你要对大型数据集运行查询，你应该详细了解性能方面的细节（MySQL有时需要一些帮助，这就是其中之一）。 - Unreason

Answer 1

当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，您可以使用这里描述的方法来模拟分区内的排名。

编辑： 实际上，这篇文章将为您提供所需的内容。

基本上是这样的：

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替换为包含值的列名。

要确切地了解其功能，从最内部查询开始逐步运行它们。

此外，有一个小简化 - 找到mid的子查询如果某个类别没有足够的值，则可能返回NULL，因此应该将其COALESCE到某个常量，使其在比较中有意义（在您的情况下，它将是值域的MIN，在文章中它是MAX）。

编辑2：我忘了提及它是LIMIT 2,1决定n（LIMIT n,1）。

- Unreason

---

这个答案能够挽救生命。 - Georgy Ivanov

Answer 2

如果你正在使用MySQL，为什么不使用LIMIT功能呢？按降序排序记录并限制前n个，即：

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

- Danyal Sandeelo

---

好问题，这也带来了一个问题。如果我只想检索一个最大值，但是可能有多个最大值怎么办？我尝试过类似于select max(x) where max(x) = (select --top values--)的语句，但不确定如何使其正常工作。 - Mohammed Joraid

Answer 3

从 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演示

- Lukasz Szozda

Answer 4

这是我在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
;

工作原理：

• 自连接到表格
• 分组由co.country = ci.country完成
• 每个组的N个元素由) < 1控制，因此对于3个元素-）< 3
• 获取最大或最小取决于：co.id < ci.id
  • co.id < ci.id - 最大值
  • co.id > ci.id - 最小值

完整示例在此处：

mysql select n max values per group/

mysql select max and return multiple values

注意：请记住，在两个地方都应该执行附加约束条件，例如gender = 0。因此，如果您只想获取男性，则应在内部和外部选择上应用约束条件

- DataScientYst