欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

sql运用:查询各科分数都 > 80 分的三种写法

程序员文章站 2022-06-23 14:33:27
查询各科分数都 > 80 分的三种写法 #创建表 create table student_grade ( s_name varchar (100), course...

查询各科分数都 > 80 分的三种写法

#创建表
create table student_grade (
    s_name varchar (100),
    course varchar (50),
    grade int
);

#插入模拟数据
insert into student_grade value ('a', '数学', 100);
insert into student_grade value ('a', '语文', 60);
insert into student_grade value ('a', '英语', 40);
insert into student_grade value ('b', '数学', 10);
insert into student_grade value ('b', '语文', 10);
insert into student_grade value ('b', '英语', 50);
insert into student_grade value ('v', '数学', 10);
insert into student_grade value ('v', '语文', 100);
insert into student_grade value ('v', '英语', 40);
insert into student_grade value ('c', '数学', 20);
insert into student_grade value ('c', '语文', 30);
insert into student_grade value ('c', '英语', 90);
insert into student_grade value ('d', '数学', 90);
insert into student_grade value ('d', '语文', 90);
insert into student_grade value ('d', '英语', 95);
insert into student_grade value ('e', '数学', 99);
insert into student_grade value ('e', '语文', 97);
insert into student_grade value ('e', '英语', 95);

select * from student_grade;

#查询各科分数都 > 80 分的写法
#方法一
select s_name from student_grade group by s_name having min(grade) > 80;
方法二
select distinct(s_name) from student_grade where s_name not in (select s_name from student_grade where grade <= 80);