本文共 1384 字,大约阅读时间需要 4 分钟。
mybatis配置and rownum< minus 查询第几行到第几行数据的sql原型和mybatis原型:
sql原型如下:
select
t.pk_id as wq FROM t1t where dr=0 and rownum<10 minus select t.pk_id as 12 FROM t1t where dr=0 and rownum>2 order by JGqwMC descmybatis的mapper配置文件原型如下:
<select id="exportCurrentPage" resultMap="CorrectEndExcelMap"
parameterType="java.util.Map"> <if test="searchText != null"> <bind name="searchTextLike" value="'%' + _parameter.searchText + '%'" /> </if> <if test="xm != null"> <bind name="xmLike" value="'%' + _parameter.xm + '%'" /> </if> select t.pk_id as JGMC FROM t1 t <where> <trim prefixOverrides="and"> t.DR=0 <![CDATA[ and rownum<10 minus ]]> </trim> </where> select t.pk_id as JGMC FROM t1 t <where> <trim prefixOverrides="and"> t.DR=0 and rownum>2 </trim> </where> order by JGMC desc </select>注意:如果需要配置自定义的排序,需要如下设置,因为oracle的rownumber是不会变化的(此处的排序规则是:order by t.pk_id):
select * from(
select ROW_NUMBER() over(order by t.pk_id) as rowIndex , rownum,t.PK_ID FROM t1t WHERE t.DR=0 and t.VSTATUS>=1 ) t where rowIndex<= 40 and rowIndex>=20或者利用minus写了更为复杂的写法(不推荐)
select * from(
select ROW_NUMBER() over(order by t.pk_id) as rowIndex , rownum,t.PK_IDFROM t1 t
WHERE t.DR=0 and t.VSTATUS>=1 ) t where rowIndex<= 40 minus select * from( select ROW_NUMBER() over(order by t.pk_id) as rowIndex , rownum, t.PK_IDFROM t1 t
WHERE t.DR=0 and t.VSTATUS>=1 ) t where rowIndex < 20 +1 ;此处的排序规则是:order by t.pk_id
转载地址:http://hsmxi.baihongyu.com/