SQL求出每门课程成绩都大于80分的学生姓名

表A

name	subject	score
zhangsan	math	80
zhangsan	chinese	90
wangwu	math	70
wangwu	chinese	85
aa	math	90
aa	chinese	95

 

                                              最后结果:aa

思路一：比较复杂

1.对表A的name分组，计算count

2.对表A的name分组where score>80 ，计算count

3.用1的结果去左连接2的结果，当count相等即可。

select name

from

(select name,count(name) as num

  from A

group by name)a

left join

(select name,count(name) as num

  from A where score>80

group by name)b

on a.name=b.name where a.num=b.num

 

思路二：反向求解(name只要不在小于80的里面）

先求出分数小于等于80的name作为条件

select name from A  where name not in (select name from A where score<=80)