蒙古野驴(濒危一级)需求,曲线图描述企业活动数量以及支出趋势。计算企业过去半年每个月审核通过总活动数量以及活动总支出、人均支出,当月无数据必须显示0,以及未来半年内的总活动数量以及活动经费申请均值和总值列表效果审核月份月活动总数月人均支出月总支出未来半年活动总数未来半年人均支出未来半年总支出1996-011月活动总数1月人均1月总支出[1996-01-1996-06]1996-02select dateTable.yMonth as auditDay, round(sum(ifnull(dataInfo.price,0)),2) priceTotalByMonth, round(sum(ifnull(dataInfo.headcount,0)),2) headcountTotalByMonth, ifnull(round(sum(dataInfo.headcount*dataInfo.price)/sum(dataInfo.headcount),2),0) perPriceByMonth, round(sum( if(dataInfo.startDate >= dateTable.yMonth and dataInfo.startDate <= date_format(concat(dateTable.yMonth,'01') + INTERVAL 5 MONTH, '%Y%m') ,dataInfo.headcount,0) ),2) as nextHalfYearHeadcountTotal, round(sum( if(dataInfo.startDate >= dateTable.yMonth and dataInfo.startDate <= date_format(concat(dateTable.yMonth,'01') + INTERVAL 5 MONTH, '%Y%m') ,dataInfo.price,0) ),2) as nextHalfYearPriceTotal, ifnull(round(sum( if(dataInfo.startDate >= dateTable.yMonth and dataInfo.startDate <= date_format(concat(dateTable.yMonth,'01') + INTERVAL 5 MONTH, '%Y%m') ,dataInfo.headcount*dataInfo.price,0) ) / sum(if(dataInfo.startDate >= dateTable.yMonth and dataInfo.startDate <= date_format(concat(dateTable.yMonth,'01') + INTERVAL 5 MONTH, '%Y%m') ,dataInfo.headcount,null)) ,2),0) as nextHalfYearPerPricefrom ( select date_format(now(),'%Y%m') as yMonth union all select date_format(now() - INTERVAL 1 MONTH,'%Y%m') as yMonth union all select date_format(now() - INTERVAL 2 MONTH,'%Y%m') as yMonth union all select date_format(now() - INTERVAL 3 MONTH,'%Y%m') as yMonth union all select date_format(now() - INTERVAL 4 MONTH,'%Y%m') as yMonth union all select date_format(now() - INTERVAL 5 MONTH,'%Y%m') as yMonth ) dateTable left join ( select date_format(audit.audit_date,'%Y%m') as auditDate ,date_format(com.start_date,'%Y%m') as startDate ,com.price ,com.headcount from tu_competition_audit audit left join tu_competition com on com.id = audit.competition_id) dataInfo on dataInfo.auditDate = dateTable.yMonthgroup by dateTable.yMonthorder by dateTable.yMonth表tu_competition_audittu_competition查询结果