最近工作中,遇到一个问题,需要批量把一个表A中的某一列数据更新到另一张表B中,难点是表A中可能是多列数据要合并为一条数据更新到B表中。感觉还蛮有意思,在这里记录下来。

由于具体的数据内容涉及到公司的产品细节,我把问题转换成更新学生所有选课内容的问题来说明。

任务描述:

数据库中存放了两张表

  • 学生信息表(StuInfo),包含学号,姓名,选修课
  • 学生成绩表(StuScore),包含学号,选修课名字,成绩

StuInfo表

id name elective
1001 Tom
1002 Peter
1003 Jack
2001 Rose

StuScore表

id elective score
1001 绘画 93
1002 绘画 86
1002 音乐 98
1003 电影 92

现在需要,批量将StuScore表中的elective更新到StuInfo表中,如果同一个学生选修多门课,多门课之间用“,”拼接起来。

解决思路

下面的解决方案,都是在SQL Server中测试的,MySql或者Oracle数据库,可以根据思路,请自行搜索相应的sql语句。

第一步:假设StuInfo表中待更新的列在StuScore表中只有一条记录,更新数据只需要

1
2
3
4
5
UPDATE A 
SET elective = B.elective
FROM StuInfo A
INNER JOIN StuScore B
ON A.id = B.id

第二步:将B表中相同学生的多条记录拼接成一条记录

1
2
3
4
5
SELECT elective = (STUFF ((
SELECT ',' + elective
FROM StuScore
WHERE id = B.id FOR XML PATH('')), 1, 1, ''))
FROM StuScore B GROUP BY id

第三步:将上述两个过程合并

1
2
3
4
5
6
7
8
9
10
11
UPDATE A 
SET elective = B.elective
FROM StuInfo A
INNER JOIN (
SELECT id, elective = (STUFF ((
SELECT ',' + elective
FROM StuScore
WHERE id= C.id FOR XML PATH('')), 1, 1, ''))
FROM StuScore C GROUP BY id
) B
ON A.id = B.id

最终,StuInfo表的内容更新为:

id name elective
1001 Tom 绘画
1002 Peter 绘画,音乐
1003 Jack 电影
2001 Rose