ROW_NUMBER()OVER()-创新互联
ROW_NUMBER()OVER() 是Oracle SQL分析函数，主要是用来对要查询的数据分组排序使用。
使用方法
ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col2）
对列col1分组，col2排序操作。
例子：
创新互联公司专注骨干网络服务器租用十载，服务更有保障！服务器租用，
雅安服务器托管 成都服务器租用，成都服务器托管，骨干网络带宽，享受低延迟，高速访问。灵活、实现低成本的共享或公网数据中心高速带宽的专属高性能服务器。SQL> SELECT
2 ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal, empno) AS rn,
3 empno, ename, sal, deptno
4 FROM emp;
RN EMPNO ENAME SAL DEPTNO---------- ---------- ---------- ---------- ----------
1 7934 MILLER 1300 10
2 7782 CLARK 2450 10
3 7839 KING 5000 10
1 7369 SMITH 800 20
2 7876 ADAMS 1100 20
3 7566 JONES 2975 20
4 7788 SCOTT 3000 20
5 7902 FORD 3000 20
1 7900 JAMES 950 30
2 7521 WARD 1250 30
3 7654 MARTIN 1250 30
RN EMPNO ENAME SAL DEPTNO---------- ---------- ---------- ---------- ----------
4 7844 TURNER 1500 30
5 7499 ALLEN 1600 30
6 7698 BLAKE 2850 3014 rows selected.
同时也可以单独使用其来对结果进行排序
可以和order by 对比一下：
SQL> SELECT empno, ename, sal,
2 ROW_NUMBER()OVER(ORDER BY sal, empno) AS rn
3 FROM emp;
EMPNO ENAME SAL RN
---------- ---------- ---------- ----------
7369 SMITH 800 1
7900 JAMES 950 2
7876 ADAMS 1100 3
7521 WARD 1250 4
7654 MARTIN 1250 5
7934 MILLER 1300 6
7844 TURNER 1500 7
7499 ALLEN 1600 8
7782 CLARK 2450 9
7698 BLAKE 2850 10
7566 JONES 2975 11
EMPNO ENAME SAL RN
---------- ---------- ---------- ----------
7788 SCOTT 3000 12
7902 FORD 3000 13
7839 KING 5000 14
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3145491563
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 462 | 4 (25)| 00:00:01 |
| 1 | WINDOW SORT | | 14 | 462 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 462 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
16 consistent gets
1 physical reads
0 redo size
1049 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> SELECT empno, ename, sal
2 FROM emp
3 ORDER BY sal, empno;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7900 JAMES 950
7876 ADAMS 1100
7521 WARD 1250
7654 MARTIN 1250
7934 MILLER 1300
7844 TURNER 1500
7499 ALLEN 1600
7782 CLARK 2450
7698 BLAKE 2850
7566 JONES 2975
EMPNO ENAME SAL
---------- ---------- ----------
7788 SCOTT 3000
7902 FORD 3000
7839 KING 5000
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 150391907
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 462 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 462 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 462 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
16 consistent gets
1 physical reads
0 redo size
943 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
对比ORDER BY 子句，排序结果一样，使用ROW_NUMBER()OVER()函数可生产RN列，便于在某些列表程序选择行数。
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
文章名称：ROW_NUMBER()OVER()-创新互联
文章路径：
http://pwwzsj.com/article/dpehgg.html
