题目：

用一条SQL 语句 查询出每门课都大于80 分的学生姓名，表格样式及数据如下：

image

结果为

image

请至少使用两种方法作答

参考答案：

数据库版本：Server version: 8.0.20 MySQL Community Server - GPL

建表语句

create table dailytest_20200624(
    name varchar(20),
    course varchar(20),
    score int
);

数据准备

insert into dailytest_20200624 values ('张三','语文',81);
insert into dailytest_20200624 values ('张三','数学',75);
insert into dailytest_20200624 values ('李四','语文',76);
insert into dailytest_20200624 values ('李四','数学',90);
insert into dailytest_20200624 values ('王五','语文',81);
insert into dailytest_20200624 values ('王五','数学',100);
insert into dailytest_20200624 values ('王五','英语',91);

查询逻辑-方法1

select
       A.name
from (select
             name,
             min(score) min_score
      from dailytest_20200624
      group by name)A
where A.min_score > 80;

select
    name
from(
select
       name,
       course,
       score,
       row_number() over (partition by name order by score asc) as cn
from dailytest_20200624) A
where A.cn = 1
and A.score > 80;

查询逻辑-方法2

select
    DISTINCT A.name
from dailytest_20200624 A
    left join
    (select name,course,score from dailytest_20200624 where score < 80)B on A.name = B.name
where B.name is null

附：
题目来源：https://mp.weixin.qq.com/s/W90Hf-dwLdJxbjtx2Fy3Cg