我现在有张表,大概的数据如下
aid num
1 10
1 20
1 30
1 40
1 50
2 30
2 50
要求是查找每个aid按照num的排序,前3个num的平均数
求大侠帮忙,谢谢阿
------解决方案--------------------
表名:tb1 然后取平均数保留了2位小数
- SQL code
select aid ,round(avg(num),2) from ( select aid ,num, row_number() over(partition by aid order by num) rn from tb1 )t1 where rn < 4group by num
------解决方案--------------------
- SQL code
select aid avg(num)from(select aid ,num ,row_number()over(partition by aid order by num asc) rn from tab )where rn<=3 group by aid