MySQL 滑动订单问题

滑动订单问题是指为每个月返回上一年度（季度或月度等）的滑动订单数。
即：某个月份N，返回从（N-11）月到N月的订单总数。（架设月份序列中不存在间断）。



首先创建一个测试表

1. create table monthly_orders(
   
2. ordermonth date,
   
3. ordernum int unsigned,
   
4. primary key(ordermonth));

再插入一些数据

1. insert into monthly_orders select '2010-02-01',23;
   
2. insert into monthly_orders select '2010-03-01',26;
   
3. insert into monthly_orders select '2010-04-01',24;
   
4. insert into monthly_orders select '2010-05-01',27;
   
5. insert into monthly_orders select '2010-06-01',26;
   
6. insert into monthly_orders select '2010-07-01',32;
   
7. insert into monthly_orders select '2010-08-01',34;
   
8. insert into monthly_orders select '2010-09-01',30;
   
9. insert into monthly_orders select '2010-10-01',31;
   
10. insert into monthly_orders select '2010-11-01',32;    
    
11. insert into monthly_orders select '2010-12-01',33;
    
12. insert into monthly_orders select '2011-01-01',31;
    
13. insert into monthly_orders select '2011-02-01',34;
    
14. insert into monthly_orders select '2011-03-01',34;
    
15. insert into monthly_orders select '2011-04-01',38;
    
16. insert into monthly_orders select '2011-05-01',39;
    
17. insert into monthly_orders select '2011-06-01',35;
    
18. insert into monthly_orders select '2011-07-01',49;
    
19. insert into monthly_orders select '2011-08-01',56;
    
20. insert into monthly_orders select '2011-09-01',55;
    
21. insert into monthly_orders select '2011-10-01',74;
    
22. insert into monthly_orders select '2011-11-01',75;
    
23. insert into monthly_orders select '2011-12-01',14;

最后返回的结果集应该为：



解决方案

1. select
   
2.     date_format(a.ordermonth, '%Y-%m') as from_month,
   
3.     date_format(b.ordermonth, '%Y-%m') as to_month,
   
4.     sum(c.ordernum) as orders
   
5. from monthly_orders a
   
6.     join monthly_orders b
   
7.     on date_add(a.ordermonth, interval 11 month) = b.ordermonth
   
8.     join monthly_orders c
   
9.     on c.ordermonth between a.ordermonth and b.ordermonth
   
10. group by a.ordermonth,b.ordermonth;

该查询对monthly_orders表进行自联接，a表用做下边界(from_month )，b表用做上边界(to_month )。
联接条件为：date_add(a.ordermonth, interval 11 month) = b.ordermonth。
例如2010年2月将匹配2011年1月
完成此次自联接操作之后，需要对订单进行统计，这时需要在进行一次自联接，得到范围内每个月的订单数量，因此联接条件为c.ordermonth between a.ordermonth and b.ordermonth 。



按照上述方法，我们还可以统计每个季度订单的详情，作为环比和同比增长的比较依据。

即



解决方案：

1. select
   
2.     date_format(a.ordermonth,\'%Y-%m\') as from_month,
   
3.     date_format(b.ordermonth,\'%Y-%m\') as to_month,
   
4.     sum(c.ordernum) as orders
   
5. from monthly_orders a
   
6.     join monthly_orders b
   
7.     on date_add(a.ordermonth, interval 2 month) = b.ordermonth
   
8.     and month(a.ordermonth) % 3 = 1
   
9.     join monthly_orders c
   
10.     on c.ordermonth between a.ordermonth and b.ordermonth
    
11. group by a.ordermonth,b.ordermonth;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29773961/viewspace-1248568/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29773961/viewspace-1248568/