mybatis中Oracle分页动态sql查询中使用ORDER BY遇到的问题

今天写一个列表页查询功能

mybatis代码如下

<select id="getRolePage" resultType="cn.security.Role">
        SELECT
        *
        FROM
        (
            SELECT
            TMP.*,
            ROWNUM ROW_ID
            FROM
            (
                SELECT
                  t1.*,
                  t2.ORG_NAME
                FROM
                    T_ROLE t1
                    LEFT JOIN T_ORG_INFO t2 ON t1.PROJECT_ID = t2.ID
                WHERE
                    t1.EFFICIENT = 1
                    <if test="params.LIKE_roleName != null and params.LIKE_roleName != ''">
                        AND ROLE_NAME LIKE CONCAT(CONCAT('%',#{params.LIKE_roleName}),'%')
                    </if>
                    <if test="params.LIKE_roleKey != null and params.LIKE_roleKey != ''">
                        AND ROLE_KEY LIKE CONCAT(CONCAT('%',#{params.LIKE_roleKey}),'%')
                    </if>
                AND t2.EFFICIENT = 1
                ORDER BY TMP.ROLE_SORT DESC
            ) TMP
            WHERE
            ROWNUM <![CDATA[ <= ]]>  #{params.endNum}
        )
        WHERE
            ROW_ID <![CDATA[ > ]]> #{params.startNum}
    </select>               

　表中有12条数据，分页查询10条，发现项目执行结果和直接执行SQL结果存在两条数据不一致。

原因：

ORDER BY 语句问题造成

解决方案：

把 ORDER BY语句移动到 ROWNUM 之后 

<select id="getRolePage" resultType="cn.security.Role">
        SELECT
        *
        FROM
        (
            SELECT
            TMP.*,
            ROWNUM ROW_ID
            FROM
            (
                SELECT
                  t1.*,
                  t2.ORG_NAME
                FROM
                    T_ROLE t1
                    LEFT JOIN T_ORG_INFO t2 ON t1.PROJECT_ID = t2.ID
                WHERE
                    t1.EFFICIENT = 1
                    <if test="params.LIKE_roleName != null and params.LIKE_roleName != ''">
                        AND ROLE_NAME LIKE CONCAT(CONCAT('%',#{params.LIKE_roleName}),'%')
                    </if>
                    <if test="params.LIKE_roleKey != null and params.LIKE_roleKey != ''">
                        AND ROLE_KEY LIKE CONCAT(CONCAT('%',#{params.LIKE_roleKey}),'%')
                    </if>
                AND t2.EFFICIENT = 1
            ) TMP
            WHERE
            ROWNUM <![CDATA[ <= ]]>  #{params.endNum}
　　　　　　　ORDER BY TMP.ROLE_SORT DESC
        )
        WHERE
            ROW_ID <![CDATA[ > ]]> #{params.startNum}
    </select>