kpt_xdmy/doc/eqzhibiao/getChooseWaterPrice.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,
'水费（万元）' 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,#单头牛迄今实际节约
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 * 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.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 * 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
)t
left join 
pasture  pp on pp.id  = t.pastureId
where pp.id <>18
group by pp.center,pp.region,pp.name  with rollup