mysql - 在 SQL 中选择集合中的顶部项目

Question

我有一个表，其中包含对象的位置、名称和价格。例如

Location | Name | Price
Store 1    Apple   $.50
Store 1    Pear    $.75
Store 2    Peach   $.75
Store 3    Mango   $1.50
Store 3    Melon   $2.00

我想要返回的是

Location | Name | Price
Store 1    Apple   $.50
Store 2    Peach   $.75
Store 3    Mango   $1.50

我该怎么做？

Answer 1

在row_subquery中使用group by:

select location,name,price
from table_name
where (location,price) in
( select t.location,min(t.price)
  from table_name t
  group by t.location
)

您还可以使用自加入

select t1.location,t1.name,t1.price
from table_name t1 
join
( select location,min(price) as price
  from table_name 
  group by location
) t2 on t1.location=t2.location and t1.price=t2.price