Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  397 随笔 :: 33 文章 :: 29 评论 :: 0 Trackbacks
rownum伪劣的相关应用
 
 
1、在SQL中使用rownum的限制:
 

    select * from t1 where rownum >= 1 ;

    select * from t1 where rownum >= 2 ;

    对于上面两个SQL来说,第一个可以返回所有记录,第2个SQL返回0条记录;
 
    原因是Oracle扫描第1条记录时,因为不满足而无法输出,因此rownum序列重新置0,然后永远因无法满足条件而不能输出,最终返回0条记录。包括rownum=2也由于此原因不能得到记录。
 
    所以当需要rownum >= n 时,需要先把rownum转化为实列,例如:

 

    select num from

    ( select num, rownum rn from t1)

    where rn >= 10 ;

 

    另外,rownum列不能加任何基表的前缀,例如:

    selectid,t1.rownum from t1;  --ORA-01747: invalid user.table.column specification

 

 

2、对表排序,再取出第i到第j条记录:

 

    select * from

    ( select num, rownum rn from

    ( select num from t1 order by id ))

    where rn between 5 and 10 ;

 

    注意:使用minus来取可能出现问题,因为会自动distinct掉

    select * from

    (select num from t1 orderbyid)

    whererownum <= 10

    minus

    select * from

    (select num from t1 orderbyid)

    whererownum <= 4 ; ----会发生错误,因为minus自动distinct

 

 

3、order by的一些特性:

 

    (select * from t1);

    select * from t1 orderbyid;

    (select * from t1 orderbyid);--发生错误ORA-00907: missing right parenthesis

    (select * from t1 )orderbyid;

    select * from (select * from t1 orderbyid);

    其中除第3个外,其余均可正确执行,主要是由于Oracle的解析规则造成,修改成第4种方式即可。

 

 

4、一条关于rownum语句的问题:

 

    select * from t1 whererownum <= 10orderbyiddesc;

    select * from t1 whererownum <= 10orderby num desc;

 

    比较上面两条语句,唯一的区别是id字段上有索引,num字段没有索引

 

    乍一看这两条语句都会先进行筛选rownum <= 10 然后再排序,则得出的结果集应该是相同的10行记录,但是实际上两条语句运行出来的结果是完全不同的。

 

    主要的原因还是由于RBO会判断排序字段是否有索引。如果存在索引:就先排序,然后进行rownum筛选;如果字段无索引则先进行筛选再排序。

 

 

5、标准SQL函数row_number() over()的用法:

 

    selectid,num,row_number() over(partitionbyidorderby num) from t1;

 

    即对id分组后,再按照num的排列顺序取出一次的row_number

    在实际应用中可以取出例如:每个班的最后10名的成绩,每个月最大的20比交易等等。

 

 

6、使用rownum提取依次的序列:

 

    这是一个比较基本的应用了:

    selectrownumfrom dual connectbyrownum<=10;

 

    把rownum换成level的效果也是一样:

    selectlevelfrom dual connectbylevel<=10;

 

    这个在实际的应用还是比较广泛的,例如:列出当月的所有天数

    select trunc(sysdate,'mm')+rownum-1as everyday from dual

    connectbyrownum <= to_number(to_char(last_day(sysdate),'dd'));

 

 

 
posted on 2008-08-14 20:58 decode360 阅读(205) 评论(0)  编辑  收藏 所属分类: 05.SQL

只有注册用户登录后才能发表评论。


网站导航: