MYSQL批量插入字段空值赋值0

 

    <insert id="insertReignFromReport">
        <foreach collection="departIdLst"  item="item" index="index" open="" close="" separator=";">
            insert into t_preparation_ryzwl(
                <include refid="baseInCol"/>
            )
            select
                pe.department_id,
                COALESCE(count(pe.id),0) as strength_amount,
                COALESCE(CONCAT(
                ROUND(sum(case when sb.reign=1 then 1
                               when sb.reign=2 then 1
                               when sb.reign=3 then 1
                               when sb.reign=4 then 1
                               when sb.reign=5 then 1
                               end) / count(pe.id) * 100,2)
                ),0),
                COALESCE(sum(case when sb.reign=1 then 1
                               when sb.reign=2 then 1
                               when sb.reign=3 then 1
                               when sb.reign=4 then 1
                               when sb.reign=5 then 1
                               end),0),
                COALESCE(sum(case when sb.reign=1 then 1 end),0),
                COALESCE(sum(case when sb.reign=2 then 1
                        when sb.reign=3 then 1
                        when sb.reign=4 then 1
                        when sb.reign=5 then 1
                        end),0),
                COALESCE(sum(case when sb.reign=6 then 1
                        when sb.reign=7 then 1
                        when sb.reign=8 then 1
                        when sb.reign=9 then 1
                        end),0),
                COALESCE(sum(case when sb.reign=10 then 1 end),0),
                COALESCE(sum(case when sb.reign=11 then 1 end),0),
                COALESCE(sum(case when sb.reign=12 then 1 end),0),
                COALESCE(sum(case when sb.reign=13 then 1 end),0),
                #{inuser},
                pe.person_type
            from t_preparation_rydtsb as sb
            inner join t_person as pe
            on sb.person_id = pe.id
            where sb.deleted = '0'
                and pe.deleted = '0'
                and pe.retired = '0'
                and pe.department_id = #{item}
                and date_format(sb.report_time,'%Y-%m-%d') = (SELECT MAX(date_format(report_time, '%Y-%m-%d'))
            FROM t_preparation_rydtsb sb LEFT JOIN t_person pe ON sb.person_id = pe.id where pe.department_id = #{item})
            group by pe.person_type
        </foreach>
    </insert>