mysql滑动订单问题原理与解决方法实例分析
需积分: 0 145 浏览量
2020-09-08
23:30:42
上传
评论
收藏 55KB PDF 举报
mysql滑动订单问题原理与解决方法实例分析滑动订单问题原理与解决方法实例分析
主要介绍了mysql滑动订单问题原理与解决方法,结合实例形式分析了mysql滑动订单的问题的基本原理、解决方
法与相关操作注意事项,需要的朋友可以参考下
本文实例讲述了mysql滑动订单问题原理与解决方法。分享给大家供大家参考，具体如下：
先根据以下代码来创建表MonthlyOrders并导入一定的数据
CREATE TABLE MonthlyOrders(
ordermonth DATE,
ordernum INT UNSIGNED,
PRIMARY KEY (ordermonth)
);
INSERT INTO MonthlyOrders SELECT '2010-02-01',23;
INSERT INTO MonthlyOrders SELECT '2010-03-01',26;
INSERT INTO MonthlyOrders SELECT '2010-04-01',24;
INSERT INTO MonthlyOrders SELECT '2010-05-01',27;
INSERT INTO MonthlyOrders SELECT '2010-06-01',26;
INSERT INTO MonthlyOrders SELECT '2010-07-01',32;
INSERT INTO MonthlyOrders SELECT '2010-08-01',34;
INSERT INTO MonthlyOrders SELECT '2010-09-01',30;
INSERT INTO MonthlyOrders SELECT '2010-10-01',31;
INSERT INTO MonthlyOrders SELECT '2010-11-01',32;
INSERT INTO MonthlyOrders SELECT '2010-12-01',33;
INSERT INTO MonthlyOrders SELECT '2011-01-01',31;
INSERT INTO MonthlyOrders SELECT '2011-02-01',34;
INSERT INTO MonthlyOrders SELECT '2011-03-01',34;
INSERT INTO MonthlyOrders SELECT '2011-04-01',38;
INSERT INTO MonthlyOrders SELECT '2011-05-01',39;
INSERT INTO MonthlyOrders SELECT '2011-06-01',35;
INSERT INTO MonthlyOrders SELECT '2011-07-01',49;
INSERT INTO MonthlyOrders SELECT '2011-08-01',56;
INSERT INTO MonthlyOrders SELECT '2011-09-01',55;
INSERT INTO MonthlyOrders SELECT '2011-10-01',74;
INSERT INTO MonthlyOrders SELECT '2011-11-01',75;
INSERT INTO MonthlyOrders SELECT '2011-12-01',14;
滑动订单问题是指为每个月返回上一年度（季度或月度）的滑动订单数，即每个月份N，返回N-11到月份N的订单总数。这
里，假设月份序列中不存在间断。
执行下面的SQL查询实现每个月返回上一年度的滑动订单总数
SELECT
DATE_FORMAT(a.ordermonth, '%Y%m') AS frommonth,
DATE_FORMAT(b.ordermonth, '%Y%m') AS tomonth,
SUM(c.ordernum) AS orders
FROM monthlyorders a
INNER JOIN monthlyorders b
ON DATE_ADD(a.ordermonth, INTERVAL 11 MONTH) = b.ordermonth
INNER JOIN monthlyorders c
ON c.ordermonth BETWEEN a.ordermonth AND b.ordermonth
GROUP BY a.ordermonth,b.ordermonth;
运行结果如下图
该查询首先对MonthlyOrders表进行自连接。a表用做下边界(frommonth)，b表用做上边界(tomonth)。连接的条件为:
评论0
最新资源