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 '现代牧业' pastureName, 20 sort, #RIGHT(rr.wmonth,2) num, '设备指标（万元）'ftype, ROUND(sum(rr.monthBudget),2) monthBudget, #预算 ROUND(sum(rr.monthLimit),2) monthLimit, #内控 ROUND(sum(rr.sumPrice),2) sumPrice, #迄今实际 ROUND( sum(rr.monthLimit)/rr.sumDay*rr.realDay,2) qmonthBudget, #迄今内控 ROUND( sum(rr.monthLimit)/rr.sumDay*rr.realDay-(sum(rr.sumPrice)),2) asave, #实际节约 ifnull(ROUND(1+(( sum(rr.monthLimit)/rr.sumDay*rr.realDay-(sum(rr.sumPrice)))/(sum(rr.monthLimit)/rr.sumDay*rr.realDay)),2),0) realReason,#实际达成率 IFNULL(ROUND(sum(rr.monthBudget)/sum(rr.cowSumBudgetMonth),2),0) oneCowBudget, #单头牛预算 -- IFNULL(ROUND(sum(rr.monthLimit)/sum(rr.cowSumBudgetMonth),2),0) oneCowLimit, #单头牛内控 sum(rr.oneCowLimit) oneCowLimit, -- IFNULL(ROUND((sum(rr.monthLimit)/rr.sumDay*rr.realDay)/(sum(rr.cowSumBudgetMonth)/rr.sumDay*rr.realDay),2),0) oneCowQmonthBudget, sum(rr.oneCowQmonthBudget) oneCowQmonthBudget, #单头牛迄今内控 sum(rr.oneCowSumPrice) oneCowSumPrice, -- IFNULL(ROUND(sum(rr.sumPrice)/(sum(rr.cowSum)/rr.sumDay*rr.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际 -- IFNULL(ROUND((sum(rr.monthLimit)/rr.sumDay*rr.realDay-(sum(rr.sumPrice)))/(sum(rr.cowSum)/rr.sumDay*rr.realDay),2),0) oneCowAsave, sum(rr.oneCowAsave)oneCowAsave, 18 pastureId FROM (SELECT '维修费（万元）' 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.sumDay, qq.realDay, qq.cowSumBudgetMonth, qq.cowSum 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, #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(DATE_ADD(?,INTERVAL -1 DAY),'%Y-%m'),'-01') AND bpu.`receiveTime` < CONCAT(DATE_FORMAT(DATE_ADD(DATE_ADD(?,INTERVAL -1 DAY),INTERVAL 1 YEAR),'%Y-%m'),'-01') AND ( pu.`useTypeV` = '维修' OR pu.`useTypeV` = '保养' ) AND (SELECT iscal FROM department d WHERE d.id=bpu.`departmentId`)= 0 ) tem) qq UNION ALL SELECT '电费（万元）' 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.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际 IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice))/(qq.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowAsave,#单头牛迄今实际节约 qq.sumDay, qq.realDay, qq.cowSum, qq.cowSumBudgetMonth 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, #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.elecConsumption * e.`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 electricity 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') 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/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.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际 IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice))/(qq.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowAsave,#单头牛迄今实际节约 qq.sumDay, qq.realDay, qq.cowSum, qq.cowSumBudgetMonth 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, #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 * e.`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 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') 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/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.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际 IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice))/(qq.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowAsave,#单头牛迄今实际节约 qq.sumDay, qq.realDay, qq.cowSum, qq.cowSumBudgetMonth 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, #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(d.oilAmount * d.`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 `diesel` d WHERE d.selTime >= CONCAT(DATE_FORMAT(CONCAT(LEFT(?,7),'-01'),'%Y-%m'),'-01') AND d.selTime < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01') ) tem) qq) rr UNION ALL SELECT p.name, p.sort, '设备指标（万元）'ftype, ROUND(sum(rr.monthBudget),2) monthBudget, #预算 ROUND(sum(rr.monthLimit),2) monthLimit, #内控 ROUND(sum(rr.sumPrice),2) sumPrice, #迄今实际 ROUND( sum(rr.monthLimit)/rr.sumDay*rr.realDay,2) qmonthBudget, #迄今内控 ROUND( sum(rr.monthLimit)/rr.sumDay*rr.realDay-(sum(rr.sumPrice)),2) asave, #实际节约 ifnull(ROUND(1+(( sum(rr.monthLimit)/rr.sumDay*rr.realDay-(sum(rr.sumPrice)))/(sum(rr.monthLimit)/rr.sumDay*rr.realDay)),2),0) realReason,#实际达成率 IFNULL(ROUND(sum(rr.monthBudget)/sum(rr.cowSumBudgetMonth),2),0) oneCowBudget, #单头牛预算 IFNULL(ROUND(sum(rr.monthLimit)/sum(rr.cowSumBudgetMonth),2),0) oneCowLimit, #单头牛内控 IFNULL(ROUND((sum(rr.monthLimit)/rr.sumDay*rr.realDay)/(sum(rr.cowSumBudgetMonth)/rr.sumDay*rr.realDay),2),0) oneCowQmonthBudget, #单头牛迄今内控 IFNULL(ROUND(sum(rr.sumPrice)/(sum(rr.cowSum)/rr.sumDay*rr.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际 IFNULL(ROUND((sum(rr.monthLimit)/rr.sumDay*rr.realDay-(sum(rr.sumPrice)))/(sum(rr.cowSum)/rr.sumDay*rr.realDay),2),0) oneCowAsave, pastureId FROM (SELECT '维修费（万元）' 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.sumDay, qq.realDay, qq.cowSumBudgetMonth, qq.cowSum, 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, #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 UNION ALL SELECT '电费（万元）' 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.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际 IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice))/(qq.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowAsave,#单头牛迄今实际节约 qq.sumDay, qq.realDay, qq.cowSumBudgetMonth, qq.cowSum, 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, #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.elecConsumption * e.`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 electricity 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') AND e.date < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01') AND (m.pastureId = ? OR ? in ('18','')) AND (m.departmentId = ? or ? = '') GROUP BY m.pastureId ) tem) qq UNION ALL SELECT '水费（万元）' 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.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际 IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice))/(qq.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowAsave,#单头牛迄今实际节约 qq.sumDay, qq.realDay, qq.cowSumBudgetMonth, qq.cowSum, 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, #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 * e.`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 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') AND e.date < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01') AND (m.pastureId = ? OR ? in ('18','')) AND (m.departmentId = ? or ? = '') GROUP BY m.pastureId ) tem) qq UNION ALL SELECT '燃动费（万元）' 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.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowSumPrice,#单头牛的迄今实际 IFNULL(ROUND((qq.monthLimit/qq.sumDay*qq.realDay-(qq.sumPrice))/(qq.cowSumBudgetMonth/qq.sumDay*qq.realDay),2),0) oneCowAsave,#单头牛迄今实际节约 qq.sumDay, qq.realDay, qq.cowSumBudgetMonth, qq.cowSum, 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, #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 d.pastureId, ROUND(IFNULL(SUM(d.oilAmount * d.`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 `diesel` d WHERE d.selTime >= CONCAT(DATE_FORMAT(CONCAT(LEFT(?,7),'-01'),'%Y-%m'),'-01') AND d.selTime < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01') AND (d.pastureId = ? OR ? in ('18','')) AND (d.deptId = ? or ? = '') GROUP BY d.pastureId ) tem) qq)rr INNER JOIN pasture p ON p.id = rr.pastureId GROUP BY rr.pastureId )t left join pasture pp on pp.id = t.pastureId where pp.id <>18 group by pp.center,pp.region,pp.name with rollup