分页数据有重复的问题

前段时间，测试给了个bug ，前端显示时第一页和第二页有重复的数据，后台debug测试也有。把控制台打印的sql考到可视化工具跑一下，问题还是有。

然后再去除分页，发现问题就没有了。出现问题点在于分页。后来请教了我们公司的大佬。说可能是因为你那个字段值有重复的情况，最好加一个可以保证唯一性的字段对数据进行排序。

之前没改的sql

select * from(
        select
        <include refid="Base_Column_List"/>,
        case
        when TRAIN_STATUS=1
        then '1'
        when TRAIN_STATUS=2
        then '2'
        when TRAIN_STATUS=3
        then '3'
        when TRAIN_STATUS=5
        then '5'
        when (to_char(TRAIN_START_DATE, 'yyyy-mm-dd') || TRAIN_START_TIME) >
        to_char(sysdate, 'yyyy-mm-ddHH24:mi')
        then '4-1'
        when (to_char(TRAIN_END_DATE, 'yyyy-mm-dd') || TRAIN_END_TIME) >
        to_char(sysdate, 'yyyy-mm-ddHH24:mi')
        then '4-2'
        else '4-3'
        end as STATUS
        from TB_TRAINING_INFO
        order by
        case
        when STATUS='1'
        then 1
        when STATUS='3'
        then 2
        when STATUS='4-1'
        then 3
        when STATUS='4-2'
        then 4
        when STATUS='4-3'
        then 5
        when STATUS='2'
        then 6
        else 7
        end asc
        )
        <where>
            <if test="trainName!=null and trainName!=''">
                and TRAIN_NAME like '%' || #{trainName,jdbcType=OTHER} ||'%'
            </if>
            <if test="originatorName!=null and originatorName!=''">
                and ORIGINATOR_NAME like '%' || #{originatorName,jdbcType=OTHER} || '%'
            </if>
            <if test="originatorOrgName!=null and originatorOrgName!=''">
                and ORIGINATOR_ORG_NAME like '%' || #{originatorOrgName,jdbcType=OTHER} || '%'
            </if>
            <if test="trainStatus!=null and trainStatus!=''">
                and TRAIN_STATUS = #{trainStatus,jdbcType=CHAR}
            </if>
            <if test="originator!=null and originator!=''">
                and ORIGINATOR = #{originator,jdbcType=OTHER}
            </if>
            <if test="status!=null and status!=''">
                and STATUS = #{status,jdbcType=OTHER}
            </if>
            and delete_flag='0'
        </where>

从上面sql得到status他的值是有重复的，然后我再加一个字段问题解决了。附上修改sql

select * from(
        select
        <include refid="Base_Column_List"/>,
        case
        when TRAIN_STATUS=1
        then '1'
        when TRAIN_STATUS=2
        then '2'
        when TRAIN_STATUS=3
        then '3'
        when TRAIN_STATUS=5
        then '5'
        when (to_char(TRAIN_START_DATE, 'yyyy-mm-dd') || TRAIN_START_TIME) >
        to_char(sysdate, 'yyyy-mm-ddHH24:mi')
        then '4-1'
        when (to_char(TRAIN_END_DATE, 'yyyy-mm-dd') || TRAIN_END_TIME) >
        to_char(sysdate, 'yyyy-mm-ddHH24:mi')
        then '4-2'
        else '4-3'
        end as STATUS
        from TB_TRAINING_INFO
        order by
        case
        when STATUS='1'
        then 1
        when STATUS='3'
        then 2
        when STATUS='4-1'
        then 3
        when STATUS='4-2'
        then 4
        when STATUS='4-3'
        then 5
        when STATUS='2'
        then 6
        else 7
        end asc,UPDATE_DATE desc
        )
        <where>
            <if test="trainName!=null and trainName!=''">
                and TRAIN_NAME like '%' || #{trainName,jdbcType=OTHER} ||'%'
            </if>
            <if test="originatorName!=null and originatorName!=''">
                and ORIGINATOR_NAME like '%' || #{originatorName,jdbcType=OTHER} || '%'
            </if>
            <if test="originatorOrgName!=null and originatorOrgName!=''">
                and ORIGINATOR_ORG_NAME like '%' || #{originatorOrgName,jdbcType=OTHER} || '%'
            </if>
            <if test="trainStatus!=null and trainStatus!=''">
                and TRAIN_STATUS = #{trainStatus,jdbcType=CHAR}
            </if>
            <if test="originator!=null and originator!=''">
                and ORIGINATOR = #{originator,jdbcType=OTHER}
            </if>
            <if test="status!=null and status!=''">
                and STATUS = #{status,jdbcType=OTHER}
            </if>
            and delete_flag='0'
        </where>

总结：order by的时候最后在目标排序字段的基础上应该加上一个可以保证唯一性的字段对数据进行排序