oracle

sql 分页

Posted by Vincent on May 8, 2010

SQL 分页

  1. 常用分页，其中的 第二层的 rownum <= 20可以控制最内层查询过程中的最大记录数

       select * from (select t1.*, rownum rn
   	  from (select *
         from table_buyer
        where buyer_status = 2
        order by gmt_create desc) t1
  where rownum <= 20) 
where rn >= 10;

  2. 分页用between实现
    不推荐，因为第三层不能推到最内层控制查询条数，效率低

    select * from (select t.*, rownum rn
    from (select *
          from table_buyer
         where buyer_status = 2
         order by gmt_create desc) t) 
         where rn between 10 and 20;

  3. 效率最高
    用rowid 分页，大数据量翻页常用写法，常用于单表，并且所有查询和排序字段都在索引中的情况

    select * from (select rid
  from (select t.rid, rownum linenum
          from (select rowid rid
                  from table_buyer
                 where buyer_status = 2
                 order by gmt_create desc) t
         where rownum <= 20)
 where linenum >= 10) t1, table_buyer t2   
 where t1.rid = t2.rowid;

…

This work is licensed under a CC A-S 4.0 International License.