kpt_xdmy/doc/eqzhibiao/getChooseEqMonthBudgetListSumAll.sql

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