一个SQL查询？？

[xq1981]

为管理业务培训信息，建立了三个表：
S(S#,SN,SD,SG,SA)S#,SN,SD,SA,SG,SY分别代表学号，学员姓名，所属单位，学员年龄，薪资，进厂日期
C(C#,CN)C#,CN分别代表课程编号，课程名称
SC(S#,C#,G)S#,C#,G分别代表学号，所选的课程编号，学习成绩
查询为课程超过4门，平均成绩高于95分且单科成绩不低于90的员工的薪资加(现有薪资的)5%？？？？

[xq1981]

谢谢各位，散分啦！

[user_xj]

[Quote=引用 9 楼 dreamloster 的回复:]
SQL code

UPDATE S SET SA=SA*1.05 WHERE S# IN
(
SELECT S# FROM SC WHERE G>=90 GROUP BY S# HAVING AVG(G)>=95 AND COUNT(C#)>=4
)
[/Quote]
上面的的不错！
看了半天 也不晓得LZ要的结果是查询还是修改~~

[sanshenshi]

http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html?99102
可以参考下上面这个

[Mr_Nice]

[Quote=引用 3 楼 sanshenshi 的回复:]

http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html?99102
可以参考下上面这个
[/Quote]

up

[rmljoe]

select s#,sn,sg,sg*1.05 newsg
from s
where 
s# in (select s# from sc group by s# having count(1) > 4 and avg(G) > 95) -- 超4门，平均成绩高于95
and 
not exists(select 1 from sc where sc.s# = s.s# and g < 90) -- 单科成绩不低于90

[small_agile]

UPDATE STUDENT SET SNAME = 'S' FROM STUDENT,COURSE,SC WHERE STUDENT.S# = SC.S# AND SC.C# = COURSE.C# AND STUDENT.S# IN(SELECT S# FROM SC GROUP BY S# HAVING COUNT(*)>4)
AND STUDENT.S# IN (SELECT S# FROM SC GROUP BY S# HAVING AVG(SCORE)>30) AND NOT EXISTS (SELECT * FROM COURSE WHERE SCORE<90)
WHERE SCORE<90)

[small_agile]

UPDATE STUDENT SET SNAME = 'S' FROM STUDENT,COURSE,SC WHERE STUDENT.S# = SC.S# AND SC.C# = COURSE.C# AND STUDENT.S# IN(SELECT S# FROM SC GROUP BY S# HAVING COUNT(*)>4)
AND STUDENT.S# IN (SELECT S# FROM SC GROUP BY S# HAVING AVG(SCORE)>30) AND NOT EXISTS (SELECT * FROM COURSE WHERE SCORE<90)

我也不知道行不行,請高手檢查檢查!!多多指教.

[small_agile]

UPDATE S SET SG=SG+SG*0.05 FROM STUDENT,COURSE,SC WHERE STUDENT.S# = SC.S# AND SC.C# = COURSE.C# AND STUDENT.S# IN(SELECT S# FROM SC GROUP BY S# HAVING COUNT(*)>4)
AND STUDENT.S# IN (SELECT S# FROM SC GROUP BY S# HAVING AVG(SCORE)>30) AND NOT EXISTS (SELECT * FROM COURSE WHERE SCORE<90)

上面的是我在另外一張表裡的測試程序,改改字段就可以了!!

[dreamloster]

UPDATE S SET SA=SA*1.05 WHERE S# IN
(
SELECT S# FROM SC WHERE G>=90 GROUP BY S# HAVING AVG(G)>=95 AND COUNT(C#)>=4
)