Oracle中in超过1000的解决办法

直接上干货

<if test="parentIdList !=null and parentIdList.size() > 0">
      AND PARENT_ID IN
    <!--第一种:每个元素union all一次-->
    <foreach item="parentId" index="index" collection="parentIdList" open="(" separator=" union all " close=")">
         select '${parentId}' from dual
    </foreach>
      
    <!--第二种:每999个or一次-->
    <foreach collection="parentIdList" item="parentId" index="index" open="(" close=")" separator=",">
        <if test="(index % 999) == 998"> NULL ) 
           OR PARENT_ID IN (
        </if>#{parentId}
    </foreach>

    <!--第三种:每999个union all一次-->
    <foreach collection="parentIdList" item="parentId" index="index" open="(" close=")" separator=",">
      	<if test="(index % 999) == 998">NULL) UNION ALL SELECT  <include refid="base_column_list"/> 
      		FROM USER WHERE PARENT_ID IN(
     	</if>#{parentId}
    </foreach>

 </if>

注意: base_column_listsql块要和你主查询的字段一致