case...when...then...else...end,是在from前面,可以改变记录中某字段的值,不能决定是否显示该记录;
where,是在from后面,不可以改变记录中某字段的值,但可以决定是否显示该记录。
case...when...then...else...end,可用于对同一记录的多个字段求和,带分支判断。
另外,对字段判断和处理,往往需要强制类型转换。
<select id="findByPage" resultType="com.huarui.mapping.entity.PmpersoninputEntity" parameterType="com.huarui.common.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 day31
FROM 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>