ITPub博客

首页 > 数据库 > Oracle > 数据操纵语言——Oracle rownum用法详解

数据操纵语言——Oracle rownum用法详解

Oracle 作者:m2homme 时间:2013-12-08 11:06:57 0 删除 编辑

对付rownum去说它是oracle系统次第分派为从查询返回的行的编号,返回的第一行分派的是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; 
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------


(2)rownum对付大年夜于某值的查询条件

   假设想找到从第二行记真当前的记真,当使用rownum>2是查不出记真的,缘由是由于rownum是一个总是从1开初的伪列,

Oracle 以为rownum> n(n>1的自然数)那种条件照旧不成立,以是查不到记真。

查找到第二行当前的记真可以使用以下的子查询体式格式去解决。注重子查询中的rownum必须要有别号,可则借是不会查出记真去,

那是由于rownum不是某个表的列,假设不起别号的话,没法知道rownum是子查询的列借是主查询的列。

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

(3)rownum对付小于某值的查询条件
rownum对付rownum1的自然数)的条件以为是成立的,以是能够找到记真。
SQL> select rownum,id,name from student where rownum <3;
    ROWNUM ID     NAME
---------- ------ ---------------------------------------------------
        1 200001 张一
        2 200002 王二

查询rownum在某区间的数据,必须使用子查询。例如要查询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标出正确序号(有小到大年夜)
笔者在事变中有一上百万笔记真的表,在jsp页面中需对该表停止分页隐示,便思考用rownum去作,下面是详细体式格式(每页隐示20条): 

“select * from tabname where rownum<20 order by name" 

但却发现oracle却不能按自己的意愿去实施,而是先随便取20笔记真,然后再order by,后经征询oracle,说rownum简直便那样,想用的话,只能用子查询去真现先排序,后rownum,体式格式以下: 

"select * from (select * from tabname order by name) where rownum<20",但那样一去,效力会低很多。 

后经笔者试验,只需在order by 的字段上加主键或索引即可让oracle先按该字段排序,然后再rownum;

体式格式不变:    “select * from tabname where rownum<20 order by name"



得到某列中第N大年夜的行
select column_name from 
(select table_name.*,dense_rank() over (order by column desc) rank from table_name) 
where rank = &N; 
 假设要返回前5笔记真:
select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6) 
假设要返回第5-9笔记真:
select * from tablename 
where … 
and rownum<10 
minus 
select * from tablename 
where … 
and rownum<5 
order by name 
选出成效后用name排序隐示成效。(先选再排序)

注重:只能用以上标记(<、<=、!=)。

select * from tablename where rownum != 10;返回的是前9笔记真。 
不能用:>,>=,=,Between...and。由于rownum是一个总是从1开初的伪列,Oracle 以为那种条件不成立。

另中,阿谁别式格式更快:

select * from ( 
select rownum r,a from yourtable 
where rownum <= 20 
order by name ) 
where r > 10 
那样掏出第11-20笔记真!(先选再排序再选)

要先排序再选则须用select嵌套:内层排序中层选。 
rownum是随着成效散天生的,一旦天生,便不会革新了;同时,天生的成效是逆次递加的,出有1便永远不会有2! 
rownum 是在查询纠开发生的历程中发生的伪列,并且假设where条件中存在 rownum 条件的话,则:

1: 假设鉴定条件是常量,则: 
只能 rownum = 1, <= 大年夜于1 的自然数, = 大年夜于1 的数是出有成效的;大年夜于一个数也是出有成效的 
即 当呈现一个 rownum 不谦足条件的时分则 查询结束 this is stop key(一个不谦足,系统将该记真过滤掉,则下一笔记真的rownum借是阿谁,所当前面的便不再有谦足记真,this is stop key);

2: 假设鉴定值不是常量,则:
若条件是 = var , 则只要当 var 为1 的时分才谦足条件,阿谁时分不存在 stop key ,必须停止full scan ,对每个谦足其他where条件的数据停止鉴定,选出一行后才能去选rownum=2的行……
以下摘自《中国IT真验室》
1.在ORACLE中真现SELECT TOP N
   由于ORACLE不支持SELECT TOP语句,以是在ORACLE中常常是用ORDER BY跟ROWNUM的组开去真现SELECT TOP N的查询。
简单天说,真现体式格式以下所示:
SELECT 列名1...列名n FROM
    (SELECT 列名1...列名n FROM 表名 ORDER BY 列名1...列名n)
   WHERE ROWNUM <= N(抽出记真数)
ORDER BY ROWNUM ASC
下面举个例子简单阐明一下。
瞅客表customer(id,name)有以下数据:
   ID      NAME
   01      first

   02       Second

   03        third

   04        forth
   05        fifth
   06 sixth
  07 seventh
  08 eighth
  09 ninth

 10 tenth

