SUM(CASE WHEN ?? THEN ?? ELSE ?? END) AS ??

case...when...then...else...end，是在from前面，可以改变记录中某字段的值，不能决定是否显示该记录；where，是在from后面，不可以改变记录中某字段的值，但可以决定是否显示该记录。case...when...then...else...end，可用于对同一记录的多个字段求和，带分支判断。另外，对字段判断和处理，往往需要强制类型转换。<select id="findByPage" resultType="com.huarui.mapping.entity.PmpersoninputEntity" parameterType="mon.entity.web.Req">SELECT a.project_id,a.user_id,b.project_name,c.user_name,d.input_id,d.input_ym,sum(d.input_status) as total,SUM(CASE WHEN d.INPUT_DAY = '01' THEN d.input_status ELSE NULL END) AS day01,SUM(CASE WHEN d.INPUT_DAY = '02' THEN d.input_status ELSE NULL END) AS day02,SUM(CASE WHEN d.INPUT_DAY = '03' THEN d.input_status ELSE NULL END) AS day03,SUM(CASE WHEN d.INPUT_DAY = '04' THEN d.input_status ELSE NULL END) AS day04,SUM(CASE WHEN d.INPUT_DAY = '05' THEN d.input_status ELSE NULL END) AS day05,SUM(CASE WHEN d.INPUT_DAY = '06' THEN d.input_status ELSE NULL END) AS day06,SUM(CASE WHEN d.INPUT_DAY = '07' THEN d.input_status ELSE NULL END) AS day07,SUM(CASE WHEN d.INPUT_DAY = '08' THEN d.input_status ELSE NULL END) AS day08,SUM(CASE WHEN d.INPUT_DAY = '09' THEN d.input_status ELSE NULL END) AS day09,SUM(CASE WHEN d.INPUT_DAY = '10' THEN d.input_status ELSE NULL END) AS day10,SUM(CASE WHEN d.INPUT_DAY = '11' THEN d.input_status ELSE NULL END) AS day11,SUM(CASE WHEN d.INPUT_DAY = '12' THEN d.input_status ELSE NULL END) AS day12,SUM(CASE WHEN d.INPUT_DAY = '13' THEN d.input_status ELSE NULL END) AS day13,SUM(CASE WHEN d.INPUT_DAY = '14' THEN d.input_status ELSE NULL END) AS day14,SUM(CASE WHEN d.INPUT_DAY = '15' THEN d.input_status ELSE NULL END) AS day15,SUM(CASE WHEN d.INPUT_DAY = '16' THEN d.input_status ELSE NULL END) AS day16,SUM(CASE WHEN d.INPUT_DAY = '17' THEN d.input_status ELSE NULL END) AS day17,SUM(CASE WHEN d.INPUT_DAY = '18' THEN d.input_status ELSE NULL END) AS day18,SUM(CASE WHEN d.INPUT_DAY = '19' THEN d.input_status ELSE NULL END) AS day19,SUM(CASE WHEN d.INPUT_DAY = '20' THEN d.input_status ELSE NULL END) AS day20,SUM(CASE WHEN d.INPUT_DAY = '21' THEN d.input_status ELSE NULL END) AS day21,SUM(CASE WHEN d.INPUT_DAY = '22' THEN d.input_status ELSE NULL END) AS day22,SUM(CASE WHEN d.INPUT_DAY = '23' THEN d.input_status ELSE NULL END) AS day23,SUM(CASE WHEN d.INPUT_DAY = '24' THEN d.input_status ELSE NULL END) AS day24,SUM(CASE WHEN d.INPUT_DAY = '25' THEN d.input_status ELSE NULL END) AS day25,SUM(CASE WHEN d.INPUT_DAY = '26' THEN d.input_status ELSE NULL END) AS day26,SUM(CASE WHEN d.INPUT_DAY = '27' THEN d.input_status ELSE NULL END) AS day27,SUM(CASE WHEN d.INPUT_DAY = '28' THEN d.input_status ELSE NULL END) AS day28,SUM(CASE WHEN d.INPUT_DAY = '29' THEN d.input_status ELSE NULL END) AS day29,SUM(CASE WHEN d.INPUT_DAY = '30' THEN d.input_status ELSE NULL END) AS day30,SUM(CASE WHEN d.INPUT_DAY = '31' THEN d.input_status ELSE NULL END) AS day31FROM OA_PM_PERSON_INPUT a,OA_PM_PROJECT b,OA_COMPANY_USER c,OA_PM_PERSON_INPUT_ITEM d<where>a.project_id=b.project_id and a.user_id= c.user_id and a.input_id=d.input_id<if test="parameterMap.project_id != null">AND a.project_id = #{parameterMap.project_id}</if><if test="parameterMap.user_name != null">AND c.user_name = #{parameterMap.user_name}</if><if test="parameterMap.input_ym != null">AND d.input_ym = #{parameterMap.input_ym}</if></where>GROUP BY a.project_id,a.user_id,b.project_name,c.user_name,d.input_id,d.input_ym</select>