eg:返回排名前三的用户
select no,
sal,
row_number() over(partition by mo order by sal desc) as row_number,
rank() over(partition by mo order by sal desc) as rank,
dense_rank() over(partition by mo order by sal desc) as dense_rank
from tabname
where cid=(1,2)
order by 1,2 desc;
返回的值:
sal row_number rank dense_rank
3000 1 1 1
3000 2 1 1
2000 3 3 2
通过结果可以观察出返回值的差别
row_number会生成序号1,2,3
rank相同的sal会产生相同的序号,而后的会是3
dense_rank相同的sal产生相同的序号,其后递增
这里如果取第一的用户,row_number()会漏掉,如果去前二的用户,dense_rank很明显会返回三条记录;
故使用这三个函数的时候要看清需求