Mysql分组求平均值

select * from group_avg_test;±--------±------+| groupId | value |±--------±------+|1 |1 ||1 |2 ||2 |3 ||5 |3 ||5 |6 ||5 |6 ||3 |2 |±--------±------+select group

xiaohailalala

7879人浏览 · 2021-03-08 17:44:42

xiaohailalala · 2021-03-08 17:44:42 发布

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;