Oracle rownum介绍

喜欢ヅ旅行 2022-06-10 08:28 307阅读 0赞

rownum是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 （强调：先要有结果集）。简单的说rownum是对符合条件结果的序列号。它总是从1开始排起的。

rownum和rowid都是伪列，但是两者的根本是不同的，rownum是根据sql查询出的结果给每行分配一个逻辑编号，所以你的sql不同也就会导致最终rownum不同，但是rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录。

用法：

**1、查询第几行的记录**

select sal from emp where rownum=1; //查询得到第一行记录

select sal from emp where rownum=5; //不可以查询到第五行记录，因为rownum 总是从1开始查询的，故这种方式不可以直接得到第几行的记录。若想得到第五行记录，应采用如下方式：

select r,sal from (select rownum r,sal from emp) where r= 5;

**2、用来获取前几行的记录，即小于某值的记录。**

例：查询前四行的记录

select rownum,sal from emp where rownum<5;

**3、用来获取后几行的记录，即大于某值的记录**。例：查询第五行之后的记录

select r,sal from (select rownum r,sal from emp ) where r>5;

**4、查询一个范围内的记录。**如查询第三行到底八行的记录：

select r,sal from (select rownum r,sal from emp) where r>=3 and r<=8;

**5、rownum 与排序**

select rownum,sal from emp order by sal;

从这条语句的运行结果你可以发现，rownum不是从1 依次增大，而是乱的，实际上这些rownum是指每条记录未排序之前所处的行数，当然这不是我们想要的结果，那么如何得到rownum也是顺序排列的查询结果呢？这就需要先对原纪录排序，然后从新的顺序中提取出rownum和想要的记录内容。如：

select rownum,sal from (select \* from emp order by sal) ;

select rownum, sal from (select \* from emp order by sal ) where rownum <5;

select r, sal from (select rownum r,sal from (select \* from emp order by sal )) where r >5;

select r, sal from (select rownum r,sal from (select \* from emp order by sal )) where r >5 and r < 10;

**oracle中的rownum、order by与分页**

先看以下两条语句的执行结果：

语句一：select rownum,empno,sal from emp order by empno;

ROWNUM      EMPNO        SAL  
\---------- ---------- ----------  
         1       7369        800  
         2       7499       1600  
         3       7521       1250  
         4       7566       2975  
         5       7654       1250  
         6       7698       2850  
         7       7782       2450  
         8       7788       3000

……

语句二：select rownum,empno,sal from emp order by sal;

ROWNUM      EMPNO        SAL  
\---------- ---------- ----------  
         1       7369        800  
        12       7900        950  
        11       7876       1100  
         3       7521       1250  
         5       7654       1250  
        14       7934       1300  
        10       7844       1500  
         2       7499       1600

……

同样的两个语句，执行结果中的rownum伪列的值却大相径庭，这是什么原因呢？

其实这都是ORACLE内部的查询优化器和索引搞的鬼！

rownum伪列产生的序号是按照数据被查询出来的顺序添加上去的，第一条是1,第二条是2,依次加1。

当将一条语句交给查询优化器处理时：

如果排序列上有索引，则借助索引去查询数据，这样，读取出来的数据和rownum产生的序号是一种正常的对应关系，如语句一的结果（empno上有主键索引）。

如果排序列上没有索引，则使用全表扫描的方式，依次从表中读取数据，读取完成后，最后进行排序，于是产生了类似语句二的结果（sal列上没有索引）。

正是由于排序列上不一定有索引，所以在ORACLE中使用rownum伪列分页时，需要多加一层查询，以保证rownum序号的连续性。

语句三：select rownum,t.\* from (select empno,sal from emp order by sal) t;

ROWNUM      EMPNO        SAL  
\---------- ---------- ----------  
         1       7369        800  
         2       7900        950  
         3       7876       1100  
         4       7521       1250  
         5       7654       1250  
         6       7934       1300  
         7       7844       1500  
         8       7499       1600

……

这个结果还满意吧。。。

**分页：在外面再加上一层查询。**

select \*  
  from (select rownum num, t.\*  
          from (select empno, sal from emp order by sal) t)  
 where num between 6 and 10;

当然，如果使用分析函数row\_number就可以省略一层查询了，代码更简单点：

select \*  
  from (select row\_number() over(order by sal) num, empno, sal from emp)  
 where num between 6 and 10;