Oracle中rownum的用法

浏览数：23 / 时间：2015年06月12日

rownum是Oracle对查询结果进行顺序编号，第一行分配1，第二行2，以此类推。rownum不能以任何表的名称作为前缀。

rownum这个伪字段可以用于控制返回的记录行数。

例如表：student(学生)表，表结构为：

ID　　　    char(6)　　　　　 --学号
name　　　　VARCHAR2(10)　　　--姓名
create table student (ID char(6), name VARCHAR2(100));
insert into sale values(‘200001‘,‘张一’);
insert into sale values(‘200002‘,‘王二’);
insert into sale values(‘200003‘,‘李三’);
insert into sale values(‘200004‘,‘赵四’);
commit;

1. rownum对于等于某值的查询：

查询第一条学生的信息，可以使用rownum=1作为条件，但第二条不能用rownum=2来查询。

因为rownum都是从1开始，但1以上的自然数在rownum做等于判断是时都是false的，所以无法查到rownum = n（n>1的自然数）。

select rownum,id,name from student where rownum=1;（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标）
ROWNUM  ID     NAME
  1   200001   张一

select rownum,id,name from student where rownum =2;
ROWNUM   ID  NAME
无数据

2. rownum对于大于某值的查询：

如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列。
Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录。

select rownum,id,name from student where rownum >2;
ROWNUM   ID   NAME
无数据

可以通过子查询来获取第2条以后的记录，但子查询中的rownum必须要有别名，因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。

select * from(select rownum no ,id,name from student) where no>2;
NO    ID     NAME
3   200003   李三
4   200004   赵四

select * from (select rownum,id,name from student) where rownum>2;
ROWNUM  ID  NAME
无数据

3. rownum对于小于某值的查询：

查询第三条以前的记录，当使用rownum<3是能得到两条记录的。rownum对于rownum<n（(n>1的自然数）的条件认为是成立的，所以可以找到记录。

select rownum,id,name from student where rownum <3;
ROWNUM    ID      NAME
   1     200001   张一
   2     200002   王二

4. rownum某个范围数据的查询：

rownum对小于某值的查询条件是true的，rownum对于大于某值的查询条件是false的，但是可以间接的让它转为是true的。那就要使用子查询。

例如要查询rownum在第2行到第3行之间的数据(包含)，那么我们先让它返回小于等于3的记录，然后在主查询中判断新的rownum的别名列>=2的记录行。
但是这样的操作会在大数据集中影响速度。

select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;
 NO    ID     NAME
 2   200002   王二
 3   200003   李三

5. rownum的排序：

Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。

select rownum ,id,name from student order by name;
ROWNUM     ID      NAME
  3      200003    李三
  2      200002    王二
  1      200001    张一
  4      200004    赵四

可以看出，rownum并不是按照name列来生成的序号。系统在首次查询结果后就给分配了序号，rowid也是顺序分配的。

为了解决这个问题，应该使用子查询：

select rownum ,id,name from (select * from student order by name);
ROWNUM    ID      NAME
   1    200003    李三
   2    200002    王二
   3    200001    张一
   4    200004    赵四
这样就成了按name排序，并且用rownum标出正确序号（有小到大）

个人总结：

1.对于=1或<的可以直接用rownum。

2.对于>或某范围内的查询：先给rownum取别名后，用子查询处理变成正常的查询结果，再进行进一步的处理。

实例演示：

SELECT z.*, ROWNUM row_num
FROM (SELECT   a.bookid, a.vote_month
      FROM tbbookclick a
      WHERE vote_month > 0
      ORDER BY a.vote_month DESC) z
WHERE ROWNUM <= inintpagesize * inintpageindex

 OPEN outcurlist FOR
            SELECT 
                     t.bookid, t.bookname, t.authorid, t.authorname,
                     t.channelid, t.channelname, t.categoryid,
                     t.categoryname, t.subcategoryid, t.subcategoryname,
                     t.actionstatus, t.newchapterid, t.newchaptername,
                     t.newchaptertime, t.newvipchapterid,
                     t.newvipchaptername, t.newvipchaptertime, t.vipstatus,
                     m.clickrate_month
                FROM tbbook t,
                     (SELECT bookid, clickrate_month
                        FROM (SELECT z.*, ROWNUM row_num 
                                FROM (SELECT   a.bookid, a.clickrate_month
                                          FROM tbbookclick a
                                         WHERE clickrate_month > 0
                                      ORDER BY a.clickrate_month DESC) z
                               WHERE ROWNUM <= inintpagesize * inintpageindex)
                       WHERE row_num BETWEEN     inintpagesize
                                               * (inintpageindex - 1) + 1
                                         AND inintpagesize * inintpageindex) m
               WHERE t.bookid = m.bookid
            ORDER BY clickrate_month DESC;