部门活动支出sql报表

蒙古野驴（濒危一级）

需求，曲线图描述企业活动数量以及支出趋势。计算企业过去半年每个月审核通过总活动数量以及活动总支出、人均支出,当月无数据必须显示0，以及未来半年内的总活动数量以及活动经费申请均值和总值

列表效果

select 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 nextHalfYearPerPrice

from (

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.yMonth

group by dateTable.yMonth

order by dateTable.yMonth

表

tu_competition_audit

tu_competition

查询结果