查询score中选学多门课程的同学中分数为非最高分成绩的记录。

 

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 )　　　　　　--非最高分