According to the field to merge adjacent rows-CodePudding

Each prawn, according to the field 1, if the adjacent line are the same, are combined into a line, and the field 2 sum, detailed below, thanks a lot!

CodePudding user response:

It is best to get the code, if you have to do in the database, you can write a stored procedure with cursor traverses

CodePudding user response:

Thank you for your reply

CodePudding user response:

reference 1/f, the heavy rain will respond to:

it is best to get the code, if you have to do in the database, you can write a stored procedure with cursor traversal

Agree to this question and answer ~

CodePudding user response:

I remember how I said once,,,

 
 - the original dataThe create table tests01 (name1 varchar (10), score int) 
Insert into tests01 (name1, score) 
Values (" a ", 10), (" a ", 20), (" b ", 10), (" b ", 30), (" b ", 20), (" b ", 30), (" a ", 10), (" c ", 20), (" c ", 30), (" ding ", 10), (" ding ", 10), (" a ", 40) 
- consolidated data 
The create table tests01_des (id int primary key auto_increment, name1 varchar (10), score int); 
- create a stored procedure 
DELIMITER $$
CREATE PROCEDURE ` StatisticStore ` () 
The BEGIN 
Declare the done INT the DEFAULT 0; 
Declare name2 varchar (10); 
Declare score2 int. 
DECLARE MAXID INT. 
Declare cur_test cursor for the select name1, score from tests01; 
DECLARE the CONTINUE HANDLER FOR the NOT FOUND SET done=1; 

The open cur_test; 
Repeat 
The fetch cur_test into name2 score2; 
If not done then 
The begin 
If ((select count (1) the from tests01_des)=0) then 
Insert into tests01_des (name1, score) values (name2 score2); 
The else 
The begin 
If (select name1 from tests01_des order by id desc limit 1)=name2 then 
The SET MAXID=(select Max (id) from tests01_des); 
The update tests01_des set score=score + score2 where id=MAXID; 
The else 
Insert into tests01_des (name1, score) values (name2 score2); 
end if; 
end; 
end if; 
end; 
end if; 

Repeat until done end; 
The close cur_test; 
END $$
DELIMITER. 

- the call execution 
Call StatisticStore (); 

- view the data 
Select * from tests01; 
Select * from tests01_des;

CodePudding user response:

sunny flowers reference 4 floor response:

how do I remember my answer once,,,

 
 - the original dataThe create table tests01 (name1 varchar (10), score int) 
Insert into tests01 (name1, score) 
Values (" a ", 10), (" a ", 20), (" b ", 10), (" b ", 30), (" b ", 20), (" b ", 30), (" a ", 10), (" c ", 20), (" c ", 30), (" ding ", 10), (" ding ", 10), (" a ", 40) 
- consolidated data 
The create table tests01_des (id int primary key auto_increment, name1 varchar (10), score int); 
- create a stored procedure 
DELIMITER $$
CREATE PROCEDURE ` StatisticStore ` () 
The BEGIN 
Declare the done INT the DEFAULT 0; 
Declare name2 varchar (10); 
Declare score2 int. 
DECLARE MAXID INT. 
Declare cur_test cursor for the select name1, score from tests01; 
DECLARE the CONTINUE HANDLER FOR the NOT FOUND SET done=1; 

The open cur_test; 
Repeat 
The fetch cur_test into name2 score2; 
If not done then 
The begin 
If ((select count (1) the from tests01_des)=0) then 
Insert into tests01_des (name1, score) values (name2 score2); 
The else 
The begin 
If (select name1 from tests01_des order by id desc limit 1)=name2 then 
The SET MAXID=(select Max (id) from tests01_des); 
The update tests01_des set score=score + score2 where id=MAXID; 
The else 
Insert into tests01_des (name1, score) values (name2 score2); 
end if; 
end; 
end if; 
end; 
end if; 

Repeat until done end; 
The close cur_test; 
END $$
DELIMITER. 

- the call execution 
Call StatisticStore (); 

- view the data 
Select * from tests01; 
Select * from tests01_des;

The answer is very detailed