kpt_xdmy/doc/eqzhibiao/getChooseMonthOilT.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(d.oilAmount),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') #startTime
AND d.selTime  < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01') #endTime
  
  ) 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
  d.pastureId,
  ROUND(IFNULL(SUM(d.oilAmount),0),2) sumPrice,
  #LEFT(d.selTime,7)wmonth,
	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') #startTime
AND d.selTime  < CONCAT(DATE_FORMAT(DATE_ADD(CONCAT(LEFT(?,7),'-01'),INTERVAL 1 MONTH),'%Y-%m'),'-01') #endTime
AND  (d.pastureId = ? OR ? in ('18',''))
AND (d.deptId = ? or ? = '')
  GROUP BY  d.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