本文實(shí)例講述了mysql滑動(dòng)訂單問題原理與解決方法。分享給大家供大家參考，具體如下：
先根據(jù)以下代碼來創(chuàng)建表MonthlyOrders并導(dǎo)入一定的數(shù)據(jù)
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;
滑動(dòng)訂單問題是指為每個(gè)月返回上一年度（季度或月度）的滑動(dòng)訂單數(shù)，即每個(gè)月份N，返回N-11到月份N的訂單總數(shù)。這里，假設(shè)月份序列中不存在間斷。
執(zhí)行下面的SQL查詢實(shí)現(xiàn)每個(gè)月返回上一年度的滑動(dòng)訂單總數(shù)
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;
運(yùn)行結(jié)果如下圖
該查詢首先對(duì)MonthlyOrders表進(jìn)行自連接。a表用做下邊界(frommonth)，b表用做上邊界(tomonth)。連接的條件為:
DATE_ADD(a.ordermonth, INTERVAL 11 MONTH) = b.ordermonth
例如，a表中的2010年2月將匹配2011年1月。
完成自連接之后，需要對(duì)訂單進(jìn)行統(tǒng)計(jì)。這時(shí)需要再進(jìn)行一次自連接，得到范圍內(nèi)每個(gè)月的訂單數(shù)量。因此連接的條件為
c.ordermonth BETWEEN a.ordermonth AND b.ordermonth
基于上述方法，我們還可以統(tǒng)計(jì)每個(gè)季度訂單的情況，以此作為和同比增長(zhǎng)的比較依據(jù)。
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;
運(yùn)行結(jié)果如下圖
更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過程技巧大全》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》
希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。
您可能感興趣的文章:- MySQL中聚合函數(shù)count的使用和性能優(yōu)化技巧
- MySQL常用聚合函數(shù)詳解
- MySql 中聚合函數(shù)增加條件表達(dá)式的方法
- php+mysql開源XNA 聚合程序發(fā)布 下載
- Mysql無法選取非聚合列的解決方法
- MySQL查詢排序與查詢聚合函數(shù)用法分析
- MySQL單表查詢操作實(shí)例詳解【語(yǔ)法、約束、分組、聚合、過濾、排序等】
- mysql連續(xù)聚合原理與用法實(shí)例分析
- MySQL流程函數(shù)常見用法實(shí)例分析
- mysql累積聚合原理與用法實(shí)例分析
