oracle的rownum oracle的rownum分页

Oracle之分页

在Oracle中有一个方法rownum用来查询第一行到第n行的内容，但没有一个合适的方法若查询第x行到第y行的内容，而在实际应用中却经常需要查询第x行到第y行的内容，这时我们就需要使用rownum和子表查询等内容来进行查询，因为这一块内容属于Oracle总的常用部分所以专门在此介绍。

在Oralce中有一个伪列rownum，其在创建表的时候就存在了却不显示，若要使用这个列可以直接调用即可，也可以对这个列添加别名来调用。

rownum只能用于显示小于某行的数据即第一行开始到你要查询到的那一行为止的数据。

在Oracle把查询第几行到第几行的操作称为分页，其具体操作是通过子查询等操作完成。

select 列名 from (select 表名.*,rownum rn from 表名)表名 where rn操作;

思考如下：

1.选择所有内容

select * from emp;

2.显示rownum

select e.*,rownum rn from(select * from emp)e;

这一步可以精简为下面形式，但某些情况只能用上面那种

select emp.*,rownum rn from emp;

3.查询

select * from(select e.*,rownum rn from (select * from emp)e);

4.其他变化

在某些时候我们需要先对表的内容进行排序，随后查询第x行到第y行的内容，这个时候有一个需要注意的点是rownum是在表产生的时候产生的伪列，所以使用排序会连着rownum的值进行排序，从而达不到想要的效果。

为了解决上述这个问题，我们需要使用子表查询即先排好序，再在新表之中显示rownum来规避这个问题。

考虑到排序的问题，所以在上方第二步的时候使用第一种方法即select e.*,rownum rn from(select * from emp)e;，在内表select * from emp中进行排序可以完成在乱序中找到第x行到第y行的效果。

Oracle之rownum（转载）

一、rownum的说明

rownum是oracle特有的一个关键字。

（1）对于基表，在insert记录时，oracle就按照insert的顺序，将rownum分配给每一行记录，因此在select一个基表的时候，rownum的排序是根据insert记录的顺序显示的，例如：

（2）对于子查询，则rownum的顺序是根据子查询的查询顺序进行动态分配的，例如：

由上图可以看到T1_RN和T2_RN的区别。

t1中的rownum是根据emp这个基表的默认顺序分配的，而内层子循环是根据SAL字段进行排序，所以t2的rownum是根据内层子查询的记录顺序分配的。

----------------------------- 分 割 线 -------------------------------------

二、rownum的一些使用技巧

（1）使用rownum限制查询返回的记录数

1、例如，我们现在只想看到emp表中的第一条记录：

将rownum限制为1，这样就只能查询出一条记录。

2、现在，我们现在想查看emp中的前2条记录：

将rownum的限制为2条，这样就可以查询出前2条记录。

3、假如我们现在只想查看emp中的第二条记录，又该如何写语句呢？

如果我们先这样写：

where条件为：rownum=2，来看看查询结果：

发现没有查出任何数据，为什么呢？这里就要对oracle的rownum做进一步的理解。

因为rownum并不是当作实体数据存放在每一张表中，而是在每一次select查询的时候，根据基表的默认insert顺序由oracle动态分配的，有1才有2，如果rownum没有1，那么2也就没有了意义，所以这个查询就不会有任何结果出来。这个时候我们就需要利用子查询和别名列来实现这个需求：

首先通过子查询，取出emp表的前2条记录，并将子查询中的rownum定义为别名rn，然后在外层查询中，使用where条件使rn=2即可，查询出emp表的第二条记录：

oracle使用(五)_子查询_分页rownum

sql允许多次嵌套，子查询即嵌套其他查询中得查询

可把子查询返回结果理解成一张表，外层查询会把子查询返回的结果当成一张表

子查询要用括号括起来

将子查询放在比较运算符的右边，增强可读性

子查询的分类：

单行子查询：子查询返回一行记录，可使用单行比较运算符

多行子查询：子查询返回多行记录

--有哪些雇员薪水在平均薪水之上

--1、先求平均薪水

select avg(nvl(e.sal,0)) from emp e;

