【ORACLE语法】ORACLE 中ROWNUM用法,ORACLE自定义分页怎么写

ROWNUM 查询的时候，一定要查询出第一行，否者查不出任何记录，所以做分页查询的时候必须做子查询。

sql举例如下

select *
from (select ROWNUM as rn, t.* from H_CAPITAL_CITY_IDCARD t where ROWNUM <= 3) u
where u.rn > 2 and rn <= 3;

mybatis中的写法如下

<select id="getCapitalCityIDCard" resultType="java.lang.String">
        <![CDATA[
            select IDCARD
            from (select ROWNUM as rn, t.IDCARD
                  from H_CAPITAL_CITY_IDCARD t
                  where IDCARD_REL is null and ROWNUM <= #{endRowNum}) u
            where u.rn > #{startRowNum}
              and u.rn <= #{endRowNum}
        ]]>
</select>

参考文献：

https://blog.youkuaiyun.com/qq_39196949/article/details/84379874