Title: there are two tables
Table 1: Sales details

  Table 2: product details

Demand: query the monthly sales in 2020 and the year-on-year comparison, and sort them in ascending order by month and year

Idea: step by step operation

1. Get monthly data
   First, the extraction time and the sales of each month. To have sales, the two tables must be connected
   -------Get time
   Year - gets the year
   Month (field name) -- get time

SELECT YEAR(s.sail_time) year, MONTH(s.sail_time) month , SUM(s.number*p.pro_price) sales volume  
 FROM  sail_info s 
 LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id
GROUP BY YEAR(s.sail_time) , MONTH(s.sail_time)  

The reason for using the left link here is to supplement the prices of individual products in the sales table. The left link is used based on the left table
result:

1. Single step operation to obtain year-on-year data
   Year on year: generally, it is the ratio between the nth month of this year and the nth month of last year
   Calculation method: year-on-year growth rate = (amount in current period - amount in the same period) ÷ amount in the same period × 100%
   Therefore, to have a table with one-to-one correspondence between the current period data and the same period data, the current period data should be separated from the same period data
   With the first step, it's easy to get data
   Current data:

SELECT YEAR(s.sail_time) year, MONTH(s.sail_time) month , SUM(s.number*p.pro_price) sales volume  
 FROM  sail_info s 
 LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id
where YEAR(s.sail_time)=2020 
GROUP BY YEAR(s.sail_time) , MONTH(s.sail_time)  

  Contemporaneous data:

SELECT YEAR(s.sail_time) year, MONTH(s.sail_time) month , SUM(s.number*p.pro_price) sales volume  
 FROM  sail_info s 
 LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id
where YEAR(s.sail_time)=2019
GROUP BY YEAR(s.sail_time) , MONTH(s.sail_time)  

Now we will associate tables
The difference between the current period and the same period is that the year is different and the month is the same. Then use the month as the connection point. The code and result screenshot are as follows, which is completed year-on-year~
The calculation is for the current period, and the data of the current period is the key, so the right connection is used here - because the data of the current period is placed on the right, and the left connection is used on the left
ps: if you think the steps are skipped here, you can change the query result to * to view the associated table first

SELECT  CONCAT(e,'-',a) years, b Monthly sales, 
CASE WHEN d>0 THEN CONCAT((b-d)/d*100,'％') -- Convert to percentage. Considering that the sales amount is 0, the denominator cannot be 0. Use case Conditional judgment is carried out
     ELSE "No data in the same period" END Year on year  
     -- The text here can be changed
 FROM 
(SELECT YEAR(s.sail_time) f , MONTH(s.sail_time) c , SUM(s.number*p.pro_price) d FROM  sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id  WHERE YEAR(s.sail_time)=2019 GROUP BY MONTH(s.sail_time)) s2 
RIGHT JOIN 
(SELECT YEAR(s.sail_time) e，MONTH(s.sail_time) a , SUM(s.number*p.pro_price) b  FROM  sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id  WHERE YEAR(s.sail_time)=2020 GROUP BY MONTH(s.sail_time)) s1 
ON s1.a=s2.c 

 

3. Obtain month on month data in one step
Month on month: generally refers to the ratio between the level of the reporting period and the level of the previous period. Here, it refers to the data of this month and the data of last month
Calculation method: month on month growth rate = (current period number - previous period number) ÷ previous period number × 100%
It must be related to the table. How can it be connected???
Since the chain comparison in 2020 is calculated, the table is related based on the data to be calculated
There are all the data corresponding to the year and month above. This step is mainly to associate the tables
Associate two tables:
-1. The data table of 2020, that is, the query result table with the judgment condition set as 2020
-2 considering that January 2020 corresponds to December 2019, this table is a table without judgment conditions, including the data of 2019 and 2020

SELECT  *  FROM 
(SELECT YEAR(s.sail_time) e,MONTH(s.sail_time) a , SUM(s.number*p.pro_price) Current sales  FROM  sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id  WHERE YEAR(s.sail_time)=2020 GROUP BY MONTH(s.sail_time)) s1 
LEFT JOIN 
(SELECT YEAR(s.sail_time) f, MONTH(s.sail_time) g, SUM(s.number*p.pro_price) Sales of last period FROM  sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id GROUP BY MONTH(s.sail_time),YEAR(s.sail_time)) s3 
ON ((s1.a-1)=s3.g AND s1.e=s3.f) OR (s1.a=1 AND s3.g=12 AND s3.f=2019)

  Resolve the following:

(s1.a-1)=s3.g AND s1.e=s3.f  

This is for the data of February 2020. The data of 2020 02-06 corresponds to the data of 2020 01-05 one by one
According to the before correlation, a (month) and G (month) should be equal, because the comparison is from the previous period, so they should be staggered, and the large ones should be subtracted from the small ones
What about January??

s1.a=1 AND s3.g=12 AND s3.f=2019

You can't use the above method here. It's a new year, so you need to use the and connection to limit it, and it needs to be limited to December 2019. Because there are few data here, it's no problem not to limit the year

The two parts exist side by side with or, and the two conditions should be enclosed in parentheses respectively, otherwise the query condition is equal to No....
Replace * with

CONCAT(e,'-',a) years,f Monthly sales, 
 CASE WHEN h>0 THEN CONCAT((b-h)/h*100,'％')
     ELSE "No data in the previous period" END Ring ratio

Execute query:

1. The last step is to put the data into a table~
   Through the above description, it can be found that all tables are associated with the 2020 data query, so this table can be placed in the middle and the three tables can be associated (which is also the reason why the year-on-year data is connected to the right)

CASE WHEN d>0 THEN CONCAT((b-d)/d*100,'％')
     ELSE "No data in the same period" END Year on year , 
 CASE WHEN h>0 THEN CONCAT((b-h)/h*100,'％')
     ELSE "No data in the previous period" END Ring ratio
 FROM 
(SELECT MONTH(s.sail_time) c , SUM(s.number*p.pro_price) d ,YEAR(s.sail_time) ye FROM  sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id  WHERE YEAR(s.sail_time)=2019 GROUP BY MONTH(s.sail_time)) s2 
RIGHT JOIN 
(SELECT MONTH(s.sail_time) a , SUM(s.number*p.pro_price) b ,YEAR(s.sail_time) e FROM  sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id  WHERE YEAR(s.sail_time)=2020 GROUP BY MONTH(s.sail_time)) s1 
ON s1.a=s2.c 
LEFT JOIN 
(SELECT YEAR(s.sail_time) f, MONTH(s.sail_time) g, SUM(s.number*p.pro_price) h FROM  sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id GROUP BY MONTH(s.sail_time),YEAR(s.sail_time)) s3 ON ((s1.a-1)=s3.g AND s1.e=s3.f) OR
(s1.a=1 AND s3.g=12 AND s3.f=2019) ORDER BY CONCAT(e,'-',a)