比如查看员工的工资排名。排名需要注意重复数据,对于重复数据有三种排名方式:
分别是: 顺序、同名跳号,同名不跳号
SELECT deptno,
empno,
sal,
row_number() over (PARTITION BY deptno ORDER BY sal DESC) AS row_number,
rank() over (PARTITION BY deptno ORDER BY sal DESC) AS rank,
dense_rank() over (PARTITION BY deptno ORDER BY sal DESC) AS dense_rank
FROM EMP /*EMP为员工表*/
WHERE deptno = 20;
运行结果如下:(重点关注加粗的几个数字)
DEPTNO EMPNO SAL ROW_NUMBER RANK DENSE_RANK
20 7788 3000 1 1 1
20 7902 3000 2 1 1
20 7566 2975 3 3 2
20 7876 1100 4 4 3
20 7369 800 5 5 4
实际查询时,确认好需求再定