mysql (hive)获取分组后,求最大值

获取分组后取某字段最大一条记录
方法一:(效率最高) hive不支持
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

展开阅读全文

没有更多推荐了,返回首页