表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)