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

標籤：style   blog   color   io   for   art   ar   div   

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