最近工作中，遇到一个问题，需要批量把一个表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