Oracle的in查询

In查询

SELECT
    ID,
    ASSAY_ITEM_NAME
FROM
    DIC_ASSAY_ITEM
WHERE
    ID IN (
        SELECT DISTINCT
            ASSAY_ITEM_ID
        FROM
            DIC_ASSAY_ITEM_METHOD
    )
或者
Mapper
List<PersonInfoExt> selectByDeptmentCodes(@Param("deptmentCodes")String[] deptmentCodes);
Mapper.xml
SELECT
<include refid="Base_Column_List"/>
FROM V_PERSON ue
WHERE 1=1
<if test="deptmentCodes !=null and deptmentCodes !=''">
    and ue.dept_id in
    <foreach collection="deptmentCodes" item="dept_code" separator=","
             open="(" close=")">
        #{dept_code}
    </foreach>
</if>
order by ue.name

嵌套使用

SELECT
    samplemodel.*
FROM
    DIC_SAMPLE_BIZ_MODEL samplemodel
WHERE
    ID IN (
        SELECT
            SAMPLE_BIZ_MODEL_ID
        FROM
            CNF_LABORATORY_BIZ_CAPACITY cap
        WHERE
            LABORATORY_ID IN (
                SELECT
                    ID
                FROM
                    RES_LABORATORY
                WHERE
                    1 = 1
            )
    )

注意
Oracle中in括号中的项，不能超过1000
超过1000项就会报错，主要是考虑性能问题做的限制