oracle in 超过1000解决方案

oracle in 超过1000解决方案

1.with as table建立临时表子查询

with t as (
    select '1' as id from dual
    union all
    select '2' as id from dual
    ·····
)
select i.*
from table1 i
where i.id in (
    select t.id from t
)

2.使用or

方式1：

select * from table where id in (1, 2, ..., 1000) or id in(1001, ....., 1999)

后台使用

    public String createSql(String col ,List<Integer> umIds) {
        StringBuffer sb = new StringBuffer();
        sb.append(col + " in(");
        int size = umIds.size();
        int index = 0;
        for (int i = 0; i < size; i++) {
            index++;
            if (index == 999) {
                index = 0;
                sb.append(")");
                sb.append("  or " + col + " in ( ");
            }
            if (index + 1 == 999 || size - 1 == i) {
                sb.append("" + umIds.get(i) + "");
            } else {
                sb.append("" + umIds.get(i) + ",");
            }
        }
        sb.append(")");
        return sb.toString();
    }

方式2：

select * from table where id =1 or id =2 or·····

超过1000报错：

<isNotEmpty property="ids" prepend="and">
    t.id in
    <iterate open="(" close=")" conjunction="," property="ids">
        #ids[]#
    </iterate>
</isNotEmpty>

更正：

<isNotEmpty property="ids" prepend="and">
    <iterate open="(" close=")" conjunction=" or " property="ids">
        t.cltno = #ids[]#
    </iterate>
</isNotEmpty>

前面是比较老的ibatis写法，在mybatis3里面你可以这样做

 <if test="scope.ids!= null and scope.ids.size() > 0">
                and (D.PAY_CLT_NO in
                <foreach collection="scope.ids" open="(" close=")" separator="," item="id" index="idx">
                    <if test="idx != 0 and idx % 999 == 0 and idx != scope.ids.size() - 1">
                        #{id}) or D.PAY_CLT_NO in (#{id}
                    </if>
                    <if test="idx == 0 or (idx % 999 != 0 or idx == scope.ids.size() - 1)">
                        #{id}
                    </if>
                </foreach>
                )
            </if>

3.后台拼接查询，返回后重组list

传给后台参数LIST数组大小超过1000，截取查询两次，最后addAll()；