MySql按字段分组取最大值记录

数据库原始数据如下：数据库名：tbl_clothers

需求是：按照type分组，并获取个分组中price中的最大值，解决sql如下：

方法一：

select * 
from (select type, name, price from tbl_clothers order by price desc) as a

  group by a.type;

方法二：

select a.* from tbl_clothers as a 
where price = (select max(price) from tbl_clothers where a.type=type)

方法三：

select a.* from tbl_clothers as a 
where not exists (select * from tbl_clothers where type=a.type and price>a.price)
# not exists意思是：在tbl_clothers中找不到比a的价格更大的值，也就是a的值应该是最大的价格。

方法四：

select a.* from tbl_clothers as a 
where exists (select count(*) from tbl_clothers where type=a.type and price>a.price having count(*)=0)

方法五：

select a.* from tbl_clothers a 
inner join (
select type,max(price) maxprice 
from tbl_clothers group by type) b on a.type=b.type and a.price=b.maxprice 
#order by a.type; 

 方法六：这个是比较直观的

SELECT MAX(update_time) AS update_time, fid
FROM new
GROUP BY fid
ORDER BY update_time DESC

SELECT *
FROM new
INNER JOIN user ON user.id = new.user_id
WHERE fid = ? AND update_time = ?

建立索引ALERT TABLE ADD INDEX update_fid (fid, updatetime)

 

有些语法可能会由于数据裤版本不同，会有差别。