数据库
service层
publicList<ReportDayWeekMonthYear>getReportDayWeekMonthYear(ReportDayWeekMonthYearReqreq){LocalDatestartTimeLocalDate=LocalDate.parse(req.getStartTime(),DateTimeFormatter.ofPattern("yyyy-MM-dd"));LocalDateendTimeLocalDate=LocalDate.parse(req.getEndTime(),DateTimeFormatter.ofPattern("yyyy-MM-dd"));Durationduration=Duration.between(startTimeLocalDate.atStartOfDay(),endTimeLocalDate.atStartOfDay());StringtimeFormatParam;Stringtype;intscope;if(req.getFlag()==4){intyears=endTimeLocalDate.getYear()-startTimeLocalDate.getYear();intendMonth=endTimeLocalDate.getMonthValue();intstartMonth=startTimeLocalDate.getMonthValue();scope=endMonth-startMonth+1;System.out.println("month:"+scope);}else{scope=(int)duration.toDays()+1;}switch(req.getFlag()){case1:// 查询当日数据timeFormatParam="%Y-%m-%d";type="DAY";//scope = 1;break;case2:// 查询周数据timeFormatParam="%Y-%m-%d";type="DAY";//scope = 7;break;case3:// 查询月数据timeFormatParam="%Y-%m-%d";type="DAY";//scope = 30;break;case4:// 查询年数据timeFormatParam="%Y-%m";type="MONTH";//scope = 12;break;default:timeFormatParam="%Y-%m-%d";type="DAY";//scope = 1;break;}List<ReportDayWeekMonthYear>reportDayWeekMonthYear=xxlJobLogReportMapper.getReportDayWeekMonthYear(endTimeLocalDate,timeFormatParam,type,scope);for(ReportDayWeekMonthYeardayWeekMonthYear:reportDayWeekMonthYear){System.out.println(dayWeekMonthYear);}returnreportDayWeekMonthYear;}mapper
select t1.curr_date as orderly_time,IFNULL(t2.fail_count,0)fail_count,IFNULL(t2.running_count,0)running_count,IFNULL(t2.suc_count,0)suc_count from(select date_format(date_add(#{endTimeLocalDate},interval-t.help_topic_id ${type}),#{timeFormatParam})as curr_date from mysql.help_topic t where t.help_topic_id<#{scope})t1LEFTJOIN(<choose><when test="type != 'MONTH' ">SELECTDATE_FORMAT(trigger_day,#{timeFormatParam})formatime,running_count,suc_count,fail_countFROMxxl_job_log_report</when><otherwise>//count(列名)是统计分组后列的个数,//sum(列名)是将列的值相加SELECTDATE_FORMAT(trigger_day,'%Y-%m')formatime,sum(running_count)running_count,sum(suc_count)suc_count,sum(fail_count)fail_countFROMxxl_job_log_reportGROUPBYformatime</otherwise></choose>)t2ONt1.curr_date=t2.formatime