分页数据有重复的问题

前段时间,测试给了个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的时候最后在目标排序字段的基础上应该加上一个可以保证唯一性的字段对数据进行排序

转载于:https://www.cnblogs.com/jiangweichao/p/11163515.html

  • 1
    点赞
  • 0
    评论
  • 0
    收藏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值