11 last



   则按NAME的字母逆抽出前三个瞅客的SQL语句以下所示:

SELECT * FROM


    (SELECT * FROM CUSTOMER ORDER BY NAME)


   WHERE ROWNUM <= 3


   ORDER BY ROWNUM ASC


   输出成效为:


ID NAME


   08 eighth


   05 fifth


   01 first


2.在TOP N记录中抽出第M(M <= N)笔记真


ROWNUM是记真表中数据编号的一个躲躲子段,以是能够在得到TOP N笔记真的时分同时抽出记真的ROWNUM,然后再从那N笔记真中抽取记真编号为M的记真,即便我们希看得到的成效。


从上面的阐明能够很随便马虎得到下面的SQL语句。


SELECT 列名1...列名n FROM


     (


     SELECT ROWNUM RECNO, 列名1...列名nFROM


       (SELECT 列名1...列名n FROM 表名 ORDER BY 列名1...列名n)


     WHERE ROWNUM <= N(抽出记真数)


   ORDER BY ROWNUM ASC


     )


   WHERE RECNO = M(M <= N)


同样以上表的数据为根柢,那么得到以NAME的字母逆排序的第二个瞅客的疑息的SQL语句该当那样写:


   SELECT ID, NAME FROM


     (


      SELECT ROWNUM RECNO, ID, NAME FROM


        (SELECT * FROM CUSTOMER ORDER BY NAME)


         WHERE ROWNUM <= 3


         ORDER BY ROWNUM ASC )


       WHERE RECNO = 2


     成效则为:


   ID NAME


    05 fifth


3.抽出按某种体式格式排序的记真散开的第N笔记真


   在2的阐明中,当M = N的时分,即为我们的标题讲的成效。真际上,2的做法在里面N>M的局部的数据是根基上不会用到的,我们仅仅是为了阐明利便而采用。


   如上所述,则SQL语句应为:


SELECT 列名1...列名n FROM


     (


      SELECT ROWNUM RECNO, 列名1...列名nFROM


        (SELECT 列名1...列名n FROM 表名 ORDER BY 列名1...列名n)


         WHERE ROWNUM <= N(抽出记真数)


      ORDER BY ROWNUM ASC


     )


     WHERE RECNO = N


     那么,2中的例子的SQL语句则为:


    SELECT ID, NAME FROM


      (


       SELECT ROWNUM RECNO, ID, NAME FROM


         (SELECT * FROM CUSTOMER ORDER BY NAME)


       WHERE ROWNUM <= 2


       ORDER BY ROWNUM ASC


      )


      WHERE RECNO = 2


     成效为:


   ID NAME


    05 fifth


4.抽出按某种体式格式排序的记真散开的第M笔记真开初的X笔记真


   3里所讲得仅仅是抽取一笔记真的景象,当我们必要抽取多笔记真的时分,此时在2中的N的取值该当是在N >= (M + X - 1)阿谁规模内,当让最经济的取值便是取等好的时分了的时分了。当然最初的抽取条件也不是RECNO = N了,该当是RECNO BETWEEN M AND (M + X - 1)了,以是随之而去的SQL语句则为:


   SELECT 列名1...列名n FROM


    (


     SELECT ROWNUM RECNO, 列名1...列名nFROM


      (

SELECT 列名1...列名n FROM 表名 ORDER BY 列名1...列名n)

         WHERE ROWNUM <= N (N >= (M + X - 1))
         ORDER BY ROWNUM ASC
 )


     WHERE RECNO BETWEEN M AND (M + X - 1)


    同样以上面的数据为例,则抽取NAME的字母逆的第2笔记真开初的3笔记真的SQL语句为:


   SELECT ID, NAME FROM


     (


      SELECT ROWNUM RECNO, ID, NAME FROM


        (SELECT * FROM CUSTOMER ORDER BY NAME)


      WHERE ROWNUM <= (2 + 3 - 1)


      ORDER BY ROWNUM ASC


     )


     WHERE RECNO BETWEEN 2 AND (2 + 3 - 1)


     成效以下:


   ID NAME


    05 fifth


    01 first


   04 forth


    以此为根柢,再扩大的话,做成存储历程,将开初记真数和抽取记真数为参数,便能够轻松真现分页抽取数据。


   当然了,上面所讲的皆是一些最根基的,真际使用中常常皆出有那么简单,可是不管怎么说,不管复杂的使用总是由那些简单的元素构成,把握一些最根基的体式格式初终是重要的。
<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21726601/viewspace-1115730/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-05-23