MySQL求环比和同比

[Swaggy_Z0306]

创建了a_date和b_两个date视图 不知道语句怎么写 求帮助

[stelf]

如果是msql8.0,有分析函数可以很好的实现你这个需求。如果是低版本的，找一下类似分析函数实现的方式

[Swaggy_Z0306]

引用 1 楼 stelf的回复:

如果是msql8.0,有分析函数可以很好的实现你这个需求。如果是低版本的，找一下类似分析函数实现的方式

只能硬算 我只知道公式是 （本期-上期）/上期 但不知道具体语句要怎么写

[stelf]

给出示例脚本，数据库版本，只截个图还要别人帮你一行一行敲例子啊？。。

引用 2 楼 BinJiLinZuo 的回复:

[quote=引用 1 楼 stelf的回复:]如果是msql8.0,有分析函数可以很好的实现你这个需求。如果是低版本的，找一下类似分析函数实现的方式

只能硬算 我只知道公式是 （本期-上期）/上期 但不知道具体语句要怎么写[/quote]

[Swaggy_Z0306]

视图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

[Swaggy_Z0306]

例子已给出和版本 求老哥帮助

[stelf]

看一下这样是否解决了你的问题？ with a_date as ( select date '2017-09-01' as p_date, 57557.50 as p_num from dual union all select '2017-10-01' as p_date, 57257.08 as p_num from dual union all select '2017-11-01' as p_date, 57100.79 as p_num from dual union all select '2017-12-01' as p_date, 57763.20 as p_num from dual union all select '2018-01-01' as p_date, 57180.89 as p_num from dual union all select '2018-02-01' as p_date, 57076.54 as p_num from dual union all select '2018-03-01' as p_date, 57211.45 as p_num from dual union all select '2018-04-01' as p_date, 57124.92 as p_num from dual union all select '2018-05-01' as p_date, 58431.51 as p_num from dual union all select '2018-06-01' as p_date, 56914.29 as p_num from dual union all select '2018-07-01' as p_date, 56866.79 as p_num from dual union all select '2018-08-01' as p_date, 56081.86 as p_num from dual union all select '2018-09-01' as p_date, 53450.41 as p_num from dual union all select '2018-10-01' as p_date, 52854.91 as p_num from dual union all select '2018-11-01' as p_date, 52784.23 as p_num from dual union all select '2018-12-01' as p_date, 53019.75 as p_num from dual union all select '2019-01-01' as p_date, 55041.18 as p_num from dual union all select '2019-02-01' as p_date, 54135.55 as p_num from dual union all select '2019-03-01' as p_date, 53856.55 as p_num from dual union all select '2019-04-01' as p_date, 54259.70 as p_num from dual union all select '2019-05-01' as p_date, 54156.53 as p_num from dual union all select '2019-06-01' as p_date, 55552.37 as p_num from dual) select * from a_date p1 left join a_date p2 on p1.p_date = DATE_ADD(p2.p_date, INTERVAL -1 MONTH) left join a_date p3 on p1.p_date = DATE_ADD(p3.p_date, INTERVAL -1 YEAR)

[stelf]

[Swaggy_Z0306]

引用 7 楼 stelf的回复:

这样表都连起来了就直接可以套入公式算了是吧

[stelf]

你的问题不就是不知道怎么连接这个数据么？数据给你准备好了，你想怎么用就怎么使用啊

引用 8 楼 BinJiLinZuo 的回复:

[quote=引用 7 楼 stelf的回复:]

这样表都连起来了就直接可以套入公式算了是吧[/quote]

[Swaggy_Z0306]

引用 9 楼 stelf的回复:

你的问题不就是不知道怎么连接这个数据么？数据给你准备好了，你想怎么用就怎么使用啊 [quote=引用 8 楼 BinJiLinZuo 的回复:] [quote=引用 7 楼 stelf的回复:]

这样表都连起来了就直接可以套入公式算了是吧[/quote][/quote] 明白了 谢谢老哥！！