此场景多用于题目所示的需求；也常用于降低某些历史表中的数据行数，或类似场景。以下以归档学生成绩为例。
Studentscores 为归档成绩表，scores为详细成绩表，两表依靠xh(学号)进行连接。
##mysql5.6及以后，mysql这款互联网数据库，为啥是互联网数据库，因为既免费又好上线。函数啥啥的真的好用，更新语句的写法也是各种各样，几乎是只要你能想的通，它就能办到。
#考生成绩表
create table studentscores (
xh varchar(10) primary key not null,
xm varchar(10) not null,
scores varchar(1000)
);
#成绩表
create table scores(
xh varchar(10) not null,
kmdm varchar(10) not null,
kmmc varchar(20) not null,
score float(6,2) not null,
primary key(xh,kmdm)
);
#插入成绩
insert into studentscores(xh,xm,scores) values ('1001','张3','');
insert into studentscores(xh,xm,scores) values ('1002','李3','');
select * from studentscores order by xh;
insert into scores (xh,kmdm,kmmc,score) values('1001','01','语文',60.5);
insert into scores (xh,kmdm,kmmc,score) values('1001','02','数学',70.5);
insert into scores (xh,kmdm,kmmc,score) values('1001','03','英语',99);
insert into scores (xh,kmdm,kmmc,score) values('1002','01','语文',98);
insert into scores (xh,kmdm,kmmc,score) values('1002','02','数学',81);
insert into scores (xh,kmdm,kmmc,score) values('1002','03','英语',62);
select * from scores order by xh,kmdm;
#将scores字符串聚合查询结果集作为新表更新到studentscores
#做法1
update studentscores a,(select xh,group_concat(kmdm,'-',kmmc,':',score) scores from scores group by xh) b
set a.scores=b.scores
where a.xh=b.xh;
#做法2，与做法1比孰优孰劣，请留言讨论
update studentscores a cross join (select xh,group_concat(kmdm,'-',kmmc,':',score) scores from scores group by xh) b on(a.xh=b.xh)
set a.scores=b.scores;
#显示结果
select * from studentscores;
--Oracle 11G.2.0.4及以后,大O哥作为行业老大，还真是牛B，函数库绚丽，具体做法如下。
create table studentscores (
xh varchar2(10) primary key not null,
xm varchar2(10) not null,
scores varchar(1000)
);
#成绩表
create table scores(
xh varchar2(10) not null,
kmdm varchar2(10) not null,
kmmc varchar2(20) not null,
score number(6,2) not null,
constraint scores_pk primary key(xh,kmdm)
);
insert into studentscores(xh,xm,scores) values ('1001','张3','');
insert into studentscores(xh,xm,scores) values ('1002','李3','');
commit;
select * from studentscores order by xh;
insert into scores (xh,kmdm,kmmc,score) values('1001','01','语文',60.5);
insert into scores (xh,kmdm,kmmc,score) values('1001','02','数学',70.5);
insert into scores (xh,kmdm,kmmc,score) values('1001','03','英语',99);
insert into scores (xh,kmdm,kmmc,score) values('1002','01','语文',98);
insert into scores (xh,kmdm,kmmc,score) values('1002','02','数学',81);
insert into scores (xh,kmdm,kmmc,score) values('1002','03','英语',62);
commit;
select * from scores order by xh,kmdm;
#O记的字符串聚合函数listagg使用方法很多，以后会有介绍。及其强烈要求使用merge进行双表联合更新，好处第一可以装大象，第二是准确。
merge into studentscores a using(select xh,listagg(kmdm||'-'||kmmc||':'||score,',') within group(order by xh,kmdm) scores from scores group by xh) b on (a.xh=b.xh)
when matched then
update set a.scores=b.scores;
commit;
#显示结果
select * from studentscores;
--SQL Server2K5及以后，案例使用的是SQL Server2012，别再提2K，什么年代了还用2K。这里有一个问题SQL2K5至2K17之前没有字符串聚合函数，可怎么办呢，有个曲线的方法，就是使用for xml构造xml字符串的方法。这方法其实挺难用的。听说2K17有了聚合函数String_Agg()。
#考生成绩表
create table studentscores (
xh varchar(10) primary key not null,
xm varchar(10) not null,
scores varchar(1000)
);
#成绩表
create table scores(
xh varchar(10) not null,
kmdm varchar(10) not null,
kmmc varchar(20) not null,
score NUMERIC(6,2) not null,
PRIMARY KEY CLUSTERED (xh, kmdm)
);
insert into studentscores(xh,xm,scores) values ('1001','张3','');
insert into studentscores(xh,xm,scores) values ('1002','李3','');
select * from studentscores order by xh;
insert into scores (xh,kmdm,kmmc,score) values('1001','01','语文',60.5);
insert into scores (xh,kmdm,kmmc,score) values('1001','02','数学',70.5);
insert into scores (xh,kmdm,kmmc,score) values('1001','03','英语',99);
insert into scores (xh,kmdm,kmmc,score) values('1002','01','语文',98);
insert into scores (xh,kmdm,kmmc,score) values('1002','02','数学',81);
insert into scores (xh,kmdm,kmmc,score) values('1002','03','英语',62);
select * from scores order by xh,kmdm;
--到了聚合更新的地方了，Sql Server不知道怎么想的，用这个数据库的人从来没有字符串聚合的需求吗？2016年以前出的各个版本的都没有这个函数，只能用费劲的折中的干法，费事费劲。做到这的时候才发现自己给自己挖了个坑，没办法跳吧。
--做法1，此种做法是为了符合sql的标准事也可以说是抄袭的O记的。各位看官。
merge into studentscores a using
(select a.xh,replace(replace((select kmdm+'-'+kmmc+':'+cast(score as varchar) a from scores b where b.xh=a.xh for xml auto),'',',') scores
from scores a
group by a.xh) b on (a.xh=b.xh)
when matched then
update set a.scores=b.scores;
--各位看官看到了上面的表b的写法了吗？变态不？
--做法2，其实就是类似mysql的做法，但就是很变态，set后面字段必须加上表全名，不能使用别名。
update studentscores set studentscores.scores=b.scores
from studentscores a,(select a.xh,replace(replace((select kmdm+'-'+kmmc+':'+cast(score as varchar) a from scores b where b.xh=a.xh for xml auto),'',',') scores
from scores a
group by a.xh) b
where a.xh=b.xh;
select * from studentscores order by xh;
不管是那种方法，都不优雅，都不灵活，而且看到了吗，每行聚合字符串的尾巴上还多个’,’，如果必须去除那就再截取一下吧。
综合以上，O记最优雅，mysql最直接，SQL Server就如它的名字一样本身就是个bug。不过都能实现。以上涉及的技术点是，多表联合更新，字符串聚合，表创建，主键创建等等。每种数据库都标明了版本，实际操作请注意版本。
