Oracle学习8:Oracle中rownum机制原理&用法详解

#1. 概述
rownum是Oracle引入的虚列
在物理上这个虚列并不存在,只是在查询时才构造出来。伪列通常是自由分配的,用户无法执行修改等操作。
#2. 特点
关于rownum有以下主要特点:

  • rownum不属于任何表。
  • rownum存在的前提,先有结果表。
  • rownum总是从1开始。
  • rownum一般只和<(<=)一起用。
  • 使用rownum进行分页查询需要把rownum转化为实列,并针对rownum查询。
    #2. 机制原理
    rownum的用法看似奇怪,其实如果明白其机制原理就会很简单。
    首先我们来做个简单的实验:从dual表中取出所有的字段,并取出rownum。
    如果我们采用如下的写法:t.rownum
select t.*,t.rownum from dual t;

这样运行就会报01747错:
这里写图片描述
因为实际上,dual表就不存在rownum这个字段,所以我们无法使用t.rownum的格式。
正确的写法,应该是:

select t.*, rownum from dual t;

所以,rownum是一个虚列,不属于任何表。

那么这虚列是怎么来的。我们在做个简单的实验,便于理解:
如下,我们有一个简单的表:test_ljb,共有十条记录。
我们加上rownum。

select t.*, rownum from test_ljb t;

结果如下,很好理解,选出十条记录,rownum从1到10
这里写图片描述
我们加上一个salary的筛选条件:

select t.*, rownum from test_ljb t where t.salary>1200;

结果如下:选出三条记录,rownum从1到3
这里写图片描述
需要注意的是,第二个结果表的rownum对应的employee和第一张并不对应。
如:在第一张表rownum为1时,对应的时Arvin,而第二张对应的是Oracle。

原因如下:
因为rownum是对结果集加的一个伪列,即先查到结果集之后再加上去的一个列
简单的说,rownum是对符合条件结果的序列号。它总是从1开始排起的,所以选出的结果不可能跳过1,而有其他大于1的值
或者说,rownum是一个动态的,根据新的结果集实时变化的

比如,如下语句:

select t.*, rownum from test_ljb t where rownum >1; --大于2、3或者其他任何大于1的值,结果相同。

我们发现没有符合条件的记录。

根据原理,rownum是对结果集的从1开始排。那么以上的语句的结果集是什么呢?

事实上,当执行完from test_ljb时,我们可以把他当作时一个结果表,rownum是从1-10
然后,重点,当我们执行过滤条件,rownum>1 时,第一条记录不满足,剔除。这个时候,新的结果集产生了,原来的第二条记录就成了第一条,相应的rownum变为了1-9
再次比较原来的第一条,现在的第二条记录,他的rownum也是1,也不满足,rownum是1-8
以此类推,流水的记录,铁打的rownum从1开始。所以,直到rownum是1,还不满足。所以最后没有记录被筛选出来,也没了rownum

所以,我们写出的这类语句:

rownum>1
rownum>5 and rownum<10
rownum between 6 and 9

统统都是没结果的。

不过有意思的是,选出前十条,可以有好多写法:

rownum<=10
rownum<11
rownum<>11  --不等于,根据原理应该很好理解,不赘述
rownum!=11  --不等于

#3. 用法
那有的同学就犯嘀咕了,我要做大于查询怎么搞啊,分页查询怎么搞啊,人家Mysql和Hive一个limit a, b 直接完事,你Oracle怎么搞。
其实方法还是有的,也是用rownum,不过要先把这货转化为实列。加个子查询就可以了。
老套路,简单实验走一波。
##3.1 大于查询
还是test_ljb表,就选>5行。

select * from 
(
    select t.*,rownum r from test_ljb t
)t
where t.r>5;

##3.2.1 简单分页查询

3 and <6

select * from 
(
    select t.*,rownum r from test_ljb t
)t
where t.r>3 and t.r<6

##3.3.2 排序分页查询
排序分页查询就麻烦了,首先要排序,然后再排序的基础上再筛选。
当然这个也是实际项目最常用的。
选出薪水最高的第4、5、6个。

select rst.* from 
(
    select t2.*,rownum r2 from 
    (
        select t.*,rownum r1 from test_ljb t order by nvl(salary,0) desc
        --这里需要说明的是,rownum仅仅针对新的结果集动态标记,而排序并不会生成新的结果集。
        --所以这条语句的rownum看起来并不是按照1-10的顺序排列。
        --事实上,这里的rownum r1并不会用到,为了看起来更加直观,加上的。
    )t2
)rst
where rst.r2 between 4 and 6;

这里需要注意的是:rownum仅仅针对新的结果集动态标记,而排序并不会生成新的结果集,如果仅仅执行

select t.*,rownum r1 from test_ljb t order by nvl(salary,0) desc

结果如下:
这里写图片描述

注:这里写SQL语句时,注意缩进,有利于思路流畅。


好了,看到这里相信你对rownum应该熟稔于心啦,那就赶快落实于行,操作一波吧。

参考文献:Oracle-rownum总结

  • 6
    点赞
  • 28
    收藏
    觉得还不错? 一键收藏
  • 3
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值