select * from score a where sno in (
select sno from score group by sno having count(*)>1) --子查询 选多门课的同学
and degree<(select max(degree) from score b where a.cno=b.cno ) --非最高分
查询score中选学多门课程的同学中分数为非最高分成绩的记录。
原文：http://www.cnblogs.com/sistrong-5880558/p/7738963.html