rank和rownumber都是自动生成序号,后面都可以跟partition by分组和order by 排序。
不同之处在于,rownumber在order by后面的字段,排序字段数值相等时,rownumber字段依次递增。
rank在order by后面的字段,排序字段数值相等时,rownumber都相同,直接跳到下一个不同的序号。
select rank() over(partition by A order by B ) num,A as "分组",B as "排序" from TABLE;
-------------------------
select rownumber() over(partition byA order byB) num,A as "分组",B as "排序" from TABLE;