Oracle中rownum的用法

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;

 

站外扩展阅读:

解析oracle的rownum

 

posted @ 2014-06-03 17:00  IT浪潮之巅  阅读(575)  评论(0编辑  收藏  举报
   友情链接: 淘宝优惠券