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 |
+----+----+---------------+------+-----+
沒有留言:
張貼留言