--2.把所有人的薪水和平均薪水作比较，使用的单行比较运算符

select * from emp e where e.sal > (select avg(nvl(e.sal,0)) from emp e);

-- 查询雇员表哪些人是经理人

-- 1.先查询所有经理人 distinct去重(多行子查询)

select distinct e.mgr from emp e;

--2.在雇员表中过滤是经理的

select * from emp where empno in (select distinct e.mgr from emp e);

-- 每个部门的平均薪水等级

--1.求出每个部门的平均薪水等级

select e.deptno,avg(nvl(e.sal,0)) from emp e group by e.deptno;

--2.根据部门平均薪水断出薪水等级

select g.deptno,sg.grade from salgrade sg join (select e.deptno,avg(nvl(e.sal,0)) avgsal from emp e group by e.deptno) g on g.avgsal between sg.losal and sg.hisal;

--1.求平均薪水最高的部门的部门编号

-- 求部门平均薪水

select e.deptno,avg(nvl(e.sal,0)) from emp e group by e.deptno;

-- 求最高的薪水

select max(t.vsal) from (select e.deptno,avg(nvl(e.sal,0)) vsal from emp e group by e.deptno) t;

oracle的rownum oracle的rownum分页

-- 求部门编号（重复的sql可以抽取为视图）

select t.deptno,t.vsal from (select e.deptno,avg(nvl(e.sal,0)) vsal from emp e group by e.deptno) t where t.vsal = (select max(t.vsal) from (select e.deptno,avg(nvl(e.sal,0)) vsal from emp e group by e.deptno) t);

)

--2.求部门的平均薪水的等级

--3.求部门平均的薪水等级 (薪水等级的平均)

--求每个人的薪水等级

select e.deptno, sg.grade

from emp e

join salgrade sg

on e.sal between sg.losal and sg.hisal;

--求部门的平均薪水等级

select t.deptno, avg(t.grade)

from (select e.deptno, sg.grade

from emp e

join salgrade sg

on e.sal between sg.losal and sg.hisal) t

group by t.deptno;

--4.求薪水最高的前5名员工

-- 所有员工薪水降序

select * from emp e order by e.sal desc;

-- oracle中没有limit子句，rownum只存在于查询出来的虚拟表中

select * from (select * from emp e order by e.sal desc) t where rownum <=5;

--5.求薪水最高的第6到10名

-- 默认对于基表 rownum，跟插入顺序有关

select rownum rn,t.* from emp t;

-- 对于子查询rownum跟内层查询的顺序有关

--rn顺序是根据内层查询记录进行分配的

select rownum rn,t.* from (select * from emp e order by e.sal desc) t;

-- 根据rn顺序来过滤第6到10条

select * from (select rownum rn,t.* from (select * from emp e order by e.sal desc) t) t1 where t1.rn >5 and t1.rn <=10;

oracle中ROWNUM使用

Q提出想要把tableA表按照col2列排序后，找出第5-10行记录。咋搞？

按照特定条件查找出前N行数据，可以通过 ROWNUM 解决。

ROWNUM 又被称为 伪列 ，伪列就像在在表中有这么一列，但并不存储在表中，能够基于伪列进行从查询，也能够基于 ROWNUM 进行更新删除操作，例如

不过由于ROWNUM会随着条件限制、表记录更改等发生变化，并不是表记录的真实值，所以尽量避免用ROWNUM来进行删改操作。

ROWNUM 支持能够查出正确记录的操作符为<、<=、!=、=1，对于>、>=、=N(N>1)、BETWEEN…AND…运行时不报错，但直接使用不能查询出正确结果。

ROWNUM 是在查询出结果集后，给结果集添加上一个伪列，类似于给查询出的结果标上序号，序号从1开始，连续递增，不存在序号跳跃的现象。例如：

结果为：

如果加上限制条件：

结果为：

原来第4行会变成第3行，所以如果用WHERE ROWNUM > 3来进行查询时，并不会有任何结果，因为第一条记录(ROWNUM=1)不满足条件被去掉后，原第二条记录就成为第一条记录(ROWNUM=1)，仍旧不满足被去掉，以此类推，所以永远不会有大于3的记录。由此，使用ROWNUM != 2与ROWNUM < 2等价。

