select round(sum(t.monthBudget),2)monthBudget, round(sum(t.monthLimit),2)monthLimit, round(sum(t.sumPrice),2)sumPrice, round(sum(t.qmonthBudget),2)qmonthBudget , round(sum(t.asave),2)asave, round(avg(t.oneCowBudget),2)oneCowBudget, round(avg(t.oneCowLimit),2)oneCowLimit, round(avg(t.oneCowQmonthBudget),2)oneCowQmonthBudget, round(avg(t.oneCowSumPrice),2)oneCowSumPrice, round(avg(t.oneCowAsave),2)oneCowAsave, t.ftype ftype, t.realReason realReason, t.pastureId pastureId, case when pp.name is null then case when pp.center is null then '集团' else if(pp.region is null,pp.center,pp.region) end else pp.name end pastureName from (SELECT '水量（吨）' ftype, ROUND(qq.monthBudget,2) monthBudget, #预算 ROUND(qq.monthLimit,2) monthLimit, #内控 ROUND(qq.sumPrice,2) sumPrice, #迄今实际 ROUND( qq.monthLimit/qq.sumDay*qq.realDay,2) qmonthBudget, #迄今内控 ROUND( qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice),2) asave, #实际节约 ROUND(1+(( qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice))/(qq.monthLimit/qq.sumDay*qq.realDay)),2) realReason,#实际达成率 IFNULL(ROUND(qq.monthBudget/qq.cowSumBudgetMonth,2),0) oneCowBudget, #单头牛预算 IFNULL(ROUND(qq.monthLimit/qq.cowSumBudgetMonth,2),0) oneCowLimit, #单头牛内控 IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay)/(qq.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowQmonthBudget, #单头牛迄今内控 IFNULL(ROUND(qq.sumPrice/(qq.cowSum/qq.sumDay*qq.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际 IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice))/(qq.cowSum/qq.sumDay*qq.realDay),2),0) oneCowAsave,#单头牛迄今实际节约 18 pastureId FROM (SELECT IFNULL((SELECT SUM(monthBudget)monthBudget FROM `month_budget` WHERE concat(budgetMonth,'-','01') >= tem.startTime AND concat(budgetMonth,'-','01') < tem.endTime AND feeType='水量'),0)monthBudget, IFNULL((SELECT SUM(monthLimit)monthLimit FROM `month_budget` WHERE concat(budgetMonth,'-','01') >= tem.startTime AND concat(budgetMonth,'-','01') < tem.endTime AND feeType='水量' ),0)monthLimit, IFNULL((SELECT SUM(cowSumBudgetMonth)cowSumBudgetMonth FROM `month_budget` WHERE concat(budgetMonth,'-','01') >= tem.startTime AND concat(budgetMonth,'-','01') < tem.endTime AND feeType='水量' ),0)cowSumBudgetMonth, IFNULL((SELECT sum(fs.`cowNum`) FROM feed_sum fs INNER JOIN pasture p ON p.id = fs.pastureId WHERE fs.`feedDay` >= tem.startTime and fs.`feedDay` <= tem.endTime),0) cowSum, tem.startTime, tem.endTime, #DATEDIFF(DATE_ADD(LAST_DAY(tem.endTime),INTERVAL 1 DAY),tem.startTime) sumDay,#当前总天数 #DATEDIFF(tem.endTime,tem.startTime) realDay,#到当前选择的天数 DATEDIFF(LAST_DAY(tem.endTime),CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))+1 sumDay, if (DATEDIFF(tem.endTime,CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))=0,DATEDIFF(LAST_DAY(tem.endTime),CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))+1,DATEDIFF(tem.endTime,CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))) realDay, tem.sumPrice FROM(SELECT ROUND(IFNULL(SUM(e.waterConsumption ),0),2) sumPrice, CONCAT(DATE_FORMAT(CONCAT(LEFT(?,7),'-01'),'%Y-%m'),'-01') startTime, IF(RIGHT(?,2)='00',CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01'),DATE_ADD(?,INTERVAL 1 DAY)) endTime FROM water e INNER JOIN `measure` m ON m.id = e.measureId WHERE m.meterType = '水表' AND m.useType='2级表' AND e.date >= CONCAT(DATE_FORMAT(CONCAT(LEFT(?,7),'-01'),'%Y-%m'),'-01') #startTime AND e.date < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01') ) tem) qq UNION ALL SELECT '水量（吨）' ftype, ROUND(qq.monthBudget,2) monthBudget, #预算 ROUND(qq.monthLimit,2) monthLimit, #内控 ROUND(qq.sumPrice,2) sumPrice, #迄今实际 ROUND( qq.monthLimit/qq.sumDay*qq.realDay,2) qmonthBudget, #迄今内控 ROUND( qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice),2) asave, #实际节约 ROUND(1+(( qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice))/(qq.monthLimit/qq.sumDay*qq.realDay)),2) realReason,#实际达成率 IFNULL(ROUND(qq.monthBudget/qq.cowSumBudgetMonth,2),0) oneCowBudget, #单头牛预算 IFNULL(ROUND(qq.monthLimit/qq.cowSumBudgetMonth,2),0) oneCowLimit, #单头牛内控 IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay)/(qq.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowQmonthBudget, #单头牛迄今内控 IFNULL(ROUND(qq.sumPrice/(qq.cowSum/qq.sumDay*qq.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际 IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice))/(qq.cowSum/qq.sumDay*qq.realDay),2),0) oneCowAsave,#单头牛迄今实际节约 qq.pastureId FROM (SELECT tem.pastureId, IFNULL((SELECT SUM(monthBudget)monthBudget FROM `month_budget` WHERE concat(budgetMonth,'-','01') >= tem.startTime AND concat(budgetMonth,'-','01') < tem.endTime AND feeType='水量' AND pastureId = tem.pastureId),0)monthBudget, IFNULL((SELECT SUM(monthLimit)monthLimit FROM `month_budget` WHERE concat(budgetMonth,'-','01') >= tem.startTime AND concat(budgetMonth,'-','01') < tem.endTime AND feeType='水量' AND pastureId = tem.pastureId ),0)monthLimit, IFNULL((SELECT SUM(cowSumBudgetMonth)cowSumBudgetMonth FROM `month_budget` WHERE concat(budgetMonth,'-','01') >= tem.startTime AND concat(budgetMonth,'-','01') < tem.endTime AND feeType='水量' AND pastureId = tem.pastureId ),0)cowSumBudgetMonth, IFNULL((SELECT sum(fs.`cowNum`) FROM feed_sum fs INNER JOIN pasture p ON p.id = fs.pastureId WHERE p.id= tem.pastureId and fs.`feedDay` >= tem.startTime and fs.`feedDay` <= tem.endTime),0) cowSum, tem.startTime, tem.endTime, #DATEDIFF(DATE_ADD(LAST_DAY(tem.endTime),INTERVAL 1 DAY),tem.startTime) sumDay,#当前总天数 #DATEDIFF(tem.endTime,tem.startTime) realDay,#到当前选择的天数 DATEDIFF(LAST_DAY(tem.endTime),CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))+1 sumDay, if (DATEDIFF(tem.endTime,CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))=0,DATEDIFF(LAST_DAY(tem.endTime),CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))+1,DATEDIFF(tem.endTime,CONCAT(DATE_FORMAT(tem.endTime,'%Y-%m'),'-01'))) realDay, tem.sumPrice FROM(SELECT m.pastureId, ROUND(IFNULL(SUM(e.waterConsumption ),0),2) sumPrice, CONCAT(DATE_FORMAT(CONCAT(LEFT(?,7),'-01'),'%Y-%m'),'-01') startTime, IF(RIGHT(?,2)='00',CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01'),DATE_ADD(?,INTERVAL 1 DAY)) endTime FROM water e INNER JOIN `measure` m ON m.id = e.measureId WHERE m.meterType = '水表' AND m.useType='2级表' AND e.date >= CONCAT(DATE_FORMAT(CONCAT(LEFT(?,7),'-01'),'%Y-%m'),'-01') #startTime AND e.date < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01') #endTime AND (m.pastureId = ? OR ? in ('18','')) AND (m.departmentId = ? or ? = '') GROUP BY m.pastureId) tem) qq )t left join pasture pp on pp.id = t.pastureId where pp.id <>18 group by pp.center,pp.region,pp.name with rollup