– 查询出各个课程对于的平均分,从高到低排序,并展示对应的科目老师
select avg(sc.score),course.Cname,teacher.Tname
from course,sc,teacher
where course.CNo=sc.CNo and course.Tno=teacher.TNo
group by course.Cname having avg(sc.score)
order by avg(sc.score) desc
– 查询总分排名第二的学生姓名和总分数
select * from
(select student.Sname as sname,SUM(sc.score) as sum_score from sc,student where sc.SNo=student.SNO group by student.Sname order by SUM(sc.score) desc) a
where
(select count(*) from
(select student.Sname as sname,SUM(sc.score) as sum_score from sc,student where sc.SNo=student.SNO group by student.Sname order by SUM(sc.score) desc) b
where a.sum_score<=b.sum_score)=2
跟部门绩效第二类似,自连接 使score进行大小对比,目前还没找到比较简便的写法