mysql 环比 同比_MySQL统计同比环比SQL

大体思路：

MySQL没有类似oracle方便的统计函数，只能靠自己去硬计算：通过时间字段直接增加年份、月份，然后通过left join关联时间字段去计算环比、同比公式即可

原始表结构：

求同比SQL：

-- 按年同比

SELECTt5.*,CASEWHENt5.last_energy_yearISNULLORt5.last_energy_year =0THEN0.00

ELSEFORMAT(((t5.energy_year - t5.last_energy_year)/t5.last_energy_year)*100,2)

ENDYoY-- 同比

FROM(

SELECTt3.*,t4.last_energy_year

FROM

(

SELECTDATE_FORMAT(CONCAT(t1.`tyear`,‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘)ASYearMonth,t1.tyear,SUM(t1.energy_month) energy_year,t1.linename

FROM(SELECT

t.*,COUNT(DISTINCTt.`tyear`,t.`tmonth`,t.`linename`) c_cot

FROM`ksh_tgyjy_llxgyjydlxx` t

GROUPBYt.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1

GROUPBYt1.tyear,t1.`linename`

) t3

LEFTJOIN

(

SELECTDATE_ADD(DATE_FORMAT(CONCAT(t1.`tyear`,‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘),INTERVAL 1YEAR)ASlastYearMonth,t1.tyear,SUM(t1.energy_month) last_energy_year,t1.linename

FROM(SELECTt.*,COUNT(DISTINCTt.`tyear`,t.`tmonth`,t.`linename`) c_cot

FROM`ksh_tgyjy_llxgyjydlxx` t

GROUPBYt.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1

GROUPBYt1.tyear,t1.`linename`

) t4

ONt3.YearMonth = t4.lastYearMonth

ANDt3.linename = t4.linename

) t5-- 按年同比SELECT t5.*, CASE WHEN t5.last_energy_year IS NULL OR t5.last_energy_year =0 THEN 0.00                ELSE FORMAT(((t5.energy_year - t5.last_energy_year)/t5.last_energy_year)*100,2)         END YoY  -- 同比FROM (    SELECT t3.*,t4.last_energy_year    FROM     (       SELECT DATE_FORMAT(CONCAT(t1.`tyear`, ‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘) AS YearMonth,t1.tyear,SUM(t1.energy_month) energy_year,t1.linename        FROM(SELECT         t.*,COUNT(DISTINCT t.`tyear`,t.`tmonth`,t.`linename`) c_cot         FROM `ksh_tgyjy_llxgyjydlxx` t         GROUP BY t.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1        GROUP BY t1.tyear,t1.`linename`     ) t3       LEFT JOIN     (      SELECT DATE_ADD(DATE_FORMAT(CONCAT(t1.`tyear`, ‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘),INTERVAL 1 YEAR) AS lastYearMonth,t1.tyear,SUM(t1.energy_month) last_energy_year,t1.linename        FROM(SELECT t.*,COUNT(DISTINCT t.`tyear`,t.`tmonth`,t.`linename`) c_cot         FROM `ksh_tgyjy_llxgyjydlxx` t         GROUP BY t.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1        GROUP BY t1.tyear,t1.`linename`    ) t4    ON t3.YearMonth = t4.lastYearMonth    AND t3.linename = t4.linename ) t5

查询结果：

求月环比SQL：

-- 按月同比

SELECTDATE_FORMAT(t5.YearMonth,‘%Y-%m‘) YearMonth,t5.`linename`,t5.`energy_month`,

CASEWHENt5.lat_energy_monthISNULLORt5.lat_energy_month=0THEN0.00

ELSEFORMAT(((t5.energy_month - t5.lat_energy_month)/t5.lat_energy_month)*100,2)

ENDYoY

FROM(

SELECTt3.*,t4.energy_month lat_energy_month

FROM

(

SELECTDATE_FORMAT(CONCAT(t1.`tyear`,‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘)ASYearMonth,t1.*

FROM(SELECT

t.*,COUNT(DISTINCTt.`tyear`,t.`tmonth`,t.`linename`) c_cot

FROM`ksh_tgyjy_llxgyjydlxx` t

GROUPBYt.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1

) t3

LEFTJOIN

(

SELECTDATE_ADD(DATE_FORMAT(CONCAT(t1.`tyear`,‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘),INTERVAL 1YEAR)ASlastYearMonth,t1.*

FROM(SELECTt.*,COUNT(DISTINCTt.`tyear`,t.`tmonth`,t.`linename`) c_cot

FROM`ksh_tgyjy_llxgyjydlxx` t

GROUPBYt.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1

) t4

ONt3.YearMonth = t4.lastYearMonth

ANDt3.linename = t4.linename

) t5-- 按月同比SELECT DATE_FORMAT(t5.YearMonth,‘%Y-%m‘) YearMonth,t5.`linename`,t5.`energy_month`,          CASE WHEN t5.lat_energy_month IS NULL OR t5.lat_energy_month=0 THEN 0.00                 ELSE FORMAT(((t5.energy_month - t5.lat_energy_month)/t5.lat_energy_month)*100,2)         END YoYFROM (    SELECT t3.*,t4.energy_month lat_energy_month     FROM     (      SELECT DATE_FORMAT(CONCAT(t1.`tyear`, ‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘) AS YearMonth,t1.*        FROM(SELECT           t.*,COUNT(DISTINCT t.`tyear`,t.`tmonth`,t.`linename`) c_cot           FROM `ksh_tgyjy_llxgyjydlxx` t           GROUP BY t.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1     ) t3        LEFT JOIN     (      SELECT DATE_ADD(DATE_FORMAT(CONCAT(t1.`tyear`, ‘-‘, t1.`tmonth`,‘-01‘),‘%Y-%m-%d‘),INTERVAL 1 YEAR) AS lastYearMonth,t1.*         FROM(SELECT t.*,COUNT(DISTINCT t.`tyear`,t.`tmonth`,t.`linename`) c_cot         FROM `ksh_tgyjy_llxgyjydlxx` t         GROUP BY t.`linename`,t.`tyear`,t.`tmonth`,t.`linename`) t1    ) t4    ON t3.YearMonth = t4.lastYearMonth    AND t3.linename = t4.linename) t5

查询结果：