Oracle的rownum研究

使用好久oracle了， 今天有时间研究了一下它的ROWNUM。

rownum是oracle系统依次分配为从查询返回的行的编号（按插入数据库先后），它不能以任何表的名称作为前缀，这个伪字段可以用于限制查询返回的行数。

举例说明：
SCORE_TEST(分数)表，表结构为：

names　　　　VARCHAR2(20)　　　--姓名

scores                 number                             --分数
create table SCORE_TEST(names VARCHAR2(20), scores number);
insert into SCORE_TEST values('Liu',92);
insert into SCORE_TEST values('Zha',88);
insert into SCORE_TEST values('Hao',80);
commit;
(1) rownum 等于的查询条件
当从分数表中查询第一条信息，可以使用rownum=1作为条件。但是查找分数表中第二条信息，使用rownum=2是查不到数据。因为rownum都是从1开始，ORACLE认为1以上的自然数在和rownum做等于判断时都是false的，所以无法查到rownum = n（n>1的自然数）。
如：（PLSQL）

select ROWNUM, NAMES, SCORES from SCORE_TEST where rownum = 1;

           ROWNUM  NAMES  SCORES 
---------- ------ ---------------------------------------------------
                   1                Liu           92

select ROWNUM, NAMES, SCORES from SCORE_TEST where rownum = 2;

           ROWNUM  NAMES  SCORES 
---------- ------ ---------------------------------------------------
（2）rownum大于的查询条件
   当想查找从第二行记录以后的记录，使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立

select ROWNUM, NAMES, SCORES from SCORE_TEST where rownum > 2;

           ROWNUM  NAMES  SCORES 
---------- ------ ---------------------------------------------------
如何才能找到第二行以后的记录呢。可以使用子查询方法来解决。注意子查询中的rownum要有别名，要不然还是不会查出记录来。因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。
select CNT, NAMES, SCORES  from (select ROWNUM, NAMES, SCORES from SCORE_TEST) where ROWNUM >2;   

           ROWNUM  NAMES  SCORES

---------- ------ ---------------------------------------------------

select CNT, NAMES, SCORES  from (select ROWNUM CNT, NAMES, SCORES from SCORE_TEST) where CNT >2;

           ROWNUM  NAMES  SCORES

---------- ------ ---------------------------------------------------
                  3                Hao           80 

（3）rownum小于的查询条件
当想找到第三条记录以前的记录，当使用rownum<3是能得到两条记录的。显然rownum对于rownum1的自然数）的条件ORACLE认为是成立的。
select ROWNUM, NAMES, SCORES from SCORE_TEST where rownum < 3;

           ROWNUM  NAMES  SCORES

---------- ------ ---------------------------------------------------
                  1               Liu             92 

                  2               Zha           88 

（4）

综上几种情况，有时候需要查询rownum在某区间的数据，可以用rownum对小于某值的查询条件是人为true的，rownum对于大于某值的查询条件直接认为是false的，但是可以间接的让它转为认为是true的。那就必须使用子查询。例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，只要写以下语句，先让它返回小于等于三的记录行，然后在主查询中判断新的rownum的别名列大于等于二的记录行。（注意：这样的操作会在大数据集中影响速度）
select CNT, NAMES, SCORES  from (select ROWNUM CNT, NAMES, SCORES from SCORE_TEST where ROWNUM < =3) where CNT >2;

           ROWNUM  NAMES  SCORES

---------- ------ ---------------------------------------------------
                  3                Hao           80 

（5）rownum和排序
Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。
select ROWNUM, NAMES, SCORES  from SCORE_TEST ORDER BY SCORES

           ROWNUM  NAMES  SCORES

---------- ------ ---------------------------------------------------
                  3                Hao           80 

                  2               Zha            88 

                  1               Liu             92 

可以看出，rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。为了解决这个问题，可以使用子查询
select ROWNUM, NAMES, SCORES  from (SELECT * FROM SCORE_TEST ORDER BY SCORES)

           ROWNUM  NAMES  SCORES

---------- ------ ---------------------------------------------------
                  1                Hao           80 

                  2               Zha            88 

                  3              Liu             92 

 

 

（结）