关于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>