kpt_xdmy/doc/eqzhibiao/getChooseMonthWater.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
'水量（吨）' 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