row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根據COL1分組，在分組內部根據 COL2排序，而此函數計算的值就表示每組內部排序后的順序編號（組內連續的唯一的)

SQL> SELECT G.*, ROW_NUMBER() OVER(PARTITION BY a ,b ORDER BY c DESC) ROWN
2??? from (select '1' a, '2' b, '1' c
3??????????? from dual
4????????? union all
5????????? select '1', '2', '2'
6??????????? from dual
7????????? union all
8????????? select '1', '3', '3'
9??????????? from dual
10????????? union all
11????????? select '1', '3', '4'
12??????????? from dual
13????????? union all
14????????? select '1', '4', '5' from dual
15????????? union all
16????????? select '1','3','5'from dual) G
17 ;

A B C?????? ROWN
- - - ----------
1 2 2????????? 1
1 2 1????????? 2
1 3 5????????? 1
1 3 4????????? 2
1 3 3????????? 3
1 4 5????????? 1

與rownum的區別在于：使用rownum進行排序的時候是先對結果集加入偽列rownum然后再進行排序，而此函數在包含排序從句后是先排序再計算行號碼．

　row_number()和rownum差不多，功能更強一點（可以在各個分組內從1開時排序）．

　　rank()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內）．

　　dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名。相比之下row_number是沒有重復值的 ．

lag（arg1,arg2,arg3):
arg1是從其他行返回的表達式
arg2是希望檢索的當前行分區的偏移量。是一個正的偏移量，時一個往回檢索以前的行的數目。
arg3是在arg2表示的數目超出了分組的范圍時返回的值。

語句一:

select row_number() over(order by sale/cnt desc) as sort, sale/cnt
from (
select -60 as sale,3 as cnt from dual union
select 24 as sale,6 as cnt from dual union
select 50 as sale,5 as cnt from dual union
select -20 as sale,2 as cnt from dual union
select 40 as sale,8 as cnt from dual);

執行結果:

????????? SORT?????? SALE/CNT
---------- ----------
???????????? 1???????????? 10
???????????? 2????????????? 5
???????????? 3????????????? 4
???????????? 4??????????? -10
???????????? 5??????????? -20

語句二:查詢員工的工資,按部門排序

select ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order from scott.emp;

執行結果:

ENAME?????????????????????????? SAL????? SAL_ORDER
-------------------- ---------- ----------
KING?????????????????????????? 5000????????????? 1
CLARK????????????????????????? 2450????????????? 2
MILLER???????????????????????? 1300????????????? 3
SCOTT????????????????????????? 3000????????????? 1
FORD?????????????????????????? 3000????????????? 2
JONES????????????????????????? 2975????????????? 3
ADAMS????????????????????????? 1100????????????? 4
SMITH?????????????????????????? 800????????????? 5
BLAKE????????????????????????? 2850????????????? 1
ALLEN????????????????????????? 1600????????????? 2
TURNER???????????????????????? 1500????????????? 3
WARD?????????????????????????? 1250????????????? 4
MARTIN???????????????????????? 1250????????????? 5
JAMES?????????????????????????? 950????????????? 6

已選擇14行。

語句三:查詢每個部門的最高工資

select deptno,ename,sal from
???? (select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order
???????? from scott.emp) where sal_order <2;

執行結果:

?????? DEPTNO ENAME????????????????????????? SAL
---------- -------------------- ----------
?????????? 10 KING????????????????????????? 5000
?????????? 20 SCOTT???????????????????????? 3000
?????????? 30 BLAKE???????????????????????? 2850

已選擇3行。

語句四：

select deptno,sal,rank() over (partition by deptno order by sal) as rank_order from scott.emp order by deptno;

執行結果:

???? DEPTNO???????? SAL RANK_ORDER
---------- ---------- ----------
???????? 10??????? 1300?????????? 1
???????? 10??????? 2450?????????? 2
???????? 10??????? 5000?????????? 3
???????? 20???????? 800?????????? 1
???????? 20??????? 1100?????????? 2
???????? 20??????? 2975?????????? 3
???????? 20??????? 3000?????????? 4
???????? 20??????? 3000?????????? 4
???????? 30???????? 950?????????? 1
??????? 30??????? 1250?????????? 2
???????? 30??????? 1250?????????? 2
???????? 30??????? 1500?????????? 4
???????? 30??????? 1600?????????? 5
???????? 30??????? 2850?????????? 6

已選擇14行。

語句五：

select deptno,sal,dense_rank() over(partition by deptno order by sal) as dense_rank_order from scott.emp order by deptn;

執行結果：

???? DEPTNO???????? SAL DENSE_RANK_ORDER
---------- ---------- ----------------
???????? 10??????? 1300???????????????? 1
???????? 10??????? 2450???????????????? 2
???????? 10??????? 5000???????????????? 3
???????? 20???????? 800???????????????? 1
???????? 20??????? 1100???????????????? 2
???????? 20??????? 2975???????????????? 3
???????? 20??????? 3000???????????????? 4
???????? 20??????? 3000???????????????? 4
???????? 30???????? 950???????????????? 1
??????? 30??????? 1250???????????????? 2
???????? 30??????? 1250???????????????? 2
???????? 30??????? 1500???????????????? 3
???????? 30??????? 1600???????????????? 4
???????? 30??????? 2850???????????????? 5

已選擇14行。

語句六：

select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) as lag_ from scott.emp order by deptno;

執行結果：

???? DEPTNO ENAME??????????????????????? SAL LAG_
---------- -------------------- ---------- --------------------
???????? 10 CLARK?????????????????????? 2450
???????? 10 KING??????????????????????? 5000 CLARK
???????? 10 MILLER????????????????????? 1300 KING
???????? 20 ADAMS?????????????????????? 1100
???????? 20 FORD??????????????????????? 3000 ADAMS
???????? 20 JONES?????????????????????? 2975 FORD
???????? 20 SCOTT?????????????????????? 3000 JONES
???????? 20 SMITH??????????????????????? 800 SCOTT
???????? 30 ALLEN?????????????????????? 1600
???????? 30 BLAKE?????????????????????? 2850 ALLEN
???????? 30 JAMES??????????????????????? 950 BLAKE
???????? 30 MARTIN????????????????????? 1250 JAMES
???????? 30 TURNER????????????????????? 1500 MARTIN
???????? 30 WARD??????????????????????? 1250 TURNER

已選擇14行。

轉載于:https://www.cnblogs.com/Kazaf/archive/2011/06/30/2094015.html

總結

以上是生活随笔為你收集整理的row_number() over (partition by....order by...)用法 分组排序的全部內容，希望文章能夠幫你解決所遇到的問題。

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