Swaggy_Z0306 2019-08-14 14:14 采纳率: 0%
浏览 907
已采纳

MySQL求环比同比。有视图a_date和视图b_date,求环比和同比。

视图A视图B

现在创建了视图a_date和视图b_date,求环比和同比。

视图a_date
INSERT INTO a_date(date, feiyong) VALUES ('2017-09-01', 57557.50);
INSERT INTO a_date(date, feiyong) VALUES ('2017-10-01', 57257.08);
INSERT INTO a_date(date, feiyong) VALUES ('2017-11-01', 57100.79);
INSERT INTO a_date(date, feiyong) VALUES ('2017-12-01', 57763.20);
INSERT INTO a_date(date, feiyong) VALUES ('2018-01-01', 57180.89);
INSERT INTO a_date(date, feiyong) VALUES ('2018-02-01', 57076.54);
INSERT INTO a_date(date, feiyong) VALUES ('2018-03-01', 57211.45);
INSERT INTO a_date(date, feiyong) VALUES ('2018-04-01', 57124.92);
INSERT INTO a_date(date, feiyong) VALUES ('2018-05-01', 58431.51);
INSERT INTO a_date(date, feiyong) VALUES ('2018-06-01', 56914.29);
INSERT INTO a_date(date, feiyong) VALUES ('2018-07-01', 56866.79);
INSERT INTO a_date(date, feiyong) VALUES ('2018-08-01', 56081.86);
INSERT INTO a_date(date, feiyong) VALUES ('2018-09-01', 53450.41);
INSERT INTO a_date(date, feiyong) VALUES ('2018-10-01', 52854.91);
INSERT INTO a_date(date, feiyong) VALUES ('2018-11-01', 52784.23);
INSERT INTO a_date(date, feiyong) VALUES ('2018-12-01', 53019.75);
INSERT INTO a_date(date, feiyong) VALUES ('2019-01-01', 55041.18);
INSERT INTO a_date(date, feiyong) VALUES ('2019-02-01', 54135.55);
INSERT INTO a_date(date, feiyong) VALUES ('2019-03-01', 53856.55);
INSERT INTO a_date(date, feiyong) VALUES ('2019-04-01', 54259.70);
INSERT INTO a_date(date, feiyong) VALUES ('2019-05-01', 54156.53);
INSERT INTO a_date(date, feiyong) VALUES ('2019-06-01', 55552.37);
视图b_date
INSERT INTO b_date(date, feiyong) VALUES ('2017-10-01', 57557.50);
INSERT INTO b_date(date, feiyong) VALUES ('2017-11-01', 57257.08);
INSERT INTO b_date(date, feiyong) VALUES ('2017-12-01', 57100.79);
INSERT INTO b_date(date, feiyong) VALUES ('2018-01-01', 57763.20);
INSERT INTO b_date(date, feiyong) VALUES ('2018-02-01', 57180.89);
INSERT INTO b_date(date, feiyong) VALUES ('2018-03-01', 57076.54);
INSERT INTO b_date(date, feiyong) VALUES ('2018-04-01', 57211.45);
INSERT INTO b_date(date, feiyong) VALUES ('2018-05-01', 57124.92);
INSERT INTO b_date(date, feiyong) VALUES ('2018-06-01', 58431.51);
INSERT INTO b_date(date, feiyong) VALUES ('2018-07-01', 56914.29);
INSERT INTO b_date(date, feiyong) VALUES ('2018-08-01', 56866.79);
INSERT INTO b_date(date, feiyong) VALUES ('2018-09-01', 56081.86);
INSERT INTO b_date(date, feiyong) VALUES ('2018-10-01', 53450.41);
INSERT INTO b_date(date, feiyong) VALUES ('2018-11-01', 52854.91);
INSERT INTO b_date(date, feiyong) VALUES ('2018-12-01', 52784.23);
INSERT INTO b_date(date, feiyong) VALUES ('2019-01-01', 53019.75);
INSERT INTO b_date(date, feiyong) VALUES ('2019-02-01', 55041.18);
INSERT INTO b_date(date, feiyong) VALUES ('2019-03-01', 54135.55);
INSERT INTO b_date(date, feiyong) VALUES ('2019-04-01', 53856.55);
INSERT INTO b_date(date, feiyong) VALUES ('2019-05-01', 54259.70);
INSERT INTO b_date(date, feiyong) VALUES ('2019-06-01', 54156.53);
INSERT INTO b_date(date, feiyong) VALUES ('2019-07-01', 55552.37);

MySQL版本5.6.42

  • 写回答

2条回答 默认 最新

  • 德玛洗牙 2019-08-15 14:51
    关注

    你这算同环比只需要一张表就足够了吧。我这就以a表未真实数据的表来计算同环比吧
    select t1.date,
    case when t2.feiyong is not null then (t1.feiyong-t2.feiyong)/t2.feiyong else null end as 环比,
    case when t3.feiyong is not null then (t1.feiyong-t3.feiyong)/t3.feiyong else null end as 同比
    from a_date t1
    left join
    (select DATE_ADD(date,INTERVAL 1 month) as date,feiyong from a_date) t2
    on
    t1.date=t2.date
    left join
    (select DATE_ADD(date,INTERVAL 1 year) as date,feiyong from a_date) t3
    on
    t1.date=t3.date
    ;

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 电脑开机过商标后就直接这样,求解各位
  • ¥15 mysql , 用自己创建的本地主机和用户名 登录不上
  • ¥15 关于#web项目#的问题,请各位专家解答!
  • ¥15 vmtools环境不正常
  • ¥15 请问如何在Ubuntu系统中安装使用microsoft R open?
  • ¥15 jupyter notebook
  • ¥30 informer时间序列预测
  • ¥20 SSR引物多态性分析
  • ¥15 大漠插件在Win11易语言注册调用和免注册灵异事件,VS上注册调用完全没问题
  • ¥15 Addressable缓存机制做热更新的问题