sql使用查询in报错超出一千条处理

原创已于 2022-04-21 16:52:22 修改 · 1.9k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#java

于 2022-04-21 10:49:25 首次发布

方法1
直接在业务逻辑上处理解决超出一千条数据的问题

方法2
SQL解决
中间一条优化了使用iftest进行优化拼接

 <select id="queryHealthNotReport" resultType="org.jeecg.modules.epidemic.entity.NotReportUser">
        select s.id, s.username, s.realname, s.phone ,s.sex,s.user_identity from sys_user s, sys_user_depart d where s.id=d.user_id
        and s.status=1
        <if test="notReportUser.username != null">
            and s.username= #{notReportUser.username}
        </if>

        <if test="notReportUser.userIdentity != null">
            and  s.user_identity = #{notReportUser.userIdentity}
        </if>
        <if test="notReportUser.realname != null">
            and  s.realname like concat('%',#{notReportUser.realname},'%')
        </if>
        and d.dep_id in
        <foreach collection="deptIds" open="(" close=")" separator="," item="deptId" index="index">
            <if test="(index % 999) == 998">
             NULL) OR d.dep_id IN(
            </if>
         #{deptId}
        </foreach>
        <if test="reportedUserIds != null and reportedUserIds.size > 0">
            and s.username not in
            <foreach collection="reportedUserIds" open="(" close=")" separator="," item="reportedUserId">
                #{reportedUserId}
            </foreach>
        </if>
    </select>