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!