oracle分页查询,mybatis中oracle分页

分页sql

//方式一
 select * from 
         (select temporary1.*, Rownum as rowno from
                  ( select * from sys_user) temporary1
          ) temporary2
        where temporary2.rowno >= (1 - 1) *10 +1 and temporary2.rowno <= 1*10
        
//方式二   
SELECT * FROM (
       SELECT ROWNUM AS rowno, t.* FROM sys_user t
         WHERE t.year=2020
           AND ROWNUM <= 1*10) table_alias
 WHERE table_alias.rowno >= (1 - 1) *10 +1;

 //方式三
SELECT *
  FROM (SELECT tt.*, ROWNUM AS rowno
          FROM (  SELECT t.* FROM sys_user t
                   WHERE  t.year=2020
                   ORDER BY code DESC) tt
         WHERE ROWNUM <= 1*10) table_alias
 WHERE table_alias.rowno >= (1 - 1) *10 +1;

分页前缀

<!-- 分页sql前缀 -->
    <sql id="paginationPrefix">
         select * from (
         	select temporary1.*, Rownum as rowno from (
    </sql>

分页后缀

<sql id="paginationSuffix">
        ) temporary1
        ) temporary2
        where temporary2.rowno >= (#{pagination.page}-1)*#{pagination.limit}+1 and temporary2.rowno<=#{pagination.page}*#{pagination.limit}
    </sql>

使用前后缀分页

 <select id="tableSelect" resultMap="userResultMap">
        <include refid="paginationPrefix"/>
       select id, code, name, pwd, tel, email, addr, bdel, cdate, enabled, year from sys_user
        order by cdate desc
        <include refid="paginationSuffix"/>
    </select>