MySQL Sliding order problem

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 ', 23;insert to 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 to monthlyorders select ' 2010-07-01 ', 32;insert into monthlyorders select ' 2010-08-01 ', 34;i Nsert into monthlyorders select ' 2010-09-01 ', 30;insert to 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 to monthlyorders select ' 2011-02-01 ', 34;insert into monthlyorders select ' 2011-03-01 ', 34;i Nsert into monthlyorders select ' 2011-04-01 ', 38;insert to 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 to 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;

Sliding order problem refers to the number of sliding orders that return the previous year (quarterly or monthly) for each month, that is, the total number of orders that return N-11 to month n for each month n. Here, suppose there are no breaks in the month sequence. Execute the following SQL query to implement the total number of sliding orders returned per month for the previous year

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 one MONTH) = B. Ordermonthinner JOIN monthlyorders C on    c.ordermonth between A.ordermonth and B.ordermonthgroup by A.ordermonth, B.ordermonth;

The results of the run, such as the query, first self-connects to the Monthlyorders table. Table A is used as the lower boundary (Frommonth), and B is used as the upper boundary (Tomonth). The conditions for the connection are:Date_add (A.ordermonth, INTERVAL MONTH) = B.ordermonth. for example, February 2010 in Table A will match January 2011. After you complete the self-connection, you need to count the orders. You need to do a self-connect again to get the number of orders per month in the range. So the conditions for the connection are c.ordermonth between A.ordermonth and B.ordermonth. Based on the above method, we can also count the situation of each quarterly order as a comparison of the growth with the previous year.

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.O Rdermonth and    MONTH (a.ordermonth)% 3 = 1INNER JOIN monthlyorders c on    c.ordermonth between A.ordermonth and B.ord Ermonthgroup by A.ordermonth,b.ordermonth;

Running results such as

MySQL Sliding order problem