mysql group by最大值_MySQL之——GROUP BY分组取字段最大值

方法一：(效率最高)

select * from test as a

where typeindex = (select max(b.typeindex)

from test as b

where a.type = b.type );

方法二：(效率次之)

select

a.* from test a,

(select type,max(typeindex) typeindex from test group by type) b

where a.type = b.type and a.typeindex = b.typeindex order by a.type

方法三：

select a.* from test a inner join (select type , max(typeindex) typeindex from test group by type) b on a.type = b.type and a.typeindex = b.typeindex order by a.type

方法四：(效率最低)

select * from

(

select *,ROW_NUMBER() OVER(PARTITION BY type ORDER BY typeindex DESC) as num

from test

) t

where t.num = 1