一、場景描述

查詢機(jī)構(gòu)下的人員，要同時查詢出該機(jī)構(gòu)及其所有下級機(jī)構(gòu)的人員；比如查詢北京市，不僅要查詢出歸屬于北京市的人員，還要查詢歸屬于出朝陽區(qū)、西城區(qū)、海淀區(qū)等等的人員。

對于這個需求，有以下幾種解決思路：

1、之前的系統(tǒng)，他們的機(jī)構(gòu)編碼本身有關(guān)聯(lián)，比如北京編碼是001的話，昌平區(qū)的編碼就是0012；這樣的話，他們查詢可以用like。這種方法的問題是like查詢太慢，尤其在數(shù)據(jù)量大的時候，即使對編碼加唯一索引，也只有查最根部機(jī)構(gòu)時可以走索引（因為只有此時才符合最左前綴）。

2、我們的系統(tǒng)沒有機(jī)構(gòu)編碼，唯一標(biāo)識是雪花算法生成的19位id，沒有關(guān)聯(lián)；機(jī)構(gòu)關(guān)系是通過一張關(guān)聯(lián)表組合的起來的（適用于多業(yè)務(wù)系統(tǒng)，多機(jī)構(gòu)樹的情況）。這種情況，可以通過條件從關(guān)系表中查出所有下級機(jī)構(gòu)id，然后查詢時用in查詢；為了方便查詢，我們還建了一張機(jī)構(gòu)關(guān)系冗余表。
但是這種方法也有一個問題，就是Oracle數(shù)據(jù)庫in查詢中元素，必須在1000以內(nèi)。

二、解決方案

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>

但是這種方法不好用，實測中3萬條左右的機(jī)構(gòu)，用這種查詢查了好久都沒出來，感覺數(shù)據(jù)庫都要奔潰了。

2、方案二：用子查詢（臨時表）+關(guān)聯(lián)查詢

一般來說，超過1000多條的數(shù)據(jù)，肯定不是用戶填寫的，而是從其他地方查詢出來的；我們可以將這些數(shù)據(jù)放到一個臨時表中（用子查詢實現(xiàn)），然后用內(nèi)連接關(guān)聯(lián)查詢。

該查詢中，機(jī)構(gòu)關(guān)系冗余表SEP_SYSTEM_ORG_RELATION中有20多萬條數(shù)據(jù)，但子查詢走了我們創(chuàng)建的組合索引；整個查詢只用了0.367秒

總結(jié)

到此這篇關(guān)于sql in查詢元素超過1000條的解決方案的文章就介紹到這了,更多相關(guān)sql in查詢元素超過1000條內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！