oracle分页小谈

今天做项目时要实现分页功能，以前只在mysql上弄过，oracle倒没试过，但知道有这样一个rownum这个东西。

但这个东西也不是那么容易用的，还是有蛮多地方要注意的。它不是物理上存在的一列，而是oracle自己在结果集中进行添加的。

首先我们来看一下我们的表结构先：

我们新建一个表：

  也就一个字段ID而已，这方便我们直接地看到结果。

我们先随便插入几条数据：

先弄进去5条吧。

我们直接来一个：

select rownum,id from test;

  我们看到结果：

很高兴吧，都是一模一样的，ronwum和ID是一样的，方便我们看。

 

但结果看到的结果可能就会让你郁闷啦。

 

继续下来，我们删除几条数据再插入几条：

看到结果，我们是删除了后面两条，4，5这两条记录，而插入了8，9这两条记录。我们再来执行查询：

select rownum,id from test;

我们看到ID为8，9的已经取代了之前的4，5得到了rownum为4，5。这个说明了什么，说明了rownum并不是物理存在的，如果是物理存在的那么它肯定会随着4,5的删除而把rownum的4，5都删除了，但它并没有，而是把新插入的记录的rownum作为此值，这说明rownum肯定只是一个逻辑上的列，它有一个专门的名称——伪列。

 

下面我们继续插入数据，方便做下面的实验：

 

如果我们需要取得前5条记录，我们会怎么做呢？我们看到前面的rownum是根据我们查出来的结果来进行赋值的，那么我们就明白了，也许可以这样：

select rownum,id from test where rownum <= 5 order by id;

  但很杯具的是，我们错了，看看结果：

为什么错呢？

  原因就是rownum会在我们查询出来结果还没排序前就进行编号。由于是这个原因，我们只要加个字查询就OK啦。

select rownum,id from (select * from test order by id) where rownum <= 5;

  我们看看结果：

现在没问题了，已经按照rownum来排序了，也就是实现了我们的要求，查出前5个。

 

不要高兴的太早，查前5个没问题，那中间的记录呢，第2到5个呢，或者大于5呢。我们来看看：

 select rownum,id from (select * from test order by id) where rownum >=1 and  rownum <= 4;

   看看结果：

  这个有数据，而且正常，很好。

但不要高兴，我们分页一般不会只要第一条开始吧，如果要中间呢？我们看看：

select rownum,id from (select * from test order by id) where rownum >=1 and  rownum <= 4;

  这次不要大跌眼睛了：

  杯具了吧。

为什么我们刚才拿到的>=1时会有呢，而现在>=2没有呢？

 

原因就是oracle在赋值rownum的时候会从1开始赋值，而当我们进行rownum >=1时，由于=1这个条件是成立的，所以它可以继续取下一条rownum，继续赋值到2，接连赋值下去。

而当我们用rownum >=2时，由于=2这个条件是不成立的，因为当取到第一条rownum=1时，会把它丢弃，而当取到下一条时，rownum还是为1，还是不满足，一直这样的循环，最后的结果就是没数据可查出了。

 

但我们分页确实要这样要进行，怎么办呢？

其实也简单，还是子查询的方式，不是直接用rownum那我们把子查询中的rownum命一个别名然后通过它来限定不就OK了。

我们看看：

select rn,id from (select rownum as rn,id from (select * from test order by id) ) where rn >=2 and rn <= 4;

  也许很多朋友看不明白是什么意思，我们先看看结果：

很正常，没问题吧。

但为什么这样就没问题呢？其实我们在上面的子查询中，直接把rownum用rn作为别名，它就被完全记录下来了，这里我们用它来作限定条件已经不关原来的rownum的事了。之所以要用两个子查询是因为第一个排序需要作为子查询才可以取到正确的rownum，才可以定义别名。

相信看到这，大家都应该知道怎么用oracle来实现分页啦。

 

这几天工作忙啊，BlogWriter暂时进展不大，努力啊。

 

评论

7 楼 sjzcmlt 2016-07-04  

大神，供参考：
可在第一次查询的时候就限制最大数据
SELECT * FROM (SELECT ROWNUM r,t1.* From 表名称 t1 where rownum < M + N) t2
where t2.r >= M

6 楼 sjzcmlt 2016-07-04  

大神 ，膜拜

5 楼 cxshun 2011-06-20  

yznxing 写道

如果用这种方式来查询，那就被坑死了。。

用来理解rownum倒是没问题。


做个分页你要每次都全表扫描。太坑人了吧。

select rn,id from (select rownum as rn,id from (select * from test order by id) ) where rn >=2 and rn <= 4; 


再写仔细点，到底该如何完整的分页吧！

每次分页都要全表扫描是比较坑人，但貌似对于oracle来讲，似乎没其他比较好的方法，如果你有，不妨分享一下。大家也一起讨论。

4 楼 lsj8080 2011-06-17  

yznxing 请教一下，怎么写分页才是好的算法呢？

3 楼 yznxing 2011-06-17  

yznxing 写道

如果用这种方式来查询，那就被坑死了。。

用来理解rownum倒是没问题。


做个分页你要每次都全表扫描。太坑人了吧。

select rn,id from (select rownum as rn,id from (select * from test order by id) ) where rn >=2 and rn <= 4; 


再写仔细点，到底该如何完整的分页吧！

个人理解，不一定对。

2 楼 yznxing 2011-06-17  

如果用这种方式来查询，那就被坑死了。。

用来理解rownum倒是没问题。


做个分页你要每次都全表扫描。太坑人了吧。

select rn,id from (select rownum as rn,id from (select * from test order by id) ) where rn >=2 and rn <= 4; 


再写仔细点，到底该如何完整的分页吧！

1 楼 :倾诉 2011-06-17  

以前rownum总没去仔细看它,现在总算明白了,多谢lz啊.