-- 参考：SQL计算月环比、月同比_路易吃泡面的博客-CSDN博客 
-- mysql同比环比 

drop table if EXISTS ordertable;
create table ordertable 
-- 做业绩表-基础表  
SELECT 
date_format(STR_TO_DATE(concat_ws(‘/‘,Cyear,Cmonth),‘%Y/%m‘),‘%Y-%m‘) as datetime_month 
,STR_TO_DATE(concat_ws(‘/‘,Cyear,Cmonth,Cday),‘%Y/%m/%d‘) as datetime_day  
,sum(jftotalcount)  as orderpay  
from order_pivot_11 
GROUP BY 
date_format(STR_TO_DATE(concat_ws(‘/‘,Cyear,Cmonth),‘%Y/%m‘),‘%Y-%m‘)
,STR_TO_DATE(concat_ws(‘/‘,Cyear,Cmonth,Cday),‘%Y/%m/%d‘) ; 

-- 
-- SELECT  date_format(DATE_ADD(DATE_FORMAT(CONCAT(‘2020-03‘,‘-01‘),‘%Y-%m-%d‘),INTERVAL -1 month ),‘%Y-%m‘) -- 2020-02
-- 计算同比环比 
-- 公司 
SELECT 
a.d,a.orderpay_a,b.dd,b.datetime_month,b.orderpay_b
,(a.orderpay_a-b.orderpay_b)/b.orderpay_b as month_rate 
,concat(left((a.orderpay_a-b.orderpay_b)/b.orderpay_b *100,5),‘%‘) as 月环比 
from 
(
SELECT datetime_month as d ,sum(orderpay) as orderpay_a  
from ordertable GROUP BY datetime_month 
) a 
left join 
(
SELECT date_format(DATE_ADD(DATE_FORMAT(CONCAT(datetime_month,‘-01‘),‘%Y-%m-%d‘),INTERVAL 1 month ),‘%Y-%m‘) as dd,datetime_month ,sum(orderpay) as orderpay_b from ordertable 
GROUP BY 
date_format(DATE_ADD(DATE_FORMAT(CONCAT(datetime_month,‘-01‘),‘%Y-%m-%d‘),INTERVAL 1 month ),‘%Y-%m‘)
,datetime_month
) b on a.d=b.dd