MySQL：查询score中选学一门以上课程的同学中分数为非最高分成绩的记录

在网上找的MySQL练习题中有这么一道题：查询score中选学一门以上课程的同学中分数为非最高分成绩的记录，从网上找了好几种方法，但感觉都有问题，现把经过检验的方法放上来：

表结构：

CREATE TABLE SCORE 
(SNO VARCHAR(3) NOT NULL, 
CNO VARCHAR(5) NOT NULL, 
DEGREE DECIMAL(5,2) NOT NULL) ;

表数据：

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

最终结果如下：

网上找的方法中，下面这个表面上结果是对的，但实际上有问题：

select sno,cno,degree from score

where degree not in (select max(degree) from score group by sno)

and sno in (select sno from score group by sno having count(cno)>1);

上面这个方法因为没有将sno与degree相对应，因此搜索的结果实际上是只要degree不等于85，92，88，91，81，76这几个值就行，为此，验证一下：

create table score(
sno varchar(10),
cno varchar(20),
degree decimal(5,2)
);
insert into score values('1','yuwen',86),('1','shuxue',75),('1','yingyu',99),('2','yuwen',86),('3','yuwen',75),('3','shuxue',62);

结果为：

如果使用上面的方法明显会出错，得出的结果为：

正确方法：

select t1.sno,t2.cno,t2.degree
 from score t2,(select sno,max(degree) as degree  from score group by sno having count(sno)>1 )as t1
  where t1.sno=t2.sno and t2.degree<t1.degree;

得出结果：

原题使用该方法得出的结果为：

注：本文使用的是MySQL 5.7 自带的workbench。