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 desc
mybatis的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_ID
FROM 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_ID
FROM t1 t
WHERE t.DR=0 and t.VSTATUS>=1
) t
where rowIndex < 20 +1
;
此处的排序规则是:order by t.pk_id