MyBatis复杂mapper，根据条件数组中的数据进一步判断、从子查询中进一步筛选数据

MyBatis复杂mapper，根据条件数组中的数据进一步判断、从子查询中进一步筛选数据

需要从子查询中进一步获取数据，数组中的数据进一步判断

<select id="queryByPage" resultMap="ThingModelVO">
        select
      *    
        from t_object_model
        <where>
            creator_number = #{queryDTO.loginName}
            <if test="queryDTO.categoryId != null and queryDTO.categoryId > 0">
                AND category_id = #{queryDTO.categoryId}
            </if>
            <if test="queryDTO.state != null and queryDTO.state.size > 1">
                AND state in
                <foreach collection="queryDTO.state" index="index" open="(" close=")" item="item" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="queryDTO.state == null or queryDTO.state.size == 0">
                or state in (1,20)</if>
            <if test="queryDTO.state != null and queryDTO.state.size == 1">
                <foreach collection="queryDTO.state" index="index" item="item">
                    <if test="item == 0">
                        and state = 0
                    </if>
                    <if test="item != 0">
                        or state = #{item}
                    </if>
                </foreach>
            </if>
            <if test="queryDTO.keyword != null and queryDTO.keyword != ''">
                AND INSTR(name,#{queryDTO.keyword})
                OR INSTR(creator_name,#{queryDTO.keyword})
                OR INSTR(creator_number,#{queryDTO.keyword})
            </if>
 
        </where>
    </select>

需要从子查询中进一步筛选数据

<select id="queryByPage" resultMap="ThingModelVO">
select * from (select
        *
        from t_object_model
        <where>
            creator_number = #{queryDTO.loginName}
            <if test="queryDTO.state == null or queryDTO.state.size == 0">
                or state in (1,20)
            </if>
            <if test="queryDTO.categoryId != null and queryDTO.categoryId > 0">
                AND category_id = #{queryDTO.categoryId}
            </if>
        </where>
        ) tempTable
        <where>
            1 = 1
            <if test="queryDTO.state != null and queryDTO.state.size > 1">
                AND state in
                <foreach collection="queryDTO.state" index="index" open="(" close=")" item="item" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="queryDTO.keyword != null and queryDTO.keyword != ''">
                AND (INSTR(name,#{queryDTO.keyword})
                OR INSTR(creator_name,#{queryDTO.keyword}))
        </where>
</select>