关于oracle的原生sql排序分页

用惯了mybatis和SpringJPA自带的分页API，今天维护一个老项目，需要使用oracle的原生sql实现分页。基本思想来自这篇文章：

http://blog.csdn.net/honey_potter/article/details/53014284

在进行排序并取分页时，主要利用了rownum的属性。这篇文章的方法有个坑，会导致取出来的前五个并不是理想排序的前五个

本意是想取最新的五条记录，但是以下SQL查出来不是按最新时间排序的五条记录。

错误写法一：

select * from            ( select rownum as rn,                id,           invuser_id as invuserId,           content,           status,           mobile,           user_type  as userType,           create_by  as createBy,           create_dt  as createDt           from jqy.jqy_t_group_commentary           where (invuser_id =50105           and COMMENT_TYPE = '0'            and status='1'  )                or                  (invuser_id =50105  and  create_by = 20408397 and status != '3')                  order by create_dt desc            ) where 1=1                  and              rn > 0             and              rn <=5

错误的原因在于，第一次select出来的rn 是排序之前的，并非排序之后的rownum。

错误写法二：

      select rownum as rn, aa.* from             (select id,           invuser_id as invuserId,           content,           status,           mobile,           user_type  as userType,           create_by  as createBy,           create_dt  as createDt           from jqy.jqy_t_group_commentary           where (invuser_id =50105           and COMMENT_TYPE = '0'            and status='1'  )               or                   (invuser_id = 50105  and  create_by = 20408397 and status != '3')             order by create_dt desc             ) aa             where 1=1             and       rn >0       and        rn <=5;

这种写法会报错：
ORA-00904: “RN”: 标识符无效
00904. 00000 - “%s: invalid identifier”
*Cause:
*Action:
行 22 列 8 出错

错误写法三：

select rownum as rn, aa.* from             (select id,           invuser_id as invuserId,           content,           status,           mobile,           user_type  as userType,           create_by  as createBy,           create_dt  as createDt           from jqy.jqy_t_group_commentary           where (invuser_id =50105           and COMMENT_TYPE = '0'            and status='1'  )               or                   (invuser_id = 50105  and  create_by = 20408397 and status != '3')             order by create_dt desc             ) aa             where 1=1             and       rownum >=0       and        rownum <=5;

第三种写法 在查询第一页是没问题 也就是 rownum >=0， rownum >0 ，rownum >=1 时没问题 但是一旦rownum >1 以后 就查不出任何内容了。原因在这两文章有解释：
http://blog.csdn.net/jquerys/article/details/52432770

http://www.360doc.com/content/13/1123/18/14120004_331598212.shtml

rownum是查询过后才按顺序排的，假如你的条件是rownum>1;那么返回数据的第一条（rownum是1）就不符合要求了，然后第二条数据变成了现在的第一条，结果这一条rownum又变成1了又不符合要求了，以此类推 就没有返回结果。
如果想分页的话 是把rownum作为子表的一个字段（起个别名）如
select table1.id
from (select a1.id as id ,a1.rownum as rnum from a1) table1
where table1.rnum>1

因此，最终修改如下：

 select * from     (       select rownum as rn, aa.* from             (select id,           invuser_id as invuserId,           content,           status,           mobile,           user_type  as userType,           create_by  as createBy,           create_dt  as createDt           from jqy.jqy_t_group_commentary           where (invuser_id =50105           and COMMENT_TYPE = '0'            and status='1'  )               or                   (invuser_id = 50105  and  create_by = 20408397 and status != '3')             order by create_dt desc             ) aa             where        rownum <=10       ) bb       where bb.rn >=5;

正确完整的：

 <select id="webServiceNewGroup2.getCommentary" parameterClass="java.util.HashMap" resultClass="webServiceNewGroup2.groupCommentaryTo">        select rn , aaa.* from (        select rownum as rn, aa.* from             (select id,           invuser_id as invuserId,           content,           status,           mobile,           user_type  as userType,           create_by  as createBy,           create_dt  as createDt           from jqy.jqy_t_group_commentary           where (invuser_id = #invuserId#           and COMMENT_TYPE = #commentType#             <isNotEmpty prepend=" and " property="userType">                user_type = #userType#              </isNotEmpty>            and status='1'  )               <isNotEmpty prepend=" or " property="customerno">                  (invuser_id = #invuserId#  and  create_by = #customerno# and status != '3')             </isNotEmpty>             order by create_dt desc             ) aa)aaa             where 1=1             <isNotEmpty prepend=" and " property="end">             rn &lt;= #end#              </isNotEmpty>             <isNotEmpty prepend=" and " property="begin">             rn &gt; #begin#              </isNotEmpty>    </select>