1 查询每门课程成绩都大于80分学生的学号
数据库 表 student
name score course
A 85 语文
A 75 数学
A 82 英语
B 75 语文
B 89 数学
B 79 英语
天使美眉90 语文
天使美眉100 数学
天使美眉100 英语
请找出每门课程都超过80分的那个人名字的SQL语句
SQL1:
select name from test.stu
group by name
having count(score) =sum(case when score>80 then 1 else 0 end )
SQL2:
select name from stu
group by name
having name not in (
select name from stu
where score <80)
SQL3:
select name from test.stu
group by name
having min(score)>=80
select distinct name from student where name not in (select distinct name from student where fengshu<=80)
group by与distinct 区别
select name from student
group by name
等价于
select distinct name from student
但是group by效率更高都是以group by name后面的列名来分组只是后面的判定要和having连用