表结构如下:
name | socre | course |
zhangsan | 92 | 语文 |
zhangsan | 67 | 英语 |
zhangsan | 83 | 数学 |
lisi | 59 | 语文 |
lisi | 56 | 英语 |
lisi | 48 | 数学 |
wangwu | 92 | 语文 |
问题:请查询出所有科目分数均在60一下的同学
select name from students group by name having count(name)=count(case when score<60 then name else null end);
第二种写法思想类似,也是要借助case when:
select name from students group by name having count(name)=sum(case when score<60 then 1 else 0 end);