定时任务更新数据

定时任务更新数据

 

为空的数据设置   0   进行汇总

<!-- 未备案托位数、备案托位数 -->
        update screen_statistics ss
            join (
                SELECT s.street_code street_code
                ,COALESCE(temp.not_record_nursery_num,0) not_record_nursery_num
                ,COALESCE(temp.record_nursery_num,0) record_nursery_num
                from street as `s` left join
                (select street_code,sum(is_record=0) not_record_nursery_num,sum(is_record=1) record_nursery_num
                from ns_info where state != 0 and state !=4 group by street_code)
                as `temp` on temp.street_code = s.street_code
            ) as `temp` on temp.street_code = ss.street_code
            set ss.not_record_nursery_num = temp.not_record_nursery_num,
                ss.record_nursery_num = temp.record_nursery_num
        where ss.create_date = CURRENT_DATE and is_static = 0;

　　

最后多加一行   汇总数据  

select coalesce(a1.street_name,'总计') as streetName,
               sum(a1.nursery_total)  institutionNum ,
               sum(a1.not_record_total)  notRecordTotal,
               sum(a1.record_total) recordTotal,
               concat(IFNULL(round(sum(a1.recordRate),2),0),'%') recordRate,
               sum(a1.infant_class_num) infantClass,
               sum(a1.small_class_num) smallClass,
               sum(a1.big_class_num) bigClass,
               sum(a1.mix_class_num) mixClass
        from
            (select street_name,nursery_total,record_total,not_record_total
                  ,concat(round((record_total/nursery_total)*100,2),'%') as recordRate
                  ,infant_class_num,small_class_num,big_class_num,mix_class_num
             from screen_statistics where create_date = #{date} and is_static = 0 and is_district = 0 and street_code !='330105'
            ) a1
        GROUP BY a1.street_name
            WITH ROLLUP;