SELECT sum(tem.asave) asave , sum(tem.monthBudget) monthBudget , sum(tem.monthLimit) monthLimit , sum(tem.qmonthBudget) qmonthBudget , sum(tem.sumPrice) sumPrice , avg(tem.oneCowAsave) oneCowAsave , avg(tem.oneCowBudget) oneCowBudget , avg(tem.oneCowLimit) oneCowLimit , avg(tem.oneCowQmonthBudget) oneCowQmonthBudget , avg(tem.oneCowSumPrice) oneCowSumPrice , tem.ftype ftype , tem.pastureId pastureId , tem.realReason realReason , tem.sort sort , 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 '现代牧业' pastureName, 20 sort, '维修费（万元）' ftype, ROUND(sum(qq.monthBudget),2) monthBudget, #预算 ROUND(sum(qq.monthLimit),2) monthLimit, #内控 ROUND(sum(qq.sumPrice)/10000,2) sumPrice, #迄今实际 ROUND( sum(qq.monthLimit)/qq.sumDay*qq.realDay,2) qmonthBudget, #迄今内控 ROUND( sum(qq.monthLimit)/qq.sumDay*qq.realDay-(sum(qq.sumPrice)/10000),2) asave, #实际节约 ROUND(1+(( sum(qq.monthLimit)/qq.sumDay*qq.realDay-(sum(qq.sumPrice)/10000))/(sum(qq.monthLimit)/qq.sumDay*qq.realDay)),2) realReason,#实际达成率 IFNULL(ROUND(sum(qq.monthBudget)/sum(qq.cowSumBudgetMonth)*10000,2),0) oneCowBudget, #单头牛预算 IFNULL(ROUND(sum(qq.monthLimit)/sum(qq.cowSumBudgetMonth)*10000,2),0) oneCowLimit, #单头牛内控 IFNULL(ROUND((sum(qq.monthLimit)/qq.sumDay*qq.realDay)/(sum(qq.cowSumBudgetMonth)/qq.sumDay*qq.realDay)*10000,2),0) oneCowQmonthBudget, #单头牛迄今内控 IFNULL(ROUND(sum(qq.sumPrice)/(sum(qq.cowSum)/qq.sumDay*qq.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际 IFNULL(ROUND((sum(qq.monthLimit)/qq.sumDay*qq.realDay-(sum(qq.sumPrice)))/(sum(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='维修费' AND (SELECT iscal FROM department d WHERE d.id=month_budget.`deptId`)= 0),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 (SELECT iscal FROM department d WHERE d.id=month_budget.`deptId`)= 0 ),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 (SELECT iscal FROM department d WHERE d.id=month_budget.`deptId`)= 0 ),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( pu.`sumPrice` - pu.`quitNumber` * pu.`price`),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 partuse pu INNER JOIN bigpartuse bpu ON pu.`bigId` = bpu.`id` WHERE bpu.`useType` <> 3 AND bpu.`receiveTime` >= CONCAT(DATE_FORMAT(CONCAT(LEFT(?,7),'-01'),'%Y-%m'),'-01') AND bpu.`receiveTime` < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01') AND ( pu.`useTypeV` = '维修' OR pu.`useTypeV` = '保养' ) AND (SELECT iscal FROM department d WHERE d.id=bpu.`departmentId`)= 0 GROUP BY LEFT(bpu.`receiveTime`,7) ) tem) qq UNION ALL SELECT p.name pastureName, p.sort, '维修费（万元）' ftype, ROUND(qq.monthBudget,2) monthBudget, #预算 ROUND(qq.monthLimit,2) monthLimit, #内控 ROUND(qq.sumPrice/10000,2) sumPrice, #迄今实际 ROUND( qq.monthLimit/qq.sumDay*qq.realDay,2) qmonthBudget, #迄今内控 ROUND( qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice/10000),2) asave, #实际节约 ROUND(1+(( qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice/10000))/(qq.monthLimit/qq.sumDay*qq.realDay)),2) realReason,#实际达成率 IFNULL(ROUND(qq.monthBudget/qq.cowSumBudgetMonth*10000,2),0) oneCowBudget, #单头牛预算 IFNULL(ROUND(qq.monthLimit/qq.cowSumBudgetMonth*10000,2),0) oneCowLimit, #单头牛内控 IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay)/(qq.cowSumBudgetMonth/qq.sumDay*qq.realDay)*10000,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 pastureId = tem.pastureId AND feeType='维修费' AND pastureId = tem.pastureId AND (SELECT iscal FROM department d WHERE d.id=month_budget.`deptId`)= 0),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 AND (SELECT iscal FROM department d WHERE d.id=month_budget.`deptId`)= 0 ),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 AND (SELECT iscal FROM department d WHERE d.id=month_budget.`deptId`)= 0 ),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 bpu.pastureId, ROUND(IFNULL(SUM( pu.`sumPrice` - pu.`quitNumber` * pu.`price`),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 partuse pu INNER JOIN bigpartuse bpu ON pu.`bigId` = bpu.`id` WHERE bpu.`useType` <> 3 AND bpu.`receiveTime` >= CONCAT(DATE_FORMAT(CONCAT(LEFT(?,7),'-01'),'%Y-%m'),'-01') AND bpu.`receiveTime` < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01') AND ( pu.`useTypeV` = '维修' OR pu.`useTypeV` = '保养' ) AND (SELECT iscal FROM department d WHERE d.id=bpu.`departmentId`)= 0 AND (bpu.pastureId = ? OR ? in ('18','')) AND (bpu.departmentId = ? or ? = '') GROUP BY bpu.pastureId ) tem) qq INNER JOIN pasture p ON p.id = qq.pastureId )tem left join pasture pp on pp.id = tem.pastureId where pp.id <>18 group by pp.center,pp.region,pp.name with rollup