SQL条件查询优化
有的时候不需要关联查询,解耦,可以用in,数据量大的用exist关键字
<select id="queryBySearch" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List" />
FROM derictinvest_counterparty
WHERE 1=1
<if test="cpName != null and cpName.length > 0 ">
AND cp_id IN (SELECT id FROM counterparty WHERE `name` LIKE concat(concat('%',#{cpName},'%')))
</if>
<if test="diEpName != null and diEpName.length > 0 ">
AND di_ep_id IN (SELECT id FROM derictinvest_enterprise WHERE `name` LIKE concat(concat('%',#{diEpName},'%')))
</if>
<if test="investNo != null and investNo.length > 0 ">
AND upper(invest_no) LIKE concat(concat('%',upper(#{investNo}),'%'))
</if>
ORDER BY update_time DESC
</select>