蝙蝠岛资源网 Design By www.hbtsch.com
本文实例讲述了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)。连接的条件为:
DATE_ADD(a.ordermonth, INTERVAL 11 MONTH) = b.ordermonth
例如,a表中的2010年2月将匹配2011年1月。
完成自连接之后,需要对订单进行统计。这时需要再进行一次自连接,得到范围内每个月的订单数量。因此连接的条件为
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 orders FROM monthlyorders a INNER JOIN monthlyorders b ON DATE_ADD(a.ordermonth, INTERVAL 2 MONTH) = b.ordermonth AND MONTH(a.ordermonth) % 3 = 1 INNER JOIN monthlyorders c ON c.ordermonth BETWEEN a.ordermonth AND b.ordermonth GROUP BY a.ordermonth,b.ordermonth;
运行结果如下图
更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
标签:
mysql,滑动订单
蝙蝠岛资源网 Design By www.hbtsch.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
蝙蝠岛资源网 Design By www.hbtsch.com
暂无mysql滑动订单问题原理与解决方法实例分析的评论...
更新日志
2024年12月23日
2024年12月23日
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]