Oracle row_number分析函数

Oracle row_number分析函数

Oracle 分析函数 row_number(),返回一个整数值(>=1);

语法格式:

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

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

此用法有点像rownum,为每一行返回一个不相同的值：

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

如果没有partition by子句, 结果集将是按照order by 指定的列进行排序；

1. with row_number_test as(   
2.      select 22 a,'twenty two' b from dual union all   
3.      select 1,'one' from dual union all   
4.      select 13,'thirteen' from dual union all   
5.      select 5,'five' from dual union all   
6.      select 4,'four' from dual)   
7. select a,b,   
8.        row_number() over (order by b)   
9. from row_number_test   
10. order by a;  

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

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

1.  A B          ROW_NUMBER()OVER(ORDERBYB)   
2. -- ---------- --------------------------   
3.  1 one                                 3   
4.  4 four                                2   
5.  5 five                                1   
6. 13 thirteen                            4   
7. 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 ...]进行排序(升序),

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

1. with row_number_test as(   
2.      select 22 a,'twenty two' b,'*' c from dual union all   
3.      select 1,'one','+' from dual union all   
4.      select 13,'thirteen','*' from dual union all   
5.      select 5,'five','+' from dual union all   
6.      select 4,'four','+' from dual)   
7. select a,b,   
8.        row_number() over (partition by c order by b) row_number   
9. from row_number_test   
10. order by a;  

这个例子中，我们先按照c列分组，分为2组('*'组,'+'组)，

再按照每个小组的b列进行排序(按字符串首字母的ascii码排),

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

1.  A B          ROW_NUMBER   
2. -- ---------- ----------   
3.  1 one                 3   
4.  4 four                2   
5.  5 five                1   
6. 13 thirteen            1   
7. 22 twenty two          2