xdmy/doc/eqzhibiao/getChooseMonthiolPrice.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/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(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
 
  
  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(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,
IFNULL((SELECT SUM(cowSumBudgetMonth)cowSumBudgetMonth FROM `month_budget`  WHERE budgetMonth >= tem.startTime   AND budgetMonth < tem.endTime  AND feeType='燃动费' AND pastureId = tem.pastureId ),0)cowSumBudgetMonth

 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
)t
left join 
pasture  pp on pp.id  = t.pastureId
where pp.id <>18
group by pp.center,pp.region,pp.name  with rollup