sql in查询元素超过1000条的解决方案_Oracle

sql in查询元素超过1000条的解决方案

2023-03-05 15:53梅西库里RNG Oracle

在oracle数据库中sql使用in时,如果in的能数超过1000就会出问题,下面这篇文章主要给大家介绍了关于sql in查询元素超过1000条的解决方案,文中通过实例代码介绍的非常详细,需要的朋友可以参考下

一、场景描述

查询机构下的人员，要同时查询出该机构及其所有下级机构的人员；比如查询北京市，不仅要查询出归属于北京市的人员，还要查询归属于出朝阳区、西城区、海淀区等等的人员。

对于这个需求，有以下几种解决思路：

1、之前的系统，他们的机构编码本身有关联，比如北京编码是001的话，昌平区的编码就是0012；这样的话，他们查询可以用like。这种方法的问题是like查询太慢，尤其在数据量大的时候，即使对编码加唯一索引，也只有查最根部机构时可以走索引（因为只有此时才符合最左前缀）。

2、我们的系统没有机构编码，唯一标识是雪花算法生成的19位id，没有关联；机构关系是通过一张关联表组合的起来的（适用于多业务系统，多机构树的情况）。这种情况，可以通过条件从关系表中查出所有下级机构id，然后查询时用in查询；为了方便查询，我们还建了一张机构关系冗余表。
但是这种方法也有一个问题，就是Oracle数据库in查询中元素，必须在1000以内。

二、解决方案

1、方案一：核心思路是，将集合拆分，使用or 连接。

				?

									select * from A where id in (1, 2, …, 1000) or id in (1001, …, 1999)

用mybatis的话就是这样

				?

									select * from test_1

									<where>

									 <if test="list != null and list.size > 0">

									    (id IN

									    <!-- 处理in的集合超过1000条时Oracle不支持的情况 -->

									    <trim suffixOverrides=" OR id IN()">

									        <foreach collection="list " item="Id" index="index" open="(" close=")">

									            <if test="index != 0">

									                <choose>

									                    <when test="index % 1000 == 999">) OR id IN (</when>

									                    <otherwise>,</otherwise>

									                </choose>

									            </if>

									            #{Id}

									        </foreach>

									    </trim>

									    )

									 </if>