Quote: refer to the second floor taotao148 response:

Quote: refer to 1st floor AHUA1001 response:

SELECT date of 5 a, t. MAX maximum (t. 2 a), MIN (t. 2 a) minimum value FROM 001 copy T WHERE t. 5 a & gt;=? AND t. 5 a & lt;=? GROUP BY t. 5 a;

Thank you, but it seems still won't do, because is to extract the maximum and minimum value of each month, 120 months for 10 years, the maximum and minimum value of each month,

In modified into the,
SELECT DATE_FORMAT (5 a, t. '% Y - % m) in MAX maximum (t. 2 a), MIN (t. 2 a) minimum value FROM 001 copy T WHERE t. 5 a & gt;=? AND t. 5 a & lt;=? GROUP BY DATE_FORMAT (5 a, t. '% Y - % m);

Thank you, have a lot of help, if you will be the time corresponding to the maximum and minimum point of the query, and came to do?

CodePudding user response:

If two different time points in the same month, the storage of 2 a (storage of the data) is the same, and or the maximum or minimum value, how to display your time points,

CodePudding user response:

reference 5 floor AHUA1001 reply:

if two different time points in the same month, the storage of 2 a (storage of the data) is the same, and or the maximum or minimum value, how to display your time points,

Don't mean to, because I am a little white, so the problem may be childish, just personal want such result, the monthly maximum and minimum value can be selected, at the same time, the time of the maximum and the minimum corresponding to synchronous screening,
The data storage can be 2 a copy into 2 a_1, then 2 a maximum screening, screening minimum 2 a_1, 5 a is date remains the same,

CodePudding user response:

2 a copy of data storage can be into 2 a_1, then 2 a screening of the maximum, minimum 2 a_1 screening, is 5 a date remains the same,
The final results show like this,

CodePudding user response:

According to your requirements, can be directly execute the statement,
SELECT @ id:=@ id + 1 id, 2 a, 2 a_1, t. t. t. 5 a the FROM (SELECT MAX (2 a) t. ` 2 a `, MIN (2 a) t. ` 2 a_1 `, DATE_FORMAT (t. 5 a, '% Y - % m) ` 5 a `, @ id:=0 FROM 001 copy t WHERE t. 5 a & gt;=? AND t. 5 a & lt;=? GROUP BY DATE_FORMAT (5 a, t. '% Y - % m)) T;

CodePudding user response:

Table 001 copy has 10 years 120 months of data, the primary key id, 2 a is data, 5 a is the date, the following figure

Now want to get the maximum and minimum value of each month, the following figure

Use method is to this, now only a month to do a month, is there any way, once per month to 120 months of maximum and minimum screening out together, while retaining the corresponding 2 a 5 a
SELECT * FROM 001 copy ` ` WHERE ` a `=2 (SELECT MAX (` 2 a `) FROM 001 copy ` ` WHERE YEAR=2019 (5 a) AND the MONTH (5 a)=4)
The or ` a `=2 (SELECT MIN (` 2 a `) FROM 001 copy ` ` WHERE YEAR=2019 (5 a) AND the MONTH (5 a)=4).
If 2 a take the maximum and the minimum is not convenient at the same time, can also be copied to the 2 a 2 a_1, a screening of the maximum a screening of the minimum, as long as you keep data and data corresponding to the date of screening out at the same time,

CodePudding user response:

If the maximum in the same month, there are multiple date, should be how to display,

CodePudding user response:

SELECT DATE_FORMAT (5 a, t. '% Y - % m) in MAX maximum (t. 2 a), MIN (t. 2 a) minimum value FROM 001 copy T WHERE t. 5 a & gt;=? AND t. 5 a & lt;=? GROUP BY DATE_FORMAT (5 a, t. '% Y - % m')

5 a, b) on DATE_FORMAT (a. '% Y - % m)=b. in

The where (a. a=2 b. The maximum or a. 2 a=b. Minimum)

CodePudding user response:

If add a virtual columns, each selected date of the corresponding month, and then grouping queries will be a little bit better? As for the maximum or minimum value corresponding to multiple date, the date can get maximum, minimum date, or a random day? This depends on the specific requirements

CodePudding user response:

I think you can use the method of grouping, will be in the group

CodePudding user response:

references to the tenth floor AHUA1001 response:

if the maximum value in the same month, there is more than one date, how should, according to

According to the actual situation of the table, a data corresponding to a date,

CodePudding user response:

refer to 12 floor stelf reply:

if add a virtual columns, each of the selected date of the corresponding month, and then grouping queries will be a little bit better? As for the maximum or minimum value corresponding to multiple date, the date can get maximum, minimum date, or a random day? This depends on the specific requirements of

A data corresponding to a date, but there are very afraid of data is the same, result in the error date decisions,

CodePudding user response:

Thanks for you help and prompt, and the two schemes, is not perfect, the first is a very stupid method,
(SELECT * FROM 001 copy ` ` WHERE
` a `=2 (SELECT MAX (` 2 a `) FROM 001 copy ` ` WHERE YEAR=2019 (5 a) AND the MONTH (5 a)=1)
The or ` a `=2 (SELECT MIN (` 2 a `) FROM 001 copy ` ` WHERE YEAR=2019 (5 a) AND the MONTH (5 a)=1)
The or ` a `=2 (SELECT MAX (` 2 a `) FROM 001 copy ` ` WHERE YEAR=2019 (5 a) AND the MONTH (5 a)=2)
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull