求一个oracol sql写法
我现在有张表,大概的数据如下
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