关于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 <= #end# </isNotEmpty> <isNotEmpty prepend=" and " property="begin"> rn > #begin# </isNotEmpty> </select>