经典sql问题——每门课都大于X分

测试数据

create table sql01_score( id int not null auto_increment primary key, name varchar(30) not null comment '学生姓名', course varchar(50) comment '课程名称', grade int comment '成绩' )engine=InnoDB default charset=utf8; 
insert into sql01_score(name, course, grade) values('张三', '语文', 89); 
insert into sql01_score(name, course, grade) values('张三', '数学', 77); 
insert into sql01_score(name, course, grade) values('张三', '计算机', 65); 
insert into sql01_score(name, course, grade) values('李四', '语文', 95); 
insert into sql01_score(name, course, grade) values('李四', '数学', 90); 
insert into sql01_score(name, course, grade) values('李四', '计算机', 88); 
insert into sql01_score(name, course, grade) values('王五', '语文', 66); 
insert into sql01_score(name, course, grade) values('王五', '数学', 85); 
insert into sql01_score(name, course, grade) values('王五', '计算机', 94);

方法1——查找最低分数>=85的学生

select name from sql01_score group by name having min(grade) >= 85;

方法2————查找分数大于>=85的个数与科目数相等的

select name from sql01_score where grade >= 85 group by name having count(grade) = 3;

方法3————排除有成绩小于85的学生

select distinct(name) from sql01_score where name not in (select name from sql01_score where Grade < 85);

学习自CSDN高性能Mysql实战课