select * from group_avg_test;

在这里插入图片描述

select groupId,avg(value) from (
	select groupId,value from (
		select groupId, value,@num := if(@currGroupId = groupId, @num + 1, 1) as row_number,
		@currGroupId := groupId from(select @num := 0, @currGroupId := '') as i,
		group_avg_test order by groupId, value desc
	) as r where r.row_number <= 2
) as t group by groupId;

在这里插入图片描述

这样就求出了每个组前2个最大的value的平均值

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