回到刚开始的问题，既然ROWNUM不支持BETWEEN…AND…，>号这些，那如何得到第5-10行记录呢？

可以通过使用子集查询来解决：

使用了两次子集查询，第一次是按照col2进行排序，确保使用ROWNUM得到的是有序的结果集，第二次是用ROWNUM找出前10行记录，并将ROWNUM起别名RID保存到临时表，最后通过RID来限制第5行以后记录。这样就得到了第5-10有序记录。

对于为何先使用ORDER BY 再使用ROWNUM<=10的解释：

对于刚开始的问题，还可以使用以下子集查询进行解决：

使用MINUS

使用INTERSECT

ORACLE中的rownum

ORACLE 中ROWNUM用法总结!

Oracle数据库rownum和row_number的不同点

明确对于rownum

来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是二，以此类推，这个为字段可以用于限制查询的返回的总行数，因为rownum总是从1开始，但是1以上的自然数在rownum

做等于判断时都认为是false

条件，所以无法查到

rownum=n

（n》1的自然数），所以查找第二行以后的记录可以用子查询方法来解决，给子查询中的rownum取别名;对于小于某个值的情况两种方法都差不多，但是对于某个不等于一的值或者求某个值到某个值之间的情况，用row_number()

别名获得排名

，比用rownum伪列要简单方便的多；因为伪列总是从一开始查找；

具体用法和区别参见以下代码;

--取出工资最高的前5位

select

empno,ename,sal,rownum

from

emp;

select

*

from

(select

*

from

emp

order

by

sal

desc)

where

rownum<=5;

select

*

from

(select

ename,sal,row_number()

over(order

by

sal

desc)

as

num

from

emp)

where

num<=5;

select

*

from

(select

ename,sal,row_number()

over(order

by

sal

desc)

from

emp)

where

rownum<=5

--工资的前3名

select

*

from

emp

where

sal

>=any(select

*

from

(select

sal

from

emp

order

by

sal

desc)

where

rownum<=3);

select

*

from(select

*

from

emp

order

by

sal

desc)

where

rownum

<4;

select

*

from

(select

ename,sal,empno,deptno

,row_number()

over

(order

by

sal

desc)

from

emp)

where

rownum<4;

select

*

from

(select

ename,sal,empno,deptno

,row_number()

over

(order

by

sal

desc)

as

num

from

emp)

where

num<4

--按照工资排序，取出第6名到第10名

--使用伪列获得

select

*

from

(select

ename,sal,rownum

r

oracle的rownum oracle的rownum分页

from

(select

*

from

emp

order

by

sal

desc)

where

rownum<=10)

where

r>5;

--使用排名函数获得

select

*

from

(select

ename,sal,row_number()

over(order

by

sal

desc)

as

num

from

emp)

where

num>5

and

num<=10;

-------

按工资从高到低获得工资排名第四的员工

select

*

from

(select

ename,sal,row_number()

over(order

by

sal

desc)

as

num

from

emp)

where

num=4;

select

oracle的rownum oracle的rownum分页

*

from

(select

ename,sal,rownum

r

from

(select

*

from

emp

order

by

sal

desc)

where

rownum<=4)

where

r=4;

总结oracle中rownum和row_number()的区别

row_number()是分析函数,基本语法为row_number()

over(partition

by

字段

order

by

字段)

rownum是一个伪列

select

*

from

dept

where

rownum<=3;

select

*

from

dept

where

rownum

between

2

and

3;这儿会出错,因为rownum的特性(没有1就不会有2，没有3)决定的

SELECT

*

FROM

(SELECT

A.*,ROWNUN

FROM

DEPT

A)T1

WHERE

T1.ROWNUM

BETWEEN

2

AND

3;这么写不对,要这样写

SELECT

*

FROM

(SELECT

A.*,ROWNUM

RN

FROM

DEPT

A)T1

WHERE

T1.RN

BETWEEN

2

AND

3;

他们的主要区别是:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而函数row_number()在包含排序从句后是先排序再计算行号码。