With Mysql, according to the first field to merge, it means that if the adjacent lines of the first field is the same, will be the second field summation, and merge, however, is not simply to heavy,
Detailed below, thanks a lot!

CodePudding user response:

Thank you all for the great god

CodePudding user response:

Operated by GROUP, a GROUP,

CodePudding user response:

The code is as follows:
Call a stored procedure, the stored procedure using the cursor inside the table inside the numerical circulation,
Compared with the previous data, if the same is Update, if different Insert

- the original data
The 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 the if;
end;
End the if;
end;
End the 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:

 
 - 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 the if; 
end; 
End the if; 
end; 
End the 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;