oracle 数据库分页

示列
SELECT * FROM(SELECT A.*, ROWNUM RN FROM ( 
select a.user_id,a.username,a.name,a.password,a.mobile,a.status,a.dept_id 
from sys_back_user a ,sys_dept d 
where a.DEPT_ID = d.DEPT_ID(+)
order by a.user_id desc)   A WHERE ROWNUM <=10 ) 
WHERE RN >= 6

 mybatis 配置

<mapper namespace="com.taiji.agriculturalproductplatform.system.dao.UserDao">
    <sql id="prev">SELECT * FROM(SELECT A.*, ROWNUM RN FROM (</sql>
    <sql id="end">) <![CDATA[ A WHERE ROWNUM <=#{limit} ) WHERE RN >= #{offset}+1 ]]></sql>

 

<select id="listsn" resultType="com.taiji.agriculturalproductplatform.system.domain.UserDO">
    <if test="offset != null and limit != null">
        <include refid="prev" />
    </if>
    select a.user_id,a.username,a.name,a.password,a.mobile,a.status,a.dept_id from sys_back_user a ,sys_dept d
    <where>
        and a.DEPT_ID = d.DEPT_ID(+)
        <if test="sn != null and sn != ''"> and d.sn like #{sn}||'%' </if>
        <if test="userId != null and userId != ''"> and a.user_id = #{userId} </if>
    </where>
    <choose>
        <when test="sort != null and sort.trim() != ''">
            order by ${sort} ${order}
        </when>
        <otherwise>
            order by a.user_id desc
        </otherwise>
    </choose>
    <if test="offset != null and limit != null">
        <include refid="end" />
    </if>

</select>
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页