Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname,T#) 课程表SC(S#,C#,score) 成绩表 SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分 FROM SC L ,SC AS R WHERE L.C# = R.C# and L.score = (SELECT MAX(IL.score) FROM SC AS IL,Student AS IM WHERE L.C# = IL.C# and IM.S#=IL.S# GROUP BY IL.C#) AND R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.C# = IR.C# GROUP BY IR.C# 详见: http://blog.csdn.net/maco_wang/article/details/6281484第18个。
select S.Sname,n.最高分,n.最低分 from S LEFT JOIN sc on s.sno=sc.sno left join (select cno,MAX(Score)as 最高分,MIN(Score) as 最低分 from SC group by cno) n on sc.cno=n.cno
select S.Sname,n.最高分,n.最低分 from S LEFT JOIN sc on s.sno=sc.sno left join (select cno,MAX(Score)as 最高分,MIN(Score) as 最低分 from SC group by cno) n on sc.cno=n.cno
SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分 FROM SC L ,SC AS R WHERE L.C# = R.C# and L.score = (SELECT MAX(IL.score) FROM SC AS IL,Student AS IM WHERE L.C# = IL.C# and IM.S#=IL.S# GROUP BY IL.C#) AND R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.C# = IR.C# GROUP BY IR.C# 详见:
http://blog.csdn.net/maco_wang/article/details/6281484第18个。
left join (select cno,MAX(Score)as 最高分,MIN(Score) as 最低分
from SC
group by cno) n on sc.cno=n.cno
left join (select cno,MAX(Score)as 最高分,MIN(Score) as 最低分
from SC
group by cno) n on sc.cno=n.cno