BinJiLinZuo
Swaggy_Z0306
采纳率100%
2019-08-14 14:14

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条回答

  • qq_36720114 德玛洗牙 2年前

    你这算同环比只需要一张表就足够了吧。我这就以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
    ;

    点赞 评论 复制链接分享
  • zqbnqsdsmd zqbnqsdsmd 2年前