mysql - 选择多个最大值

Question

我在 mysql 数据库中有一个这样的表:

id | item
-----------
1  | 2
2  | 2
3  | 4
4  | 5
5  | 8
6  | 8
7  | 8

我希望结果是具有最高 Item 值的 3 条记录

select max(item) 只返回 1 个值我如何选择多个最大值？谢谢

Answer 1

您可以使用派生表来获取最大值并将其连接回到原始表以查看与其对应的所有行。

select t.id, t.item 
from tablename t
join (select max(item) as mxitem from tablename) x
on x.mxitem = t.item

编辑:

select t.co_travelers_id, t.booking_id, t.accounts_id 
from a_co_travelers t
join (select accounts_id, max(booking_id) as mxitem 
      from a_co_travelers
      group by accounts_id) x 
on x.mxitem = t.booking_id and t.accounts_id = x.accounts_id