分页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 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>
