SQL语法

1、IFNULL、NULLIF、ISNULL的用法

　　IFNULL的表达式：IFNULL（表达式1，表达式2）===》如果表达式1不为null，则返回表达式1的结果，如果表达式1结果为null，则返回表达式2的结果

　　NULLIF的表达式：NULLIF（表达式1，表达式2）===》如果表达式1的结果和表达式2的结果相等，则返回null，否则返回表达式1的结果

　　ISNULL的表达式：ISNULL（表达式）===》如果表达式的结果为null，则ISNULL返回的结果是1，否则返回0

 

2、CASE WHEN 表达式1 THEN 表达式2 ELSE 表达式3  END ===》如果表达式1成立，则返回表达式2，否则返回表达式3

 

3、OR的语法示例

<select id="listResumeByPhoneOrEmail" resultMap="BaseResultMap">
           SELECT <include refid="Base_Column_List"/> FROM T_RESUME_INFO_VERSION
        <where>
            1=1 AND T_RESUME_INFO_VERSION.DEFUNCT=0
                AND T_RESUME_INFO_VERSION.IS_LEVE = 3
                AND T_RESUME_INFO_VERSION.RESUME_ID = #{resumeId}
             AND ( 1!=1
                 <if test="phone!=null and phone!=''">
                       OR T_RESUME_INFO_VERSION.PHONE=#{phone} 
                  </if>    
                 <if test="email!=null and email!=''">
                       OR T_RESUME_INFO_VERSION.EMAIL=#{email} 
                 </if>
                 )
        </where>
        ORDER BY T_RESUME_INFO_VERSION.`UPDATE_DTTM` ASC
   </select>

 

4、循环的语法示例

<select id="listByResumeIds" resultMap="BaseResultMap" parameterType="java.util.List">
   		SELECT 
   			<include refid="Base_Column_List" /> 
		FROM 
			T_RESUME_INFO_VERSION resume
		WHERE 
			resume.version in (
				SELECT vlist.versionList from (
					SELECT rv.resume_id AS resume_id,MAX(VERSION) as versionList 
					FROM T_RESUME_INFO_VERSION rv 
					WHERE rv.RESUME_ID in 
					<foreach collection="resumeIds" index="index" item="item" open="(" separator="," close=")">  
			            #{item}  
			        </foreach>
		group by
			rv.resume_id DESC) as vlist)
		AND 
			resume.RESUME_ID in 
				<foreach collection="resumeIds" index="index" item="item" open="(" separator="," close=")">  
		            #{item}  
		        </foreach>
   </select>

　　

 

转载于:https://www.cnblogs.com/LTmei/p/7793213.html