[求sql语句]统计一个表，用统计结果批量更新另外一个表。解决办法_Sql Server

[求sql语句]统计一个表，用统计结果批量更新另外一个表。
学生表Students，学生选修课程的成绩表CourseScore。
现在需要跟据CourseScore里的数据进行统计以后更新Students表的TotalScore字段（即根据学生选修课程的成绩更新学生的总分）。
为了方便大家，我把sql语句写出来，如下所示：

SQL code

create table Students(    StudentID int identity(1,1) primary key,    Name nvarchar(32) not null,    TotalScore int not null);insert into Students(Name, TotalScore) values('刘德华', 0);insert into Students(Name, TotalScore) values('张学友', 0);insert into Students(Name, TotalScore) values('郭富城', 0);create table CourseScore(    StudentID int not null,    CourseID int not null,    Score int not null);insert into CourseScore values(1 , 1, 80);insert into CourseScore values(1 , 2, 80);insert into CourseScore values(1 , 3, 80);insert into CourseScore values(2 , 2, 95);insert into CourseScore values(2 , 3, 85);insert into CourseScore values(3 , 1, 100);insert into CourseScore values(3 , 2, 75);

谢谢大家！

------解决方案--------------------

SQL code

--更新update Students set TotalScore=b.c1from Students a left join (select StudentID,sum(Score) as c1 from CourseScore group by StudentID) bon a.StudentID=b.StudentID--查看select * from Students/*StudentID   Name                             TotalScore----------- -------------------------------- -----------1           刘德华                              2402           张学友                              1803           郭富城                              175*/
------解决方案--------------------
SQL codeupdate a set a.TotalScore=b.sfrom Students ajoin (select StudentID,sum(Score) s from CourseScore group by StudentID) bon a.StudentID=b.StudentIDselect * from Students/**StudentID   Name                             TotalScore----------- -------------------------------- -----------1           刘德华                              2402           张学友                              1803           郭富城                              175(3 行受影响)**/
------解决方案--------------------
SQL code--简化一下--更新update a set TotalScore=(select sum(Score) from CourseScore where StudentID=a.StudentID) from Students a
------解决方案--------------------
update Students set TotalScore = isnull((select sum(Score) from CourseScore where StudentID = t.StudentID),0) from Students t
------解决方案--------------------
SQL codecreate table Students(    StudentID int identity(1,1) primary key,    Name nvarchar(32) not null,    TotalScore int not null);insert into Students(Name, TotalScore) values('刘德华', 0);insert into Students(Name, TotalScore) values('张学友', 0);insert into Students(Name, TotalScore) values('郭富城', 0);create table CourseScore(    StudentID int not null,    CourseID int not null,    Score int not null);insert into CourseScore values(1 , 1, 80);insert into CourseScore values(1 , 2, 80);insert into CourseScore values(1 , 3, 80);insert into CourseScore values(2 , 2, 95);insert into CourseScore values(2 , 3, 85);insert into CourseScore values(3 , 1, 100);insert into CourseScore values(3 , 2, 75);goupdate Students set TotalScore = isnull((select sum(Score) from CourseScore where StudentID = t.StudentID),0) from Students t select * from Students/*StudentID   Name                             TotalScore  ----------- -------------------------------- ----------- 1           刘德华                              2402           张学友                              1803           郭富城                              175（所影响的行数为 3 行）*/drop table Students, CourseScore