mysql滑动订单问题原理与解决方法实例分析
16lz
2021-12-19
本文实例讲述了mysql滑动订单问题原理与解决方法。分享给大家供大家参考,具体如下:
先根据以下代码来创建表MonthlyOrders并导入一定的数据
CREATE TABLE MonthlyOrders(ordermonth DATE,ordernum INT UNSIGNED,PRIMARY KEY (ordermonth));
执行下面的SQL查询实现每个月返回上一年度的滑动订单总数
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)。连接的条件为:
DATE_ADD(a.ordermonth, INTERVAL 11 MONTH) = b.ordermonth
完成自连接之后,需要对订单进行统计。这时需要再进行一次自连接,得到范围内每个月的订单数量。因此连接的条件为
c.ordermonth BETWEEN a.ordermonth AND b.ordermonth
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相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
更多相关文章
- 《Android和PHP最佳实践》官方站
- android用户界面之按钮(Button)教程实例汇
- TabHost与RadioGroup结合完成的菜单【带效果图】5个Activity
- Android(安卓)UI开发第十七篇——Android(安卓)Fragment实例(Lis
- Android——Activity四种启动模式
- Android布局(序章)
- android中HttpURLConnection调用getResponseCode()时崩溃 解决方
- Android空引用问题的解决方法——on a null object reference
- Android发送短信方法实例详解