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;
结果: