[求sql语句]统计一个表，用统计结果批量更新另外一个表。

[fihuang]

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

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);

谢谢大家！

[叶子]

--更新
update Students set TotalScore=b.c1
from Students a left join 
(
select StudentID,sum(Score) as c1 
from CourseScore group by StudentID
) b
on a.StudentID=b.StudentID

--查看
select * from Students
/*
StudentID   Name                             TotalScore
----------- -------------------------------- -----------
1           刘德华                              240
2           张学友                              180
3           郭富城                              175
*/

[百年树人]

update a set a.TotalScore=b.s
from Students a
join (select StudentID,sum(Score) s from CourseScore group by StudentID) b
on a.StudentID=b.StudentID

select * from Students
/**
StudentID   Name                             TotalScore
----------- -------------------------------- -----------
1           刘德华                              240
2           张学友                              180
3           郭富城                              175

(3 行受影响)
**/

[叶子]

--简化一下
--更新
update a set TotalScore=(select sum(Score) from CourseScore where StudentID=a.StudentID) 
from Students a

[fihuang]

看了大家的回复，非常感谢。有个问题请教一下，为何你们的sql语句最后都要加“from ...”

我这样的写法和你们有啥区别不？

update Students
set TotalScore  = (select SUM(Score) from CourseScore where StudentID = Students.StudentID);

[flyfly2008]

这样方法更新值得学习

[dawugui]

update Students set TotalScore = isnull((select sum(Score) from CourseScore where StudentID = t.StudentID),0) from Students t

[dawugui]

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);
go

update Students set TotalScore = isnull((select sum(Score) from CourseScore where StudentID = t.StudentID),0) from Students t 

select * from Students
/*

StudentID   Name                             TotalScore  
----------- -------------------------------- ----------- 
1           刘德华                              240
2           张学友                              180
3           郭富城                              175

（所影响的行数为 3 行）
*/

drop table Students, CourseScore