典型的分页查询SQL

一、带排序的分页查询

这里以修改DB中的修改记录顺序排列为例，查找第9990到10000条之间的记录：

select t2.*
  from (select rownum rnum, t1.*
          from (select * from ABC p order by p.gmt_modified) t1
         where rownum <= 10000) t2
 where t2.rnum >= 9990

这里需要注意一下几点：

1. 这是典型的3层查询结构，sql执行的顺序是由里层到外层依次执行。
2. 第1层查询只做排序
3. 第2层只限定查询范围的上限
4. 第3层限定查询范围的下限

 

二、不带排序的分页查询

这里同样以修改DB中的修改记录顺序排列为例，查找第9990到10000条之间的记录（通上例完全一样，只要去掉其中第一层的排序就ok了）：

select *
  from (select p.*, rownum rnum from ABC p where rownum <= 10000) t1
 where t1.rnum >= 9990

 

 三、需要级联两张表的排序分页

样例sql如下：

SELECT a.*, b.receive_fee
  FROM (SELECT t2.*
          FROM (SELECT t1.*, ROWNUM rnum
                  FROM (SELECT t.*
                          FROM beyond_trade_base t
                         WHERE seller_account = :1
                           AND gmt_create >= TO_DATE(:2, 'yyyy-mm-dd')
                           AND gmt_create < TO_DATE(:3, 'yyyy-mm-dd')
                         ORDER BY gmt_create DESC) t1
                 WHERE ROWNUM <= :4) t2
         WHERE rnum >= :5) a,
       beyond_trade_process b
 WHERE a.trade_no = b.trade_no(+)

上述写法比较高效，因为这个4层查询中，最里面的3层查询完全是排序的分页查询，两张表的关联值发生在了最外层（也就是最后一层）。

但是，悲剧的是这种高效的写法未必任何场景都可以适用。如果需要在最内层的条件筛选的时候就加上beyond_trade_process的某些字段值作为筛选条件，显然上面的写法就不行了。如果是那样的话，就需要适当的将

a.trade_no = b.trade_no(+) 的条件往内层查询移动了。当然这里这个条件越靠近外层性能会越好！因为表间级联还是很耗费性能的。