MyBatis的mapper中一个字段代表多列进行模糊匹配时,and和or都存在时需要将or的范围括起来作为and后面的参数,select其他表信息作为关联数据结果展示
select其他表信息作为关联数据结果展示
SQL应在连接MySQL的工具中进行摸底,各种条件匹配情况下测试,看数据是否能够对得上!
<!-- 注意and后面的条件,需要()括起来 因为它们整体作为一个搜索条件 -->
AND (INSTR(os.name,#{req.keyword}) OR INSTR(os.identifier,#{req.keyword}))
<select id="queryByPage" resultMap="ServicesVO">
select
distinct
os.*,
(select count(*) FROM t_model_services_params WHERE ref_id = os.id and type = 0) as inputData,
(select count(*) FROM t_model_services_params WHERE ref_id = os.id and type = 1) as outputData
from t_model_services os
left join t_model_services_params osp on os.id = osp.ref_id
<where>
<if test="req.modelId != null">
AND os.model_id = #{req.modelId}
</if>
<if test="req.keyword != null and req.keyword != ''">
<!-- 注意and后面的条件,需要()括起来 因为它们整体作为一个搜索条件 -->
AND (INSTR(os.name,#{req.keyword}) OR INSTR(os.identifier,#{req.keyword}))
</if>
</where>
</select>