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)>=convert(#{pd.start_date},date)
</if>
<if test="pd.end_date!=null and pd.end_date!=''">
and convert(e.create_dt,date)<=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