oracle中rownum和row_number()使用方法
一、oracle中rownum
用于从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。
1、rownum 对于等于某值的查询条件
如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。
SQL> select rownum,id,name from student where rownum=1; --有记录
SQL> select rownum,id,name from student where rownum =2; --无记录
2、rownum对于大于某值的查询条件
如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，可以使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。
SQL>select * from(select rownum no ,id,name from student) where no>2; --有记录
SQL> select * from(select rownum,id,name from student)where rownum>2; --无记录
3、rownum对于小于某值的查询条件
如果想找到第三条记录以前的记录，当使用rownum<3是能得到两条记录的。显然rownum对于rownum
SQL> select rownum,id,name from student where rownum <3; --有记录
4、rownum和排序
Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。
SQL> select rownum ,id,name from student order by name;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
3 200003 李三
2 200002 王二
1 200001 张一
4 200004 赵四
可以看出，rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。为了解决这个问题，必须使用子查询
SQL> select rownum ,id,name from (select * from student order by name);
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200003 李三
2 200002 王二
3 200001 张一
4 200004 赵四
二、oracle中row_number()
1、row_number() over (order by col_1[,col_2 ...])
按照col_1[,col_2 ...]排序,返回排序后的结果集，并且为每一行返回一个不相同的值。
2、row_number() over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])
先按照col_n[,col_m ...进行分组，再在每个分组中按照col_1[,col_2 ...]进行排序(升序)，最后返回排好序后的结果集
oracle中row_number()实例
1.使用row_number（）函数进行编号，如
select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer
原理：先按psd进行排序，排序完后，给每条数据进行编号。
2.在订单中按价格的升序进行排序，并给每条记录进行排序代码如下：
select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order
3、统计每一个客户最近下的订单是第几次下的订单。
with tabs as
(
select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order
)
select MAX(rows) as '下单次数',customerID from tabs group by customerID
4、在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行
。
select
ROW_NUMBER() over(partition by customerID order by insDT) as rows,
customerID,totalPrice, DID
from OP_Order where insDT>'2011-07-22'
以上代码是先执行where子句，执行完后，再给每一条记录进行编号。
三、row_number()与rownum的区别
使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序，而row_number()在包含排序从句后是先排序再计算行号码。
附问题:有以下一个SQL语句:
SELECT *
FROM (
SELECT t.*, row_number() OVER (ORDER BY ID) rn
FROM mytable t
)
WHERE rn BETWEEN :start and :end
sql中的order by语句大大降低了处理的速度，如果把order by去掉，相应的执行计划会大大地提高。如果换成下面的sql:
SELECT t.*, row_number() OVER (ORDER BY ID) rn
FROM mytable t
WHERE rownum BETWEEN :start and :end
很明显，这个sql是错的，根本查询不了正确的数据信息。是否有其它的方法可以提高查询速度？
针对以上问题，就必须要了解一下关于row_number和rownum的区别，以及如何来运用这些信息。
首先了解一下rownum是如何进行工作的，根据oracle的官方文档：
如果对rownum进行大于比较，这个比较将直接返回false。如，下列sql语句将不能返回任何数据信息:
SELECT *
FROM employees
WHERE ROWNUM > 1
在查询中，第一条被命中的数据将赋予一个伪列rownum为1，那么这个条件就为false。第二条被命中的数据由于第一条的false将重新成为第一条数据，那么仍然赋值为1，显示这个条件仍然为false。后续所有的数据将重复执行这个逻辑，最后一条数据也没有返回。
这就是为什么之前的第2个查询，应该转换为以下的sql语句:
SELECT *
FROM (
SELECT t.*, ROWNUM AS rn
FROM mytable t
ORDER BY
paginator, id
)
WHERE rn BETWEEN :start and :end
接下来，需要通过创建一些临时数据表来查看这个sql语句的执行性能,我们将创建临时表，追加索引，然后填充数据，最后分析这个sql语句的查询信息。
CREATE TABLE mytable (
id NUMBER(10) NOT NULL,
paginator NUMBER(10) NOT NULL,
value VARCHAR2(50)
)
/
ALTER TABLE mytable
ADD CONSTRAINT pk_mytable_id PRIMARY KEY (id)
/
CREATE INDEX ix_mytable_paginator_id ON mytable(paginator, id)
/
INSERT
INTO mytable(id, paginator, value)
SELECT level, level / 10000, 'Value ' || level
FROM dual
CONNECT BY
level <= 1000000
/
COMMIT
/
BEGIN
DBMS_STATS.gather_schema_stats('"20090506_rownum"');
END;
/
这个Sql语句创建一个包括100万条数据的表，并且创建一个联合索引.
同时，在这个查询中，patinator字段是不是惟一的，是为了在之后展示这样一种现象：
在查询中，某些数据可能在不同的分页查询中出现多次，而某些数据则可能根据不会被查询出
这就是所谓的分页混乱。
然后，分别使用row_numer和rownum分别进行查询，返回从900001到900010之间的10条数据信息。
row_number()
SELECT *
FROM (
SELECT t.*, ROW_NUMBER() OVER (ORDER BY paginator, id) AS rn
FROM mytable t
)
WHERE rn BETWEEN 900001 AND 900010
ID PAGINATOR VALUE RN
900001 90 Value 900001 900001
900002 90 Value 900002 900002
900003 90 Value 900003 900003
900004 90 Value 900004 900004
900005 90 Value 900005 900005
900006 90 Value 900006 900006
900007 90 Value 900007 900007
900008 90 Value 900008 900008
900009 90 Value 900009 900009
900010 90 Value 900010 900010
10 rows fetched in 0.0005s (0.8594s)
SELECT STATEMENT
VIEW
WINDOW NOSORT STOPKEY
TABLE ACCESS BY INDEX ROWID, 20090506_rownum.MYTABLE
INDEX FULL SCAN, 20090506_rownum.IX_MYTABLE_PAGINATOR_ID
rownum
SELECT *
FROM (
SELECT t.*, ROWNUM AS rn
FROM (
SELECT *
FROM mytable
ORDER BY
paginator, id
) t
)
WHERE rn BETWEEN 900001 AND 900010
ID PAGINATOR VALUE RN
900001 90 Value 900001 900001
900002 90 Value 900002 900002
900003 90 Value 900003 900003
900004 90 Value 900004 900004
900005 90 Value 900005 900005
900006 90 Value 900006 900006
900007 90 Value 900007 900007
900008 90 Value 900008 900008
900009 90 Value 900009 900009
900010 90 Value 900010 900010
10 rows fetched in 0.0005s (0.7058)
SELECT STATEMENT
VIEW
COUNT
VIEW
TABLE ACCESS BY INDEX ROWID, 20090506_rownum.MYTABLE
INDEX FULL SCAN, 20090506_rownum.IX_MYTABLE_PAGINATOR_ID
从上文中，可以看出，使用rownum的查询速度略快于row_number函数。
然后再看一个row_number查询，可以看出oracle足够的智能，它可以通过使用联合索引而避免进行排序操作,然后通过使用stopkey操作，可以直接快速查找到相应的数据信息。
rownum查询也同样使用索引，但并没有利用stopkey条件，只是简单的计数操作。
那么，能否同样让rownum使用stopkey呢。在之前的查询中，oracle并不知道这个rn就是在内层查询rownum的别名，我们可以重写查询，在外层查询中使用rownum，这样就可以在外层利用stopkey条件了。这就是我们常见的oracle3层分页的变形：
SELECT *
FROM (
SELECT t.*, ROWNUM AS rn
FROM (
SELECT *
FROM mytable
ORDER BY
paginator, id
) t
)
WHERE rn >= 900001
AND rownum <= 10
ID PAGINATOR VALUE RN
900001 90 Value 900001 900001
900002 90 Value 900002 900002
900003 90 Value 900003 900003
900004 90 Value 900004 900004
900005 90 Value 900005 900005
900006 90 Value 900006 900006
900007 90 Value 900007 900007
900008 90 Value 900008 900008
900009 90 Value 900009 900009
900010 90 Value 900010 900010
10 rows fetched in 0.0005s (0.4714s)
SELECT STATEMENT
COUNT STOPKEY
VIEW
COUNT
VIEW
TABLE ACCESS BY INDEX ROWID, 20090506_rownum.MYTABLE
INDEX FULL SCAN, 20090506_rownum.IX_MYTABLE_PAGINATOR_ID
在这个查询中，oracle利用了stopkey，同时速度只有471ms,比原来更快。
如果row_number和rownum使用同样的执行计划，但为什么rownum明显更快呢。
这是因为：oracle的历史实在是太久了，而不同的时间导致相同的特性却有不同的效果。
rownum在oracle6中被引进，发布时间为1988年，在当时什么资源和条件都不满足的情况下，作为一个简单的计数器，被认为是非常简单和高效的。
而随着时代的发展，更多的需求被提及出来，这时，一个相当于但功能比rownum更强大的函数被引入，这就是row_number函数，它从oracle9i开始被引进。这时，效率已经不再是惟一的条件了，所以row_number的实现也不再以效率为惟一的指标了。
当然，如果你有更多的要求，如分组排序等，则需要使用row_number函数，但如果你仅仅是简单的分页查询，建议使用rownum，这也是为什么在现在的时代rownum还是这么流行(据说在oracle12c中有offset分页操作符了，内部同样使用row_number函数,这样rownum可以退休了)
相关文章
精彩推荐
-
下载创造与魔法 安卓版v1.0.0750
模拟经营 创造与魔法 安卓版v1.0.0750创造与魔法是一款开放世界手游，在游戏中玩家可探索这个奇妙的世
-
下载
创造与魔法修改版 最新版v1.0.0750
模拟经营 创造与魔法修改版 最新版v1.0.0750创造与魔法无限点券版是款探索冒险游戏，该款游戏的操作还是蛮自
-
下载
战争与文明官方版本 安卓版v1.7.16
模拟经营 战争与文明官方版本 安卓版v1.7.16战争与文明是一款由上海邮通科技有限公司开发的战争策略游戏，这
-
下载
迷你世界0元领皮肤无限迷你币版 最新安卓版v1.43.0
模拟经营 迷你世界0元领皮肤无限迷你币版 最新安卓版v1.43.0迷你世界0元购买皮肤版是这款开放沙盒冒险建造游戏的特殊破解版
-
下载
创造与魔法无限经验版 安卓版v1.0.0750
模拟经营 创造与魔法无限经验版 安卓版v1.0.0750创造与魔法无限经验版是款可以改造环境，整个游戏的自由度还是蛮
