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

不念不忘少年蓝@ 2022-05-19 06:50 183阅读 0赞

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>