`

Oracle的rownum原理和使用

阅读更多
Oracle的rownum原理和使用
在Oracle中,要按特定条件查询前N条记录,用个rownum就搞定了。
select * from emp where rownum <= 5
而且书上也告诫,不能对rownum用">",这也就意味着,如果你想用
select * from emp where rownum > 5
则是失败的。要知道为什么会失败,则需要了解rownum背后的机制:
1 Oracle executes your query.

2 Oracle fetches the first row and calls it row number 1.

3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.

4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).

5 Go to step 3.

了解了原理,就知道rownum>不会成功,因为在第三步的时候查询出的行已经被丢弃,第四步查出来的rownum仍然是1,这样永远也不会成功。

同样道理,rownum如果单独用=,也只有在rownum=1时才有用。



对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数,而且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的自然数)。
SQL> select rownum,id,name from student where rownum=1;(可以用在限制返回记录条数的地方,保证不出错,如:隐式游标)
SQL> select rownum,id,name from student where rownum=1;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
         1 200001 张一
SQL> select rownum,id,name from student where rownum =2;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
(2)rownum对于大于某值的查询条件
   如果想找到从第二行记录以后的记录,当使用rownum>2是查不出记录的,原因是由于rownum是一个总是从1开始的伪列,Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立,所以查不到记录
SQL> select rownum,id,name from student where rownum >2;
ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
那如何才能找到第二行以后的记录呀。可以使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名,否则还是不会查出记录来,这是因为rownum不是某个表的列,如果不起别名的话,无法知道rownum是子查询的列还是主查询的列。
SQL>select * from(select rownum no ,id,name from student) where no>2;
        NO ID     NAME
---------- ------ ---------------------------------------------------
         3 200003 李三
         4 200004 赵四
SQL> select * from(select rownum,id,name from student)where rownum>2;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
(3)rownum对于小于某值的查询条件
如果想找到第三条记录以前的记录,当使用rownum<3是能得到两条记录的。显然rownum对于rownum<n((n>1的自然数)的条件认为是成立的,所以可以找到记录。
SQL> select rownum,id,name from student where rownum <3;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
1 200001 张一
        2 200002 王二
综上几种情况,可能有时候需要查询rownum在某区间的数据,那怎么办呀从上可以看出rownum对小于某值的查询条件是人为true的,rownum对于大于某值的查询条件直接认为是false的,但是可以间接的让它转为认为是true的。那就必须使用子查询。例如要查询rownum在第二行到第三行之间的数据,包括第二行和第三行数据,那么我们只能写以下语句,先让它返回小于等于三的记录行,然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。
SQL> select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;
        NO ID     NAME
---------- ------ ---------------------------------------------------
         2 200002 王二
         3 200003 李三
(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 赵四
这样就成了按name排序,并且用rownum标出正确序号(有小到大)

10:02 浏览 (3379) 评论 (3) 分类: Data Base 收藏 相关推荐 评论
seasar 2008-05-15
order by name 如果name是pk或有index ,查询出来的rownum完全按照1,2,3.....的次序。
seasar 2008-05-15
orcle 10i中执行:
SQL> select rownum ,id,name from student order by name;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200003 李三
2 200002 王二
3 200001 张一
4 200004 赵四

可以看出是按照name排序后的序号。不知道博主用的是oracle什么版本?
分享到:
评论
1 楼 caohj 2008-08-21  
[i][/i][/u][u][/u][u][/u][u]
引用
引用
引用
引用
引用
[img][/img][url][/url][flash=200,200][/flash][b][/b]              

相关推荐

    oracle rownum和distinct

    下面我们将详细地解释 ROWNUM 和 DISTINCT 的工作原理,并分析为什么它们在一起使用时会出现一些问题。 首先, lets' talk about ROWNUM。ROWNUM 是 Oracle 系统赋予查询返回的行的编号,它是从 1 开始的,这个伪...

    Oracle Rownum的使用与JSP分页显示的实现.pdf

    综上所述,Oracle的Rownum特性在JSP分页显示的实现中起到了关键作用,通过巧妙地使用Rownum,可以实现高效、流畅的数据库查询和网页展示。同时,理解Rownum的工作原理,对于优化查询性能和编写高效的SQL语句至关重要...

    Oracle中ROWNUM的使用技巧.docx

    以下是一些关于ROWNUM的使用技巧和注意事项: 1. **Top N 结果输出**:在不考虑特定排序的情况下,我们可以直接通过`WHERE ROWNUM 来获取前N条记录。然而,如果需要基于特定排序获取Top N,这种方法可能会出现问题...

    oracle的rownum用法

    理解`ROWNUM`的工作原理对于编写高效的Oracle查询至关重要,尤其是在处理大型数据集时,能够帮助我们避免不必要的性能问题。正确使用`ROWNUM`可以让我们更好地控制查询结果的范围,从而实现更精细化的数据处理。

    oracle中取中间记录的方法

    本文详细介绍了如何在Oracle数据库中使用`MINUS`操作符配合`ROWNUM`伪列来获取数据表中的中间记录。这种方法虽然简单有效,但在实际应用时还需要注意排序和性能等问题。对于需要频繁处理这类查询的场景,可以考虑...

    ORACLE数据库中ROWNUM用法详解

    Oracle数据库中的ROWNUM是一个非常重要的概念,特别是在处理大型数据集时进行分页查询。ROWNUM是一个伪列,意味着它并...通过深入理解ROWNUM的工作原理,开发者能够更好地设计和优化数据库查询,以满足复杂的业务需求。

    Oracle利用rownum查询出部分数据[归类].pdf

    在Oracle数据库中,`ROWNUM`是一个...理解`ROWNUM`的生成原理以及其与`ORDER BY`的交互对于优化SQL查询和获取预期结果至关重要。在实际的软件开发工作中,掌握这些细节能够帮助我们写出更高效、更符合需求的查询语句。

    oracle分页查询sql

    在Oracle数据库中,实现分页查询主要有两种方式:基于`ROWNUM`和基于`OFFSET`和`FETCH`。在早期版本的Oracle中,通常使用`ROWNUM`来实现分页功能。 #### 三、基于ROWNUM的分页查询 基于`ROWNUM`的分页查询是Oracle...

    解析rownum

    总之,理解`ROWNUM`的工作原理及其限制对于在Oracle中编写高效的查询至关重要。在使用时,需要注意其与查询顺序、`ORDER BY`以及各种比较操作符的相互作用。通过巧妙运用,我们可以更好地控制数据检索,实现高效的...

    Oracle经典教程(入门到精通很详细)

    总的来说,Oracle数据库是一个功能强大且复杂的系统,它拥有众多的特性和组件,学习和掌握Oracle需要对数据库原理有一定的了解,同时还需要通过大量的实践来熟悉和运用Oracle的各种功能和工具。随着实践经验的积累,...

    oracle教学ppt(最全面).zip

    - 分页查询:学习如何使用ROWNUM和ROWNUMBER伪列进行分页检索。 4. **函数和存储过程**: - 内建函数:熟悉Oracle提供的各种内建函数,如数学函数、字符串函数、日期时间函数等。 - 自定义函数:了解如何创建和...

    oracle 分页语句

    - 除了使用`ROWNUM`之外,还可以采用其他技术来实现分页查询,例如使用`OFFSET/FETCH`关键字(在Oracle 12c及以上版本支持),这样可以直接指定跳过的行数和需要返回的行数。 #### 总结 通过以上分析,我们可以...

    Oracle分页

    一、Oracle分页原理 Oracle提供了几种不同的分页方式,包括ROWNUM、ROWNUM BETWEEN、ROWNUMBER() OVER()等。其中,ROWNUM是最基础的分页方法,它为每一行返回的结果集分配一个唯一的整数值。但是,ROWNUM在并行查询...

    oracle分页查询

    3. 使用`ROWNUM`优化:如上所述,通过合理的`ROWNUM`使用和查询提示,可以提升查询性能。 4. 考虑数据分布:如果数据分布不均匀,考虑使用其他分页策略,如基于主键的分页,以减少查询的开销。 总之,Oracle的分页...

    韩顺平oracle课堂笔记

    韩顺平老师的课堂笔记会解析这些方法的原理和使用场景,帮助读者理解如何有效地实现分页查询,优化查询性能,同时避免全表扫描。 三、多表查询 在实际业务中,往往需要对多个表进行联合查询。Oracle支持的多表查询...

    oracle-jdbc分页实现(只需传入sql语句即可实现分页)

    Oracle数据库提供了多种方法来实现分页查询,其中包括使用ROWNUM伪列、游标(CURSOR)以及Oracle的高级分页功能如ROW_NUMBER()、RANK()和DENSE_RANK()等。 1. ROWNUM伪列: ROWNUM是Oracle数据库特有的一个伪列,它...

    oracle scott 转成mysql 的表学习应用

    例如,Oracle中用ROWNUM伪列进行分页,而在MySQL中则使用LIMIT和OFFSET。 最后,了解和实践这个过程可以帮助我们更好地理解和比较两种数据库系统的特性,从而在实际工作中更高效地处理跨平台的数据迁移和管理任务。...

    PRO ORACLE SQL PDF 英文版

    5. **排序与分页**:ORDER BY用于排序查询结果,而ROWNUM或ROW_NUMBER()可以实现数据分页,这对于数据呈现和报表制作至关重要。 6. **DML操作**:INSERT、UPDATE和DELETE语句用于添加、修改和删除数据。书中会详细...

Global site tag (gtag.js) - Google Analytics