oracle强大函数,oracle 函数排行

oracle 函数排名

SQL> SELECT empno,ename,sal,rank() over(ORDER BY nvl(sal,0) DESC) 排序 from emp;

EMPNO ENAME SAL 排序

----- ---------- --------- ----------

1 zhulei 10000.00 1

2 zhulei%l 10000.00 1

7839 KING 5000.00 3

7788 SCOTT 3000.00 4

7902 FORD 3000.00 4

7566 JONES 2975.00 6

7698 BLAKE 2850.00 7

7782 CLARK 2450.00 8

7499 ALLEN 1600.00 9

7844 TURNER 1500.00 10

7934 MILLER 1300.00 11

7521 WARD 1250.00 12

7654 MARTIN 1250.00 12

7876 ADAMS 1100.00 14

7900 JAMES 950.00 15

7369 SMITH 800.00 16

16 rows selected

SQL> SELECT empno,ename,sal,dense_rank() over(ORDER BY nvl(sal,0) DESC) 排序 from emp;

EMPNO ENAME SAL 排序

----- ---------- --------- ----------

1 zhulei 10000.00 1

2 zhulei%l 10000.00 1

7839 KING 5000.00 2

7788 SCOTT 3000.00 3

7902 FORD 3000.00 3

7566 JONES 2975.00 4

7698 BLAKE 2850.00 5

7782 CLARK 2450.00 6

7499 ALLEN 1600.00 7

7844 TURNER 1500.00 8

7934 MILLER 1300.00 9

7521 WARD 1250.00 10

7654 MARTIN 1250.00 10

7876 ADAMS 1100.00 11

7900 JAMES 950.00 12

7369 SMITH 800.00 13

16 rows selected