網頁

2021/6/29

Oracle ROW_NUMBER()用法

Oracle ROW_NUMBER()用法如下。


ROW_NUMBER()是分析函式(analytic function),作用為賦予查詢結果依指定排序的每一列(row)一個唯一數字,從1開始算。作用有點類似僞欄ROWMUM

ROW_NUMBER()語法如下:

ROW_NUMBER() OVER ([query_partition_clause] order_by_clause)

query_partition_clause為分組語法,選填;order_by_clause為配號的排序條件。

例如EMPLOYEE資料表有以下資料。

+----+---------------+------+-----+
| ID | DEPARTMENT_ID | NAME | AGE |
+----+---------------+------+-----+
|  1 |             1 | John |  33 |
|  2 |             1 | Mary |  28 |
|  3 |             2 | Andy |  26 |
|  4 |             2 | Zoey |  22 |
+----+---------------+------+-----+

使用ROW_NUMBER()依照AGE由小大到排序配發號碼。

select 
    row_number() over (order by e.age) as rn, 
    e.* 
from employee e 
order by e.age; 

查詢結果。

+----+----+---------------+------+-----+
| RN | ID | DEPARTMENT_ID | NAME | AGE |
+----+----+---------------+------+-----+
|  1 |  4 |             2 | Zoey |  22 |
|  2 |  3 |             2 | Andy |  26 |
|  3 |  2 |             1 | Mary |  28 |
|  4 |  1 |             1 | John |  33 |
+----+----+---------------+------+-----+

若使用ROWNUM,則每一列號碼不會依順序排列。

select 
    rownum, 
    e.* 
from employee e 
order by age;

查詢結果。

+--------+----+---------------+------+-----+
| ROWNUM | ID | DEPARTMENT_ID | NAME | AGE |
+--------+----+---------------+------+-----+
|      4 |  4 |             2 | Zoey |  22 |
|      3 |  3 |             2 | Andy |  26 |
|      2 |  2 |             1 | Mary |  28 |
|      1 |  1 |             1 | John |  33 |
+--------+----+---------------+------+-----+

ROWNUM要達到相同的效果必須用子查詢查出排序的結果才套用。

select 
    rownum, 
    e.* 
from (
    select * 
    from z_employee 
    order by age
) e

查詢結果。

+--------+----+---------------+------+-----+
| ROWNUM | ID | DEPARTMENT_ID | NAME | AGE |
+--------+----+---------------+------+-----+
|      1 |  4 |             2 | Zoey |  22 |
|      2 |  3 |             2 | Andy |  26 |
|      3 |  2 |             1 | Mary |  28 |
|      4 |  1 |             1 | John |  33 |
+--------+----+---------------+------+-----+


此外ROW_NUMBER()還可以加上query_partition_clause指定分組。也就是PARTITION BY

例如下面依DEPARTMENT_ID分組配號。

select 
    row_number() over (partition by e.department_id order by e.age) as rn, 
    e.* 
from z_employee e 
order by e.age; 

查詢結果,可以看到依DEPARTMENT_ID分組再依AGE由小到大分配號碼。

+----+----+---------------+------+-----+
| RN | ID | DEPARTMENT_ID | NAME | AGE |
+----+----+---------------+------+-----+
|  1 |  4 |             2 | Zoey |  22 |
|  2 |  3 |             2 | Andy |  26 |
|  1 |  2 |             1 | Mary |  28 |
|  2 |  1 |             1 | John |  33 |
+----+----+---------------+------+-----+


沒有留言:

張貼留言