row_number() over ([partition by col1] order by col2) ) as 別名
表示根據col1分組，在分組內部根據 col2排序
而這個“別名”的值就表示每組內部排序后的順序編號（組內連續的唯一的）,[partition by col1] 可省略。

??

ROW_NUMBER（）語法如下：

1、row_number() over(order by column asc)先對列column按照升序，再為每條記錄返回一個序列號：

select personalid,row_number() over(order by personalid asc) rn from ?neogoodsrule

2、row_number() over(partition by column1 order by column2 asc) 先按照column1分組，再對分組后的數據進行以column2升序排列

select personalid,ct_smp_type,row_number() over(partition by personalid order by ct_smp_type asc) rn from neogoodsrule

?row_number()over(partition by col1 order by col2)表示根據col1分組，在分組內部根據col2排序，而此函數計算的值就表示每組內部排序后的順序編號（組內連續的唯一的）。
與rownum的區別在于：使用rownum進行排序的時候是先對結果集加入偽劣rownum然后再進行排序，而此函數在包含排序從句后是先排序再計算行號碼。
row_number()和rownum差不多，功能更強一點（可以在各個分組內從1開始排序）。
rank()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內）
dense_rank()也是連續排序，有兩個第二名時仍然跟著第三名。相比之下row_number是沒有重復值的。

oracle 分析函數 row_number(),返回一個整數值(>=1);

語法格式:

1.row_number() over (order by col_1[,col_2 ...])

作用:按照col_1[,col_2 ...]排序,返回排序后的結果集，

此用法有點像rownum,為每一行返回一個不相同的值：

select rownum,ename,job,???
?????? row_number() over (order by rownum) row_number???
from emp;???
??? ROWNUM ENAME????? JOB?????? ROW_NUMBER???
---------- ---------- --------- ----------???
???????? 1 SMITH????? CLERK????????????? 1???
???????? 2 ALLEN????? SALESMAN?????????? 2???
???????? 3 WARD?????? SALESMAN?????????? 3???
???????? 4 JONES????? MANAGER??????????? 4???
???????? 5 MARTIN???? SALESMAN?????????? 5???
???????? 6 BLAKE????? MANAGER??????????? 6???
???????? 7 CLARK????? MANAGER??????????? 7???
???????? 8 SCOTT????? ANALYST??????????? 8???
???????? 9 KING?????? PRESIDENT????????? 9???
??????? 10 TURNER???? SALESMAN????????? 10???
??????? 11 ADAMS????? CLERK???????????? 11???
??????? 12 JAMES????? CLERK???????????? 12???
??????? 13 FORD?????? ANALYST?????????? 13???
??????? 14 MILLER???? CLERK???????????? 14?

如果沒有partition by子句, 結果集將是按照order by 指定的列進行排序；

with row_number_test as(???
???? select 22 a,'twenty two' b from dual union all???
???? select 1,'one' from dual union all???
???? select 13,'thirteen' from dual union all???
???? select 5,'five' from dual union all???
???? select 4,'four' from dual)???
select a,b,???
?????? row_number() over (order by b)???
from row_number_test???
order by a;?

正如我們所期待的,row_number()返回按照b列排序的結果,

然后再按照a進行排序,才得到下面的結果:

A B????????? ROW_NUMBER()OVER(ORDERBYB)???
-- ---------- --------------------------???
1 one???????????????????????????????? 3???
4 four??????????????????????????????? 2???
5 five??????????????????????????????? 1???
13 thirteen??????????????????????????? 4???
22 twenty two????????????????????????? 5?

2.row_number() over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])

作用:先按照col_n[,col_m ...進行分組,

再在每個分組中按照col_1[,col_2 ...]進行排序(升序),

最后返回排好序后的結果集:

with row_number_test as(???
???? select 22 a,'twenty two' b,'*' c from dual union all???
???? select 1,'one','+' from dual union all???
???? select 13,'thirteen','*' from dual union all???
???? select 5,'five','+' from dual union all???
???? select 4,'four','+' from dual)???
select a,b,???
?????? row_number() over (partition by c order by b) row_number???
from row_number_test???
order by a;?
?

這個例子中，我們先按照c列分組，分為2組('*'組,'+'組)，

再按照每個小組的b列進行排序(按字符串首字母的ascii碼排),

最后按照a列排序,得到下面的結果集:

A B????????? ROW_NUMBER???
-- ---------- ----------???
1 one???????????????? 3???
4 four??????????????? 2???
5 five??????????????? 1???
13 thirteen??????????? 1???
22 twenty two?????????
本篇文章來源于 Linux公社網站(www.linuxidc.com)? 原文鏈接：http://www.linuxidc.com/Linux/2011-04/34251.htm

轉載于:https://www.cnblogs.com/kting/p/3070681.html

總結

以上是生活随笔為你收集整理的Oracle 分析函数--Row_Number()的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。