在网上找的MySQL练习题中有这么一道题:
查询score
中选学一门以上课程的同学中分数为非最高分成绩的记录,
从网上找了好几种方法,但感觉都有问题,现把经过检验的方法放上来:
表结构:
CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE DECIMAL(5,2) NOT NULL) ;
(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);
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);
最终结果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/690d7d5a727efec3ca6fdda627bbd189.png)
网上找的方法中,下面这个表面上结果是对的,但实际上有问题:
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);
![](https://i-blog.csdnimg.cn/blog_migrate/7d678d768aca156d1d5ab9554ce87fe2.png)
上面这个方法因为没有将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);
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);
结果为:
![](https://i-blog.csdnimg.cn/blog_migrate/bfb2650e4620f8abc689e36186e4996b.png)
如果使用上面的方法明显会出错,得出的结果为:
![](https://i-blog.csdnimg.cn/blog_migrate/0ce275de62d4ead1abf79c25d3d6a9e8.png)
正确方法:
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;
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;
得出结果:
![](https://i-blog.csdnimg.cn/blog_migrate/32ea60f985d42ba9adb0d26fc61735b7.png)
原题使用该方法得出的结果为:
![](https://i-blog.csdnimg.cn/blog_migrate/8529b98fbafa7dbff96d86e152589986.png)
注:本文使用的是MySQL 5.7 自带的workbench。