Oracle rownum详解

Oracle11g官方文档对rownum给出的解释是:

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

翻译:

rownum给查询的结果集增加一个包含数字的伪列, 数字的顺序代表了Oracle从数据文件或者缓冲区中读取该条记录的顺序, 第一条选出来的记录rownum = 1, 第二条 rownum= 2以此类推.(还不好理解的话, 就理解为rownum是查询结果集的行序号)

用途:

1.用于限制查询返回的记录的条数,比如: 查询表mytest 的f_commodity 的前9条记录

(注意如果使用 rownum > 2 这样的语句希望查询得到2行以后的数据,是永远别想得到查询结果的(除非实例化),具体原因参见下面的常见误区)

实例：获取表前10条数据

select  rownum, mytest.f_commodity
from mytest
where rownum < 10;

查询结果:

                           

2.在用途1的基础上, 在Oracle中rownum常常用于分页查询.后面会有分页查询的专题博客.

rownum的常见误区:

1.最常见的误区莫过于以为可以使用类似 where rownum  between 3 and 10 的语句来查询任意指定区间的记录.

错误示范:

select  rownum, mytest.f_commodity
from mytest
where rownum > 20;

输出结果: 一条记录都没有

原因分析: 

对于Oracle读取的第一条记录而言, 其rownum 值只能为 1, 但是记录筛选的限制条件为"rownum > 20", 因此第一条被读取的记录不符合筛选的条件,  没有记录被筛选出来。 此时结果集为空(注意rownum是附加给结果集的), rownum = 1还没有归属, 下一条记录现在成了第一条待筛选记录, rownum又被赋为1, 同样的被"rownum > 20"的条件淘汰, 结果就是所有被读取的列都得到了rownum = 1的值, 都被"rownum > 20"条件淘汰,没有记录会被筛选出来. 上述的原因同样可以用来解释为什么 rownum >= 1可以查询出记录，而rownum  > 1 却查询不到记录.

(The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.)

解决办法: 

使用子查询+别名 实例化rownum,从而达到指定区间查询的目的.

select * from (
    select rownum rn, mytest.f_commodity
    from mytest
    where rownum <= 20 --子查询选出前20条记录, 并添加rownum的伪列,给rownum伪列别名rn实例化伪列(一定要给别名,不能继续用rownum)
)where rn > 10;

2.第二点也谈不上是误区，不过也算是注意事项。如果你在同一层查询中，使用了rownum和order by，那么rownum会和它绑定的行一起根据order by的字段被排序(这样rownum的值和排序后的所在行数就不一致).

原因分析：

其实也很好理解，sql语句的执行顺序是 from > where > group by > having > select > distinct > union > order by, 那么在同一个查询中， rownum的值，在select之后就给了每一行，再order by

（If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause.

实例：

--不在同级查询中使用order by
SELECT ROWNUM, F_COMMODITY FROM WENG_TEST where rownum < 4; -- rownum能够反映所在行序  

结果：

--在同一级查询中使用order by：
select rownum, f_commodity from weng_test where rownum < 4 order by f_commodity;

结果：