Excuse me how in the database query above situation?

CodePudding user response:

To see the demand! Don't use in the project

CodePudding user response:

Actual requirements than the trouble, I'm just a simple example problems,

CodePudding user response:

1. Don't know whether I y the demand;
2. If only is the problem, to implement more appropriate in a program, to write a good SQL preach a starting id, an end id, return maximum and minimum value, more flexible;
3. If need in SQL implementation, suggest using a different table holds found out of these data, convenient query again later, I casually built some data, the query results are as follows, don't know if the original poster wants results:
The original data:
The results of the query:
Stored procedure is involved:

 BEGIN 
DECLARE I int the DEFAULT 1; 
DECLARE max_ min_, sum_, avg_ int. 
DECLARE ids_ VARCHAR (20); 
DECLARE the count int the DEFAULT 0; 
SELECT count (*) into the count from t_temp. 
WHILE I + 5 & lt;=(count + 1) DO 
SELECT MAX (t. ` value `), MIN (t. ` value `), SUM (t. ` value `), AVG (t. ` value `) from t_temp t WHERE t.i in d (I, I + 1, I + 2, + 3 I, I + 4) into max_, min_, sum_, avg_; 
The SET ids_=CONCAT_WS (", ", I, I + 1, I + 2, + 3 I, I + 4); 
Insert into t_temp_view (ids, Max, min, sum, avg) values (ids_ max_, min_, sum_, avg_); 
The SET I=I + 1; 
End the WHILE; 
End 

CodePudding user response:

 mysql> Select * from myricemeli; 
+ - + -- -- -- -- -- -- -- -- -- -- - + 
| | id col_value | 
+ - + -- -- -- -- -- -- -- -- -- -- - + 
35 | | 1 | 
68 | | 2 | 
45 | | 3 | 
| | 71 | 
59 5 | | | 
| | 63 | 
7 | | | 25 
8 46 | | | 
58 | | | 9 
+ - + -- -- -- -- -- -- -- -- -- -- - + 
9 rows in the set (0.01 SEC) 

Mysql> Select id, col_value, 
-> (select sum (col_value) from (select col_value from myricemeli where id>=d t.i order by id limit the as 5) b) s, 
-> (select avg (col_value) from (select col_value from myricemeli where id>=d t.i order by id limit 5) b) as a 
-> The from myricemeli t 
-> The where (select count (*) from myricemeli where id> T.i=d) & gt;=5; 
+ - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 
| | id col_value | s | a | 
+ - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 
35 | | 1 | 278 | | 55.6000 
| | 68 | 306 | | 61.2000 
45 | | 3 | 263 | | 52.6000 
| | 71 | 264 | | 52.8000 
59 5 | | | 251 | | 50.2000 
+ - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 
5 rows in the set (0.00 SEC) 

Mysql> 

CodePudding user response:

reference 4 floor ACMAIN_CHM response:

 mysql> Select * from myricemeli; 
+ - + -- -- -- -- -- -- -- -- -- -- - + 
| | id col_value | 
+ - + -- -- -- -- -- -- -- -- -- -- - + 
35 | | 1 | 
68 | | 2 | 
45 | | 3 | 
| | 71 | 
59 5 | | | 
| | 63 | 
7 | | | 25 
8 46 | | | 
58 | | | 9 
+ - + -- -- -- -- -- -- -- -- -- -- - + 
9 rows in the set (0.01 SEC) 

Mysql> Select id, col_value, 
-> (select sum (col_value) from (select col_value from myricemeli where id>=d t.i order by id limit the as 5) b) s, 
-> (select avg (col_value) from (select col_value from myricemeli where id>=d t.i order by id limit 5) b) as a 
-> The from myricemeli t 
-> The where (select count (*) from myricemeli where id> T.i=d) & gt;=5; 
+ - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 
| | id col_value | s | a | 
+ - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 
35 | | 1 | 278 | | 55.6000 
| | 68 | 306 | | 61.2000 
45 | | 3 | 263 | | 52.6000 
| | 71 | 264 | | 52.8000 
59 5 | | | 251 | | 50.2000 
+ - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 
5 rows in the set (0.00 SEC) 

Mysql> 

Good, I want to have a SQL to achieve, but I can't write SQL, so just write a stored procedure;
Don't know why I performed your SQL, just changed a table name, perform error, hope big grant instruction:
My table structure, only an id, value, name of the table is t_temp, why t.i
d complains?

 select id, value, 
(select the sum (value) from (select the value from t_temp where id & gt;=d t.i order by id limit the as 5) b) s, 
(select avg (value) from (select the value from t_temp where id & gt;=d t.i order by id limit 5) b) as a 
The from t_temp t where (select count (*) from t_temp where id> T.i=d) & gt;=5;