1、CASE:
CASE value WHEN [compare-value1] THEN result1 [WHEN [compare-value2] THEN result2 [ELSE result3] END
解释:用value值来匹配,如果value1和value匹配,则返回result1 ,如果value2和value匹配,则返回result2,以此类推;否则,返回ELSE后的result3。;如果没有ELSE部分的值,则返回值为NULL。这种句型类似于Java当中的switch···case···default···。
举例:
<select id="selectAdmin" resultType="com.wanda.gmp.admin.dao.model.auth.Admin" parameterType="java.util.Map" >
SELECT
a.id id ,
c.id userId,
b.user_auth_name custName,
c.username phone,
b.identity_type shenfen,
a.updated_time updatedTime ,
a.`status` status,
a.category_type type,
d.full_name subjectName,
e.zh_name brandChina,
e.en_name brandEnglish,
a.updated_by updateName
FROM
user_auth_category a
LEFT JOIN user_auth_info b ON a.user_auth_info_id = b.id
LEFT JOIN `user` c on b.user_id=c.id
LEFT JOIN `subject` d on a.category_id = d.id and a.category_type=1
left join brand e on a.category_id = e.id and a.category_type=2
WHERE a.is_active = 1
AND
a.is_admin = 1
AND a. STATUS IN (1, 10,15)
<if test="userId != null and userId != '' " >
and b.user_id = #{userId,jdbcType=BIGINT}
</if>
<if test="custName != null and custName!=''" >
and b.user_auth_name LIKE CONCAT('%',#{custName,jdbcType=VARCHAR},'%')
</if>
<if test="updateName != null and updateName!=''" >
and a.updated_by LIKE CONCAT('%',#{updateName,jdbcType=VARCHAR},'%') and a.status != 1
</if>
<if test="status != null and status != '' " >
and a.status = #{status,jdbcType=TINYINT}
</if>
<if test="shenfen != null and shenfen != ''" >
and b.identity_type = #{shenfen,jdbcType=TINYINT}
</if>
<if test="name != null and name!=''" >
and (d.full_name LIKE CONCAT('%',#{name,jdbcType=VARCHAR},'%')
or e.zh_name LIKE CONCAT('%',#{name,jdbcType=VARCHAR},'%')
or e.en_name LIKE CONCAT('%',#{name,jdbcType=VARCHAR},'%'))
</if>
<if test="phone != null and phone!=''" >
and c.username = #{phone,jdbcType=VARCHAR}
</if>
<if test="endTime!=null and endTime!=''">
<![CDATA[ and DATE_FORMAT(a.updated_time, '%Y-%m-%d')<= DATE_FORMAT(#{endTime}, '%Y-%m-%d') ]]>
</if>
<if test="startTime!=null and startTime!=''">
<![CDATA[ and DATE_FORMAT(a.updated_time, '%Y-%m-%d')>= DATE_FORMAT(#{startTime}, '%Y-%m-%d') ]]>
</if>
ORDER BY CASE a.status WHEN '1 ' THEN 1 WHEN '15' THEN 2 WHEN '10' THEN 3 END,a.updated_time DESC
<if test="offset != null and max != null" >
limit ${offset},${max}
</if>
</select>
重点关注例子中的order by 中case的运用
2、IF:
解释:如果表达式expr1是TRUE ,则 IF()的返回值为expr2; 否则返回值则为 expr3。类似于三目运算符。
3、IFNULL:
解释:假如expr1不为NULL,则函数返回值为 expr1; 否则,如果如expr1为NULL,函数返回值为expr2。
4、NULLIF:
解释:如果expr1 = expr2成立,那么返回值为NULL,否则返回值为expr1