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