SQL查询非最大值的所有记录

SELECT   *
FROM  score s1
WHERE sno IN (
        SELECT sno
        FROM score
        GROUP BY sno
        HAVING count(cno) > 1)
AND DEGREE <> (
        SELECT max(degree)
        FROM score s2
        WHERE s1.cno = s2.cno
        GROUP BY cno
)

-- 试试看
select t1.sno,t1.cno,t1.score from score as t1 inner join (select max(degree) as degree,cno from score  group by cno) as t2
on t1.cno = t2.cno and t1.degree < t2.degree 
group by t1.sno,t1.cno,t1.score having count(*) > 1

SELECT a.sno,a.cno,a.degree FROM score a LEFT JOIN
(SELECT sno,max(degree) as max_d FROM score WHERE sno in (SELECT sno from score GROUP BY sno HAVING count(sno)>=2 ) GROUP BY sno) b
on a.sno=b.sno WHERE a.degree != max_d;
测试过是对的