深入探讨:oracle中row_number() over()分析函数用法

复制代码 代码如下:

select rownum,ename,job,
row_number() over (order by rownum) row_number
from emp;
ROWNUM ENAMEJOBROW_NUMBER
---------- ---------- --------- ----------
1 SMITHCLERK1
2 ALLENSALESMAN2
3 WARDSALESMAN3
4 JONESMANAGER4
5 MARTINSALESMAN5
6 BLAKEMANAGER6
7 CLARKMANAGER7
8 SCOTTANALYST8
9 KINGPRESIDENT9
10 TURNERSALESMAN10
11 ADAMSCLERK11
12 JAMESCLERK12
13 FORDANALYST13
14 MILLERCLERK14

复制代码 代码如下:

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;

复制代码 代码如下:

A BROW_NUMBER()OVER(ORDERBYB)
-- ---------- --------------------------
1 one3
4 four2
5 five1
13 thirteen4
22 twenty two5

复制代码 代码如下:

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;

复制代码 代码如下:

A BROW_NUMBER
-- ---------- ----------
1 one3
4 four2
5 five1
13 thirteen1
22 twenty two