select col1,sum(decode(rk,1,col2,0)),sum(decode(rk,2,col2,0)),sum(decode(rk,3,col2,0)) from( ( select col1,col2,rank() over(partition by col1 order by col2) rk from tb) where rk<4)
select col1,MAX(decode(rk,1,col2,0)),MAX(decode(rk,2,col2,0)),MAX(decode(rk,3,col2,0)) from( ( select col1,col2,rank() over(partition by col1 order by col2) rk from tb) where rk<4) GROUP BY w.col1 这样可以解决,但不知是否有更好的方法?
DENSE_RANK() over(partition by col1 order by col2) dk 你的方法已够用了
from(
(
select col1,col2,rank() over(partition by col1 order by col2) rk
from tb)
where rk<4)
GROUP BY w.col1
这样可以解决,但不知是否有更好的方法?
你的方法已够用了