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

原始表结构：

求同比SQL：

-- 按年同比

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

-- 按月同比

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 YoY


FROM (


   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

查询结果：