求下面的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 [email protected]
105 匡明 男 1982-10-02 95031 [email protected]
107 王丽 女 1981-01-23 95033 [email protected]
101 李军 男 1983-02-20 95033 [email protected]
109 王芳 女 1982-02-10 95031 [email protected]
103 陆君 男 1980-06-03 95031 [email protected]
------解决方案--------------------
- SQL code
select a.sno,a.sname,avg(b.score) score into #tbfrom student a join SC b on a.sno = b.sno--where ...group by a.sno,a.snameinsert into score(sno,sname,score)select *from #tb twhere not exists (select 1 from score where sno = t.sno)update aset a.score = b.scorefrom score a join #tb b on a.sno = b.snodrop table #tb
------解决方案--------------------
- SQL code
insert into score(sno,sname,score)select sno,sname,(select avg(score) from sc where sc.SNo=student.SNo)from student