本文实例讲述了mysql滑动订单问题原理与解决方法。分享给大家供大家参考，具体如下：
先根据以下代码来创建表MonthlyOrders并导入一定的数据
|
1
2
3
4
5
|
CREATE TABLE MonthlyOrders(ordermonth DATE,ordernum INT UNSIGNED,PRIMARY KEY (ordermonth)); |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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查询实现每个月返回上一年度的滑动订单总数
|
1
2
3
4
5
6
7
8
9
10
|
SELECT DATE_FORMAT(a.ordermonth, '%Y%m') AS frommonth, DATE_FORMAT(b.ordermonth, '%Y%m') AS tomonth, SUM(c.ordernum) AS ordersFROM monthlyorders aINNER JOIN monthlyorders b ON DATE_ADD(a.ordermonth, INTERVAL 11 MONTH) = b.ordermonthINNER JOIN monthlyorders c ON c.ordermonth BETWEEN a.ordermonth AND b.ordermonthGROUP BY a.ordermonth,b.ordermonth; |
运行结果如下图
该查询首先对MonthlyOrders表进行自连接。a表用做下边界(frommonth)，b表用做上边界(tomonth)。连接的条件为:
|
1
|
DATE_ADD(a.ordermonth, INTERVAL 11 MONTH) = b.ordermonth |
例如，a表中的2010年2月将匹配2011年1月。
完成自连接之后，需要对订单进行统计。这时需要再进行一次自连接，得到范围内每个月的订单数量。因此连接的条件为
|
1
|
c.ordermonth BETWEEN a.ordermonth AND b.ordermonth |
基于上述方法，我们还可以统计每个季度订单的情况，以此作为和同比增长的比较依据。
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECT DATE_FORMAT(a.ordermonth, '%Y%m') AS frommonth, DATE_FORMAT(b.ordermonth, '%Y%m') AS tomonth, SUM(c.ordernum) AS ordersFROM monthlyorders aINNER JOIN monthlyorders b ON DATE_ADD(a.ordermonth, INTERVAL 2 MONTH) = b.ordermonth AND MONTH(a.ordermonth) % 3 = 1INNER JOIN monthlyorders c ON c.ordermonth BETWEEN a.ordermonth AND b.ordermonthGROUP BY a.ordermonth,b.ordermonth; |
运行结果如下图
希望本文所述对大家MySQL数据库计有所帮助。
原文链接：https://www.cnblogs.com/chenqionghe/p/4679800.html
