对于Oracle的rownum问题,很多资料都说不支持>,>=,=,between……and,只能用以上符号(<、& lt;=、!=),并非说用>,>=,=,between……and 时会提示SQL语法错误,而是经常是查不出一条记录来,还会出现似乎是莫名其妙的结果来,其实您只要理解好了这个rownum伪列的意义就不应该感到惊 奇,同样是伪列,rownum与rowid可有些不一样,下面以例子说明:
rownum背后的机制:
- Oracle executes your query.
- Oracle fetches the first row and calls it row number 1.
- Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.
- Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).
- Go to step 3.
具体流程如下:(帮助大家理解)
- 先按照where中除了rownum以外的其他条件筛选出结果并生成结果集 。
- 给第一步生成的结果集加上rownum伪列。
- 按照rownum的条件进一步限制,分两种情况:
3.1 如果第一条记录不符合rownum的限制条件,则把该记录丢弃,且下一条记录的rownum还是从1开始重新计数。
3.2 如果第一条记录符合rownum的限制条件,则把该记录筛选出来,且下一条记录的rownum从2开始,并重新进入第三步进行判断。
举个简单的例子,正确的写法:
/*
1.第一条记录的rownum为1,满足rownum <= 5 的限制条件,把第一条记录筛选出来
2.第二条记录的rownum为2,满足rownum <= 5 的限制条件,把第二条记录筛选出来
.
.
.
3.以此类推,筛选出前5条数据
*/
select * from sample where rownum <= 5
错误的写法:(条件永远不会成立)
/*
1.第一条记录的rownum为1,不满足rownum > 5 的限制条件,把第一条记录淘汰掉
2.第二条记录的rownum还是为1,不满足rownum > 5 的限制条件,把第二条记录淘汰掉
.
.
.
3.以此类推,条件永远不成立,无法筛选出数据
*/
select * from sample where rownum > 5
通过rownum选出某个区间的值
在实际开发中,我们经常会碰到这样的需求,“选出某个区间的值”,那这个需求怎么通过rownum实现呢?其实也很简单,我们只需要加多一层子查询即可。
举个简单的例子,比如:选出第2至第10之间的记录
select * from (
select rownum as seq_no, t.*
from sample t
where rownum <= 10
) t1 where t1.seq_no >= 2;
rownum和排序的关系
Oracle中的rownum的是在取数据的时候产生的序号,所以想对指定排序的数据去取指定的rowmun行数据时就必须注意了。
比如下面这个例子(需求:根据ID升序,然后选出前8条的记录):
由结果可以看出,根据ID升序出来的结果集中,rownum和id的顺序并不是相互对应的。
如果这个时候通过rownum <= 8 去取前8条记录,就会选出错误的数据。具体如下:
所以,同样的道理,针对排序情况,我们也需要加多一层子查询,具体如下: