用Mysql根據第一個欄位進行合并，意思就是說如果相鄰的行的第一個欄位相同，就將第二個欄位進行求和，并合并。但是，不是簡單的去重。
具體如下圖。萬分感謝！
uj5u.com熱心網友回復：
謝謝各位大神了啊uj5u.com熱心網友回復：
用ＧＲＯＵＰ分組操作。uj5u.com熱心網友回復：
代碼如下：呼叫存盤程序，存盤程序里邊使用游標回圈取表里邊的數值；
與前一個資料比較，如果相同就Update，如果不同就Insert
--原始資料
create table tests01(name1 varchar(10),score int)
insert into tests01(name1,score)
values("甲",10),("甲",20),("乙",10),("乙",30),("乙",20),("乙",30),("甲",10),("丙",20),("丙",30),("丁",10),("丁",10),("甲",40)
--整合后資料
create table tests01_des(id int primary key auto_increment , name1 varchar(10),score int);
--創建存盤程序
DELIMITER $$
CREATE PROCEDURE `StatisticStore`()
BEGIN
declare done INT DEFAULT 0;
declare name2 varchar(10);
declare score2 int;
DECLARE MAXID INT;
declare cur_test cursor for select name1,score from tests01;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
open cur_test;
repeat
fetch cur_test into name2,score2;
if not done then
begin
if ((select count(1) from tests01_des)=0) then
insert into tests01_des(name1,score) values(name2,score2);
else
begin
if (select name1 from tests01_des order by id desc limit 1)=name2 then
SET MAXID=( select max(id) from tests01_des);
update tests01_des set score = score+score2 where id=MAXID;
else
insert into tests01_des(name1,score) values(name2,score2);
end if;
end;
end if;
end;
end if;
until done end repeat;
close cur_test;
END$$
DELIMITER ;
--呼叫執行程序
call StatisticStore();
--查看資料
select * from tests01;
select* from tests01_des;
uj5u.com熱心網友回復：
--原始資料
create table tests01(name1 varchar(10),score int)
insert into tests01(name1,score)
values("甲",10),("甲",20),("乙",10),("乙",30),("乙",20),("乙",30),("甲",10),("丙",20),("丙",30),("丁",10),("丁",10),("甲",40)
--整合后資料
create table tests01_des(id int primary key auto_increment , name1 varchar(10),score int);
--創建存盤程序
DELIMITER $$
CREATE PROCEDURE `StatisticStore`()
BEGIN
declare done INT DEFAULT 0;
declare name2 varchar(10);
declare score2 int;
DECLARE MAXID INT;
declare cur_test cursor for select name1,score from tests01;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
open cur_test;
repeat
fetch cur_test into name2,score2;
if not done then
begin
if ((select count(1) from tests01_des)=0) then
insert into tests01_des(name1,score) values(name2,score2);
else
begin
if (select name1 from tests01_des order by id desc limit 1)=name2 then
SET MAXID=( select max(id) from tests01_des);
update tests01_des set score = score+score2 where id=MAXID;
else
insert into tests01_des(name1,score) values(name2,score2);
end if;
end;
end if;
end;
end if;
until done end repeat;
close cur_test;
END$$
DELIMITER ;
--呼叫執行程序
call StatisticStore();
--查看資料
select * from tests01;
select* from tests01_des;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/22764.html
標籤：基礎類
上一篇：連接SQL 2012出錯
下一篇：急!