Shulou(Shulou.com)06/01 Report--

This paper describes the principle and solution of mysql sliding order problem with an example. Share with you for your reference, the details are as follows:

First create the table MonthlyOrders and import some data according to the following code

CREATE TABLE MonthlyOrders (ordermonth DATE,ordernum INT UNSIGNED,PRIMARY KEY (ordermonth)); INSERT INTO MonthlyOrders SELECT '2010-02-01; INSERT INTO MonthlyOrders SELECT' 2010-02-01; 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 INSERT INTO MonthlyOrders SELECT '2010-10-01 Magic 31 insert INTO MonthlyOrders SELECT' 2010-11-01 QR 32 insert INTO MonthlyOrders SELECT '2010-12-01 QR 33 insert INTO MonthlyOrders SELECT' 2011-01-01 MIT 31 insert INTO MonthlyOrders SELECT '2011-02-01 MIT 34 lead insert INTO MonthlyOrders SELECT' 2011-03-01 pr 34 lead insert INTO MonthlyOrders SELECT '2011-04-01 M 38 lead insert INTO MonthlyOrders SELECT' 2011-05-01 M 39 lead insert INTO MonthlyOrders SELECT '2011-06-01 QR 35 INSERT INTO MonthlyOrders SELECT '2011-07-01 lead 49 insert INTO MonthlyOrders SELECT' 2011-08-01 May 56 insert INTO MonthlyOrders SELECT '2011-09-01 May 55 insert INTO MonthlyOrders SELECT' 2011-10-01 May 74 insert INTO MonthlyOrders SELECT '2011-11-01 May 75 insert INTO MonthlyOrders SELECT' 2011-12-01 lead 14

The sliding order problem refers to the number of sliding orders that return to the previous year (quarterly or monthly) for each month, that is, the total number of orders returned from November to month N for each month. Here, it is assumed that there is no discontinuity in the month sequence.

Execute the following SQL query to return the total number of sliding orders for the previous year each month

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.ordermonth

The running result is as follows

The query starts with a self-join of the MonthlyOrders table. Table an is used as the lower boundary (frommonth) and table b is used as the upper boundary (tomonth). The conditions for the connection are:

DATE_ADD (a.ordermonth, INTERVAL 11 MONTH) = b.ordermonth

For example, February 2010 in table a will match January 2011.

After completing the connection, the order needs to be counted. At this point, you need to do another self-connection to get the number of orders per month in the range. So the condition of the connection is

C.ordermonth BETWEEN a.ordermonth AND b.ordermonth

Based on the above methods, we can also count the number of orders in each quarter as a basis for comparison with year-on-year growth.

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.ordermonthreb. Ordermonth

The running result is as follows

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.