//方式一
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前缀 --><sqlid="paginationPrefix">
select * from (
select temporary1.*, Rownum as rowno from (
</sql>
分页后缀
<sqlid="paginationSuffix">
) temporary1
) temporary2
where temporary2.rowno >= (#{pagination.page}-1)*#{pagination.limit}+1 and temporary2.rowno<=#{pagination.page}*#{pagination.limit}
</sql>
使用前后缀分页
<selectid="tableSelect"resultMap="userResultMap"><includerefid="paginationPrefix"/>
select id, code, name, pwd, tel, email, addr, bdel, cdate, enabled, year from sys_user
order by cdate desc
<includerefid="paginationSuffix"/></select>