Oracle分页

Oracle的rownum字段是个比较奇怪的字段。拿一张有26条记录的Test表来举例。
      select * from Test where rownum >=1;
     select * from Test where rownum >=2;
     select * from Test where rownum <= 10;
第一条sql查出了26条记录，第二条sql一条记录也没查出。第三条sql查出10条记录。

导致这个的原因是因为rownum是个虚拟的字段，它是在记录输出的时候逐步产生的。

     对第一条sql，第一条记录的rownum是1，满足条件被输出，因此第二条纪录的rownum就变成2，满足条件被输出，依此类推，就把所有纪录都查出来了。
     对于第二条sql，第一条记录的rownum是1,不满足条件没被输出，因此第二条记录的rownum还是1,没满足条件没被输出，依此类推，所有纪录都没能被查出来。
     对于第三条sql，第一条记录的rownum是1，满足条件被输出，因此第二条记录的rownum就递增为2，满足条件被输出，直到第11条及之后的所有记录的rownum变成了11，不满足条件没被输出。

所以要查询Test表第n条到第m条的记录，我们应该这样写：
//对已形成的rownum进行过滤
select * from (
      //先用一个select把待查sql包围起来，此时rownum已经形成
       select row_.*, rownum rownum_ from ( select * from Test ) row_
) where rownum_ <= m and rownum_ >= n;

 

 

评论

16 楼 gaohuier 2009-03-10  

有一个所有数据库都通用的分页sql，还有人清楚啊？

15 楼 week 2008-11-17  

qqkinger 写道

Oracle的rownum字段是个比较奇怪的字段。拿一张有26条记录的Test表来举例。 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select * from Test where rownum &gt;=1; &nbsp;&nbsp;&nbsp;&nbsp; select * from Test where rownum &gt;=2; &nbsp;&nbsp;&nbsp;&nbsp; select * from Test where rownum &lt;= 10; 第一条sql查出了26条记录，第二条sql一条记录也没查出。第三条sql查出10条记录。 导致这个的原因是因为rownum是个虚拟的字段，它是在记录输出的时候逐步产生的。 &nbsp;&nbsp;&nbsp;&nbsp; 对第一条sql，第一条记录的rownum是1，满足条件被输出，因此第二条纪录的rownum就变成2，满足条件被输出，依此类推，就把所有纪录都查出来了。 &nbsp;&nbsp;&nbsp;&nbsp; 对于第二条sql，第一条记录的rownum是1,不满足条件没被输出，因此第二条记录的rownum还是1,没满足条件没被输出，依此类推，所有纪录都没能被查出来。 &nbsp;&nbsp;&nbsp;&nbsp; 对于第三条sql，第一条记录的rownum是1，满足条件被输出，因此第二条记录的rownum就递增为2，满足条件被输出，直到第11条及之后的所有记录的rownum变成了11，不满足条件没被输出。 所以要查询Test表第n条到第m条的记录，我们应该这样写： //对已形成的rownum进行过滤 select * from ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //先用一个select把待查sql包围起来，此时rownum已经形成 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select row_.*, rownum rownum_ from ( select * from Test ) row_ ) where rownum_ &lt;= m and rownum_ &gt;= n;

讲的很透彻，很清晰，我以前只知道orcale的表应该用至少一条子查询来实现，今天才明白其中的道理！谢谢提供！

14 楼 yzgname 2008-11-15  

今天的一道笔试题就是这样的，可惜答错了。学习！

13 楼 usee888 2008-11-13  

来一条mysql
select * from table limit 开始的记录数，条数;

12 楼 wizard23 2008-11-13  

受教了。。。学习

11 楼 week 2008-11-13  

我认为没有那么复杂，就一句简单的sql没。
select * from (
select A.*,rownum rn from table_name where rownum<=max
)
where rn>=min
或
select * from (
select A.*,rownum rn from table_name
)
where rn between min and max

10 楼 longyouzhi 2008-09-02  

  select *  from (    select  a.* , rownum rownum_ from (  select *  from export_book ) a
           ) b  where b.rownum_ >=10 and   b.rownum_ <= 20
