通过一个子查询得到学生的SNo,SName，平均成绩，将这个结果插入到表score，这个sql语句怎么写？？

[q191727779]

求下面的SQL语句

要求：通过一个子查询得到学生的学号、姓名、平均成绩，将这个结果插入到表score，其中平均成绩是要算出来的，以下是有关联的表

表SC
SNo CNo SDate Score
103 3-245 2009-05-01 86
105 3-245 2009-06-11 75
109 3-245 2010-06-23 68
103 3-105 2008-10-11 92
105 3-105 2010-10-14 88
109 3-105 2010-10-15 76
101 3-105 2009-10-20 64
107 3-105 2009-10-17 88
108 3-105 2010-10-18 78
101 6-166 2008-05-18 85
107 6-166 2008-06-21 79
108 6-166 2009-06-08 81

Student
SNo SName Sex BirthDate Class Email
108 曾华 男 1982-09-01 95033 [e-mail]
105 匡明 男 1982-10-02 95031 [e-mail]
107 王丽 女 1981-01-23 95033 [e-mail]
101 李军 男 1983-02-20 95033 [e-mail]
109 王芳 女 1982-02-10 95031 [e-mail]
103 陆君 男 1980-06-03 95031 [e-mail]

[五更琉璃]

with t as
(select avg(score) as score,sno from sc group by sno)
insert into score(sno,sname,score)
select student.sno,student.sname,t.score from t join on Student on t.sno=Student.sno

10楼的效率太低，正式给你个

[AcHerat]

insert into score(sno,sname,score)
select a.sno,a.sname,avg(b.score)
from student a join SC b on a.sno = b.sno
--where ...
group by a.sno,a.sname

[q191727779]

不行啊，亲~~~[Quote=引用 1 楼 acherat 的回复:]
SQL code

insert into score(sno,sname,score)
select a.sno,a.sname,avg(b.score)
from student a join SC b on a.sno = b.sno
--where ...
group by a.sno,a.sname
[/Quote]

[UserA_dmin]

 INSERT INTO SCORE(CLASS,SName,SCORE)
 SELECT S.CLASS,S.SName,AVG(C.SCORE) FROM Student S JOIN SC C
 ON S.SNO=C.SNO
 --WHERE
 GROUP BY S.SName,S.CLASS

[q191727779]

亲，是插入sno，sname，AVG_SCORE啊，没有class啊[Quote=引用 3 楼 usera_dmin 的回复:]
SQL code

INSERT INTO SCORE(CLASS,SName,SCORE)
SELECT S.CLASS,S.SName,AVG(C.SCORE) FROM Student S JOIN SC C
ON S.SNO=C.SNO
--WHERE
GROUP BY S.SName,S.CLASS
[/Quote]

[wfkmu]

你可以自己修改一下嘛，正好可以锻炼

[AcHerat]

1楼不行么？为什么？

[q191727779]

不行啊亲，表里没有class的，score表现在是空表，列名只有sno，sname，avg_score，都是要帅选，算出结果才插入到score表中啊！！！[Quote=引用 3 楼 usera_dmin 的回复:]
SQL code

INSERT INTO SCORE(CLASS,SName,SCORE)
SELECT S.CLASS,S.SName,AVG(C.SCORE) FROM Student S JOIN SC C
ON S.SNO=C.SNO
--WHERE
GROUP BY S.SName,S.CLASS
[/Quote]

[q191727779]

是求每个学生所对应的成绩的平均成绩，结果插入到表score中，你的语句执行结果是插入了重复的学生的平均成绩，而且平均成绩不对[Quote=引用 6 楼 acherat 的回复:]
1楼不行么？为什么？
[/Quote]

[AcHerat]

select a.sno,a.sname,avg(b.score) score into #tb
from student a join SC b on a.sno = b.sno
--where ...
group by a.sno,a.sname

insert into score(sno,sname,score)
select *
from #tb t
where not exists (select 1 from score where sno = t.sno)

update a
set a.score = b.score
from score a join #tb b on a.sno = b.sno

drop table #tb

[五更琉璃]

insert into score(sno,sname,score)
select sno,sname,(select avg(score) from sc where sc.SNo=student.SNo)
from student 

[elegant87]

[Quote=引用 9 楼 acherat 的回复:]

SQL code

select a.sno,a.sname,avg(b.score) score into #tb
from student a join SC b on a.sno = b.sno
--where ...
group by a.sno,a.sname

insert into score(sno,sname,score)
select *
from #tb t
where ……
[/Quote]
这个没有问题的

[q191727779]

thx~~~[Quote=引用 10 楼 z81434362 的回复:]
SQL code

insert into score(sno,sname,score)
select sno,sname,(select avg(score) from sc where sc.SNo=student.SNo)
from student
[/Quote]

[q191727779]

thx~~~[Quote=引用 10 楼 z81434362 的回复:]
SQL code

insert into score(sno,sname,score)
select sno,sname,(select avg(score) from sc where sc.SNo=student.SNo)
from student
[/Quote]

[q191727779]

亲，还是不行哦，10楼的就可以，不过，thanks all the same~~~[Quote=引用 9 楼 acherat 的回复:]
SQL code

select a.sno,a.sname,avg(b.score) score into #tb
from student a join SC b on a.sno = b.sno
--where ...
group by a.sno,a.sname

insert into score(sno,sname,score)
select *
from #tb t……
[/Quote]