溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本文實例講述了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數據庫計有所幫助。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
