数据库 查询数据结构课程最高分同学的学号、姓名、平均成绩、选课门数

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

先用子查询 查出课程最高分的同学的学号

再根据学号得到平均分agv和选课门数 count

SELECT student.sno AS `学号`,student.sname AS `姓名`,MAX(grade) AS `最高分`,MIN(grade) AS `最低分`,AVG(grade) AS `平均分`

FROM sc JOIN student ON sc.sno = student.sno GROUP BY sc.sno