UPDATE sc a
SET (a.score) =
(SELECT AVG(b.score)
FROM sc b,
course c,
teacher d
WHERE a.s# = b.s#
AND a.c# = b.c#
AND c.c# = b.c#
AND c.t# = d.t#
AND d.tname = '叶平')
WHERE EXISTS (SELECT 1
FROM sc b,
course c,
teacher d
WHERE a.s# = b.s#
AND a.c# = b.c#
AND c.c# = b.c#
AND c.t# = d.t#
AND d.tname = '叶平')
SET (a.score) =
(SELECT AVG(b.score)
FROM sc b,
course c,
teacher d
WHERE a.s# = b.s#
AND a.c# = b.c#
AND c.c# = b.c#
AND c.t# = d.t#
AND d.tname = '叶平')
WHERE EXISTS (SELECT 1
FROM sc b,
course c,
teacher d
WHERE a.s# = b.s#
AND a.c# = b.c#
AND c.c# = b.c#
AND c.t# = d.t#
AND d.tname = '叶平')
merge into sc
using (select sc.c# C#, avg(sc.score) SCORE
from sc
where sc.c# in ('002', '005')
group by sc.c#) sc2
on (sc.c# = sc2.c#)
when matched then
update set sc.score = sc2.score;
SET (a.score) =
(SELECT AVG(b.score)
FROM sc b
WHERE a.c# = b.c#)
WHERE EXISTS (SELECT 1
FROM sc b,
course c,
teacher d
WHERE a.s# = b.s#
AND a.c# = b.c#
AND c.c# = b.c#
AND c.t# = d.t#
AND d.tname = '叶平')
SET (a.score) =
(SELECT AVG(b.score)
FROM sc b
WHERE a.c# = b.c#)
WHERE EXISTS (SELECT 1
FROM sc b,
course c,
teacher d
WHERE a.s# = b.s#
AND a.c# = b.c#
AND c.c# = b.c#
AND c.t# = d.t#
AND d.tname = '叶平')
感谢斑斑回答,是是正确的。
像我3L那样做法求出了叶平的课程和平均成绩,有办法实现更新吗?
但是如果课程多的话就有点繁琐了。
比如知道02的平均分是53.33
update sc set score==53.33 where c#='002'其实#4的方法也不错,你自己稍微改一下就可以了(在子查询中将teacher表加进去进行连接,然后将条件改动一下即可),人家给了你一种思路
using ( select A.C#, avg(A.Score) as Score
from SC A,
Course B,
Teacher C
where B.C# = A.C#
and C.T# = B.T#
and C.Tname = '叶平'
group by A.C# ) V
on ( V.C# = U.C# )
when matched then
update set U.Score = V.Score;