简单的分页还是可以的 ，数据量大了，难了 ，
还是用Hibernate的分页 ，容易些。

9 楼 astronomy 2008-08-29  

用分析函数1万条之后翻最后一页时巨慢无比，不用分析函数1万条之后每翻一页都慢的要死
，酌情选择！

8 楼 qqkinger 2008-08-20  

Oracle的rownum字段是个比较奇怪的字段。拿一张有26条记录的Test表来举例。
      select * from Test where rownum >=1;
     select * from Test where rownum >=2;
     select * from Test where rownum <= 10;
第一条sql查出了26条记录，第二条sql一条记录也没查出。第三条sql查出10条记录。

导致这个的原因是因为rownum是个虚拟的字段，它是在记录输出的时候逐步产生的。

     对第一条sql，第一条记录的rownum是1，满足条件被输出，因此第二条纪录的rownum就变成2，满足条件被输出，依此类推，就把所有纪录都查出来了。
     对于第二条sql，第一条记录的rownum是1,不满足条件没被输出，因此第二条记录的rownum还是1,没满足条件没被输出，依此类推，所有纪录都没能被查出来。
     对于第三条sql，第一条记录的rownum是1，满足条件被输出，因此第二条记录的rownum就递增为2，满足条件被输出，直到第11条及之后的所有记录的rownum变成了11，不满足条件没被输出。

所以要查询Test表第n条到第m条的记录，我们应该这样写：
//对已形成的rownum进行过滤
select * from (
      //先用一个select把待查sql包围起来，此时rownum已经形成
       select row_.*, rownum rownum_ from ( select * from Test ) row_
) where rownum_ <= m and rownum_ >= n;

7 楼 han0332217 2008-06-30  

oracle的分页可以到hibernate的源码中去找呀，很不错的

6 楼 lianry 2008-06-25  

分析函数的确很好用啊

5 楼 aids198311 2008-06-16  

这样改进如何?
select name,classname from ( select rownum rn,name,classname from student where rn <=m) st where st.rn>=n
如果要加ORDER BY的话,要再加一层嵌套!

4 楼 sky000 2008-06-15  

说的有点不严谨，rowid不是伪列，rownum是伪列，rowid是oracle中用于标识出每一行记录的位置的标识，不好意思。

3 楼 sky000 2008-06-15  

分析函数在9i下面是无法走stop key的执行计划的，只有在10g的时候才行

在9i下面的oracle分页查询应该如下：
select * from (
    select t.*,rownum rownum_ from (
        select a.* from t a order by a.order_column
    ) t where rownum < page_end
) t where t.rownum_ > page_start


在这样的sql语句中，oracle会走stop key的执行计划，也就是说，如果你要查询10到20的记录，那么oracle需要读取1到20的所有记录，但是20之后的记录不需要读取。

回答楼上的，如果你要读取一百万和一百万零一千条，那么oracle需要读取前一百万零一千条的所有记录。所以，这样的分页sql适用于主要读取前面的一些页面的应用，如果每一页的读取概率相同的话，那建议用另外一种分页方法。如果有排序要求，将排序要求的字段建立索引，通过索引完成排序和分页，并且取得你所需要的那一页的rowid伪列，然后再通过rowid来读取那一页的记录，具体sql就不再写出来了。

2 楼 cammette 2008-06-11  

select * from (
      //先用一个select把待查sql包围起来，此时rownum已经形成
       select row_.*, rownum rownum_ from ( select * from Test ) row_
) where rownum_ <= m and rownum_ >= n;


假如Test很大，我想从一百万到一百万零一千条，速度怎么样？

1 楼 armorking 2008-06-03  

用分析函数吧

select X.*
from 
(
    select 
        row_number() over(order by YOUR_ORDER_ITEMS) as ROW_NO,
        TEST.*
    from TEST
) X
where X.ROW_NO <= m 
    and X.ROW_NO >= n;