--收入排名
--解析函数 规律 解析函数() over(order by 字段)
--在字段排序以后,再进行某种解析计算
--间断排名 rank()
SELECT empno,ename,sal,rank() over(ORDER BY nvl(sal,0) DESC) x
FROM emp
EMPNO | ENAME | SAL | X |
7369 | SMITH | 8000 | 1 |
7839 | KING | 8000 | 1 |
7782 | CLARK | 7450 | 3 |
7788 | SCOTT | 7000 | 4 |
7698 | BLAKE | 6850 | 5 |
7902 | FORD | 6000 | 6 |
7566 | JONES | 5975 | 7 |
7499 | ALLEN | 5600 | 8 |
7844 | TURNER | 5500 | 9 |
7521 | WARD | 5250 | 10 |
7654 | MARTIN | 5250 | 10 |
7876 | ADAMS | 4100 | 12 |
9000 | xiaoming |
| 13 |
--不间断排名 dense_rank()
SELECT empno,ename,sal,dense_rank() over(ORDER BY nvl(sal,0) DESC) x
FROM emp
EMPNO | ENAME | SAL | X |
7369 | SMITH | 8000 | 1 |
7839 | KING | 8000 | 1 |
7782 | CLARK | 7450 | 2 |
7788 | SCOTT | 7000 | 3 |
7698 | BLAKE | 6850 | 4 |
7902 | FORD | 6000 | 5 |
7566 | JONES | 5975 | 6 |
7499 | ALLEN | 5600 | 7 |
7844 | TURNER | 5500 | 8 |
7521 | WARD | 5250 | 9 |
7654 | MARTIN | 5250 | 9 |
7876 | ADAMS | 4100 | 10 |
9000 | xiaoming |
| 11 |