第一種:
select b.*
from (select rownum as rid, a.* from om_cm_book_hotword a) b
where b.rid <= 10
and b.rid > 5;
第二種:
select c.*
from (select rownum as rid, b.*
from (select a.* from om_cm_book_hotword a) b
where rownum <= 10) c
where rid > 5
因為rownum 的位置不同，第二種相對于第一種增加了一層嵌套查詢。
舉例:
select *
from (select rownum as rid, b.*
from (select *
from T_MEMBERIMPORT a
where a.importdate <=
to_date(#enddate#, 'yyyy/MM/dd hh24:mi:ss')
and a.importdate >=
to_date(#begindate#, 'yyyy/MM/dd hh24:mi:ss')) b
where rownum <= #PageNow# * 10) c
where rid > (#PageNow# - 1) * 10
<select id="queryVvip" parameterClass="java.util.Map" resultMap="businesstravel.VvipResult">
select *
from (select rownum as rid, b.*
from (select * from t_vvip
<isNotEmpty prepend="where" property="name">
name=#name#
</isNotEmpty>
<isNotEmpty prepend="and" property="companyname">
companyname=#companyname#
</isNotEmpty>
order by id ) b
where #PageNow# * 10>= rownum ) c
where rid > (#PageNow# - 1) * 10
</select>