FInd MySQL表中不同行的两个最高值

Question

我有一个MySQL表，sales，内容如下：

+----+-------+--------+
| id | name  | amount |
+----+-------+--------+
|  1 | Alice |     12 |
|  2 | Bob   |     10 |
|  3 | Alice |     14 |
|  4 | Bob   |      2 |
|  5 | Alice |      6 |
|  6 | Bob   |     23 |
|  7 | Bob   |      5 |
|  8 | Alice |     12 |
+----+-------+--------+

对于每个名称，我想找到数量最多的两行。换句话说，我想要一个返回以下结果的查询：

+----+-------+--------+
| id | name  | amount |
+----+-------+--------+
|  3 | Alice |     14 |
|  1 | Alice |     12 |
|  6 | Bob   |     23 |
|  2 | Bob   |     10 |
+----+-------+--------+

(结果中只应包括第1行和第8行中的一行。不管是哪一行。(

我该怎么做？

编辑

表中增加了第8行，以澄清领带的处理方式。

Answer 1

如果您运行的是MySQL 8.0，只需使用窗口函数：

select *
from (
select s.*,
rank() over(partition by name order by amount desc) rn
from sales s
) s
where rn <= 2
order by name, amount desc

这允许前2个平局(如果有的话(。

在早期版本中，有一个选项使用子查询进行筛选：

select *
from sales s
where (select count(*) from sales s1 where s1.name = s.name and s1.amount > s.amount) < 2
order by name, amount desc

Answer 2

旧版本MySQL中的一个简单方法是：

select t.*
from t
where t.amount >= (select t2.amount
from t t2
where t2.name = t.name 
order by t2.amount desc
limit 1 offset 1
);

对于(name, amount)上的索引，这可能是最快的方法。

Answer 3

如果您使用的是现代MySQL，row_number应该是您想要的。

select
row_number() over (partition by [name] order by [amount] desc]) [rank], 
id,
name, 
amount

这增加了必要的"；等级；列，所以您只需从SELECT中选择作为表源：

select id, name, amount
from ( -- the above query 
select
row_number() over (partition by [name] order by [amount] desc]) [rank], 
id,
name, 
amount
) x
where x.[rank] <= 2
order by [name], [amount] desc

这是吹捧。