首页 > 代码库 > Oracle中的 row_number() over (partition by order by ) 用法

Oracle中的 row_number() over (partition by order by ) 用法

oracle 里面经常这样用 

select col1,col2..., row_number() over (partition by colx order by coly) from table_name;;

这句话的意思是把表中的数值按照colx 分组,每一组内部按照coly排序,同时 row_number()返回排序之后该记录在改组内部的序号。

比如我们知道有emp表如下:

SQL> SELECT * FROM SCOTT.EMP;     EMPNO ENAME                          JOB                                MGR HIREDATE                  SAL       COMM     DEPTNO---------- ------------------------------ --------------------------- ---------- ------------------ ---------- ---------- ----------      7369 SMITH                          CLERK                             7902 17-DEC-80                 800                    20      7499 ALLEN                          SALESMAN                          7698 20-FEB-81                1600        300         30      7521 WARD                           SALESMAN                          7698 22-FEB-81                1250        500         30      7566 JONES                          MANAGER                           7839 02-APR-81                2975                    20      7654 MARTIN                         SALESMAN                          7698 28-SEP-81                1250       1400         30      7698 BLAKE                          MANAGER                           7839 01-MAY-81                2850                    30      7782 CLARK                          MANAGER                           7839 09-JUN-81                2450                    10      7788 SCOTT                          ANALYST                           7566 19-APR-87                3000                    20      7839 KING                           PRESIDENT                              17-NOV-81                5000                    10      7844 TURNER                         SALESMAN                          7698 08-SEP-81                1500          0         30      7876 ADAMS                          CLERK                             7788 23-MAY-87                1100                    20      7900 JAMES                          CLERK                             7698 03-DEC-81                 950                    30      7902 FORD                           ANALYST                           7566 03-DEC-81                3000                    20      7934 MILLER                         CLERK                             7782 23-JAN-82                1300                    10

用下面的语句显示如下:

SQL> SELECT ENAME,DEPTNO,EMPNO,ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY EMPNO) FROM SCOTT.EMP;ENAME                              DEPTNO      EMPNO ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYEMPNO)------------------------------ ---------- ---------- -----------------------------------------------CLARK                                  10       7782                                               1KING                                   10       7839                                               2MILLER                                 10       7934                                               3SMITH                                  20       7369                                               1JONES                                  20       7566                                               2SCOTT                                  20       7788                                               3ADAMS                                  20       7876                                               4FORD                                   20       7902                                               5ALLEN                                  30       7499                                               1WARD                                   30       7521                                               2MARTIN                                 30       7654                                               3BLAKE                                  30       7698                                               4TURNER                                 30       7844                                               5JAMES                                  30       7900                                               6