使用mybatis分页查询oracle数据库时，同时使用了order by和rownum，结果出现了分页查询失败，多个分页查询结果相同的情况

oracle版本11.2.0.4.0

Mapper

IPage<MsBp> findByPageno(IPage page, @Param("msBp") MsBp msBp);

XML

    <select id="findByPageno" resultMap="RM_MsBp">
        SELECT
        <include refid="MsBp_columns"/>
        FROM Ms_Bp where 1=1
        <include refid="MsBp_findPage_where2"/>
        order by APPLY_STS,UP_DATE desc
    </select>

SQL

SELECT *
  FROM (SELECT TMP.*, ROWNUM ROW_ID
          FROM (SELECT ID,
                       APPLY_STS,
                       UP_DATE
                  FROM Ms_Bp
                 where 1 = 1
                 order by APPLY_STS, UP_DATE desc) TMP
         WHERE ROWNUM <= 20)
 WHERE ROW_ID > 10;

解决办法

order by后面加上唯一性字段（类似主键id）

SELECT *
  FROM (SELECT TMP.*, ROWNUM ROW_ID
          FROM (SELECT ID,
                       APPLY_STS,
                       UP_DATE
                  FROM Ms_Bp
                 where 1 = 1
                 order by APPLY_STS, UP_DATE desc, ID) TMP
         WHERE ROWNUM <= 20)
 WHERE ROW_ID > 10;

参考地址

1.https://www.cnblogs.com/lwlxqlccc/p/8676045.html
2.https://blog.csdn.net/wang286480403/article/details/84509472