在数据库查询中查询各门课程取得最高成绩的学生姓名和成绩

Question

如题所述

Answer 1

select
t.sno,sname,avg(grade)
as
平均分,count(cname)
as
选课门数
from
student
t,sc
c,course
e
where
t.sno=c.sno
and
c.cno=e.cno
and
t.sno=
(select
top
1
t1.sno
from
student
t1,sc
c1,course
e1
where
t1.sno=c1.sno
and
c1.cno=e1.cno
and
e1.cname='数据结构'
order
by
c1.grade
desc)
group
by
t.sno,sname
1.
先用子查询
查出课程最高分的同学的学号
2.
再根据学号得到平均分agv和选课门数
count