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
