你这个是两个需求吧?第一个需求是 aid 的排序?但是aid重复的,你也看不出差距啊。 Select rownum, aid, num --这个rownum就是序号了 From ( Select aid, num From 表 Order By num ) 第二个也不算太不复杂: Select AVG(num) From ( Select num From 表 Order By num ) Where rownum <= 3
我这是一个需求,就是按照num排序的每个bid的前3条数据的num的平均值
表名:tb1 然后取平均数保留了2位小数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 < 4 group by num
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
失误最后group by num → group by aid
select aid,avg(num) from (select a.*, row_number()over(partition by a.aid order by a.num asc) rk from t a ) where rk<=3 group by aid
select aid,avg(num) from (select a.*, row_number()over(partition by a.aid order by a.num asc) rk from t a ) where rk<=3 group by aid
Select rownum, aid, num --这个rownum就是序号了
From (
Select aid, num
From 表
Order By num
)
第二个也不算太不复杂:
Select AVG(num)
From (
Select num
From 表
Order By num
)
Where rownum <= 3
from (
select aid ,num,
row_number() over(partition by aid order by num) rn
from tb1
)t1
where rn < 4
group by num
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
select aid,avg(num) from (select a.*, row_number()over(partition by a.aid order by a.num asc) rk from t a ) where rk<=3 group by aid
select aid,avg(num) from (select a.*, row_number()over(partition by a.aid order by a.num asc) rk from t a ) where rk<=3 group by aid