本文共 1574 字,大约阅读时间需要 5 分钟。
row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序)rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)dense_rank()l是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 基于[deptno]进行不具有重复序列的连续排序: SELECT deptno,empno,ROW_NUMBER() OVER(ORDER BY deptno) FROM emp; SELECT deptno,empno,RANK() OVER(ORDER BY deptno) FROM emp;基于[deptno]进行具有重复序列的连续排序:SELECT deptno,empno,DENSE_RANK() OVER(ORDER BY deptno) FROM emp; 先以[deptno]分组后,再基于[deptno]进行跳跃排序: SELECT deptno,empno,RANK() OVER(PARTITION BY deptno ORDER BY deptno) FROM emp; 先以[deptno]分组后,再基于[deptno]进行不具有重复序列的连续排序: SELECT deptno,empno,ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY deptno) FROM emp; 先以[deptno]分组后,再基于[empno]进行跳跃排序: SELECT deptno,empno,RANK() OVER(PARTITION BY deptno ORDER BY empno) FROM emp; SQL> SELECT * FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7499 ALLEN SALESMAN 7698 1981/02/20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981/02/22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981/04/02 2975.00 20 7654 MARTIN SALESMAN 7698 1981/09/28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981/05/01 2850.00 30 7782 CLARK MANAGER 7839 1981/06/09 2450.00 10 7788 SCOTT ANALYST 7566 1982/12/09 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7844 TURNER SALESMAN 7698 1981/09/08 1500.00 0.00 30 7876 ADAMS CLERK 7788 1983/01/12 1100.00 20 7900 JAMES CLERK 7698 1981/12/03 950.00 30 7902 FORD ANALYST 7566 1981/12/03 3000.00 20 7934 MILLER CLERK 7782 1982/01/23 1300.00 10 14 rows selected SQL> 转载地址:http://mktai.baihongyu.com/