1.<if> 标签 适用于单个条件
<select id="mapperFunctionName" resultType="entity.User">
select * from table_user
<where>
<if test="name!=null and name!=''">
and name = #{name}
</if>
<if test="age!=null and age!=''">
and age= #{age}
</if>
</where>
</select>
2.<choose> 标签 适用于多个条件判断
<select id="mapperFunctionName" resultType="entity.User">
select * from table_user
<where>
<choose>
<when test="name!=null and name!=''">
and name = #{name}
</when>
<when test="age!=null and age!=''">
and age= #{age}
</when>
<otherwise>
and password = #{password}
</otherwise>
</choose>
</where>
</select>
拼接常用的sql语句
1.某种情况下才给aaaName赋值否则赋为'':
case when +条件 then +sql子查询+ else ’‘ end as aaaName
2. having + group by组合用来对结果集进行分组和筛选:
group by 字段 HAVING (条件);
过滤条件中使用了聚合函数,或行已经被分组,则必须使用HAVING来替换WHERE!
3.查数据是否in数据中 用 REPLACE 替换数组
select * from user where
FIND_IN_SET(#{id}, REPLACE(REPLACE(name_id, '[', ''), ']', '')) > 0
4. 查数据直接in 参数@params(ids)
SELECT * FROM cloud_repair_bill WHERE id IN
<foreach item="id" collection="ids" open="(" separator="," close=")">
#{id}
</foreach>