MYSQL COUNT/SUM IF/IFNULL 结合使用

1、IF(条件，值1，值2) 如果条件为true，则IF()的返回 （值1），否则返回（值2）
2、IFNULL(expr1,expr2) 假如expr1不为NULL，则IFNULL()的返回值为expr1;否则其返回值为expr2
实际代码：

select
			COUNT(IF(e.`level` = 1, 1, NULL)) lv1,
			COUNT(IF(e.`level` = 2, 1, NULL)) lv2,
			COUNT(IF(e.`level` = 3, 1, NULL)) lv3,
			COUNT(IF(e.`level` = 4, 1, NULL)) lv4,
			COUNT(IF(e.`level` = 5, 1, NULL)) lv5,
			COUNT(IF(e.`level` = 6, 1, NULL)) lv6,
			COUNT(IF(e.`level` = 7, 1, NULL)) lv7,
			COUNT(IF(e.`level` = 8, 1, NULL)) lv8,
			COUNT(IF(e.`level` = 9, 1, NULL)) lv9,
			COUNT(IF(sc.score = 3, 1, NULL)) 's3',
			<if test="pd.pay_flag != null and pd.pay_flag != '' and !pd.isAdminG">
			SUM(IF(e.`level` = 1, (IFNULL(prd.pay_reg_fee,0) + IFNULL(prd.pay_other_fee,0) + IFNULL(prd.pay_other_fee_center,0)), 0)) fee1,
			SUM(IF(e.`level` = 2, (IFNULL(prd.pay_reg_fee,0) + IFNULL(prd.pay_other_fee,0) + IFNULL(prd.pay_other_fee_center,0)), 0)) fee2,
			SUM(IF(e.`level` = 3, (IFNULL(prd.pay_reg_fee,0) + IFNULL(prd.pay_other_fee,0) + IFNULL(prd.pay_other_fee_center,0)), 0)) fee3,
			SUM(IF(e.`level` = 4, (IFNULL(prd.pay_reg_fee,0) + IFNULL(prd.pay_other_fee,0) + IFNULL(prd.pay_other_fee_center,0)), 0)) fee4,
			SUM(IF(e.`level` = 5, (IFNULL(prd.pay_reg_fee,0) + IFNULL(prd.pay_other_fee,0) + IFNULL(prd.pay_other_fee_center,0)), 0)) fee5,
			SUM(IF(e.`level` = 6, (IFNULL(prd.pay_reg_fee,0) + IFNULL(prd.pay_other_fee,0) + IFNULL(prd.pay_other_fee_center,0)), 0)) fee6,
			SUM(IF(e.`level` = 7, (IFNULL(prd.pay_reg_fee,0) + IFNULL(prd.pay_other_fee,0) + IFNULL(prd.pay_other_fee_center,0)), 0)) fee7,
			SUM(IF(e.`level` = 8, (IFNULL(prd.pay_reg_fee,0) + IFNULL(prd.pay_other_fee,0) + IFNULL(prd.pay_other_fee_center,0)), 0)) fee8,
			SUM(IF(e.`level` = 9, (IFNULL(prd.pay_reg_fee,0) + IFNULL(prd.pay_other_fee,0) + IFNULL(prd.pay_other_fee_center,0)), 0)) fee9,
			SUM(IF(sc.score = 3, (IFNULL(prd.pay_reg_fee,0) + IFNULL(prd.pay_other_fee,0) + IFNULL(prd.pay_other_fee_center,0)), 0)) feeS3,
			</if>
			s.id subject_id,
			s.subject_nm
		from
			t_exam e
			inner join m_subject s on e.subject_id = s.id
        	inner join r_area_agency_user aau on e.ar_ag_u_id = aau.id
        	left join t_score sc on sc.exam_id = e.id
        	<if test="pd.pay_flag != null and pd.pay_flag != ''">
			left join (SELECT DISTINCT pr.pay_other_fee,pr.pay_other_fee_center,pr.pay_reg_fee,pr.exam_id 
				FROM t_pay_record_detail pr INNER JOIN t_pay_record pd ON pd.id = pr.pay_record_id 
				WHERE pd.pay_flag=1 
				GROUP BY pr.exam_id)prd ON prd.exam_id = e.id
			</if>
		<where>
				AND e.delete_flag = 0
			<if test="pd.pay_flag != null and pd.pay_flag != ''">
				and e.pay_flag= #{pd.pay_flag}
			</if>
			<if test="pd.keywords!= null and pd.keywords != ''"><!-- 关键词检索 -->
				and
					(
					e.batch_no LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
					or
					e.exam_id LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
					or 
					e.id LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
					)
			</if>
			<if test="pd.start_date!=null and pd.start_date!=''">
				and convert(e.create_dt,date)&gt;=convert(#{pd.start_date},date)
			</if>
			<if test="pd.end_date!=null and pd.end_date!=''">
				and convert(e.create_dt,date)&lt;=convert(#{pd.end_date},date)
			</if>
			<if test="pd.period_ids != null and pd.period_ids != ''">
				and e.period_id in 
				<foreach item="item" index="index" collection="pd.period_id_list" open="(" separator="," close=")">
					${item}
				</foreach>
			</if>
			<if test="pd.agency_id!=null and pd.agency_id!=''">
				and aau.agency_id =#{pd.agency_id}
			</if>
			<if test="pd.area_id!= null and pd.area_id != ''">
				and aau.area_id = #{pd.area_id}
			</if>
			<if test="pd.batch_no != null and pd.batch_no != ''">
				and e.batch_no = #{pd.batch_no}
			</if>
			<if test="pd.timesId != null and pd.timesId != ''">
				and e.exam_times_id = #{pd.timesId}
			</if>
		</where>
		GROUP BY e.subject_id