D8-leetcode596. 超过5名学生的课(简单)

有一个courses 表 ，有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如,表:

应该输出:

Note:
学生在每个课中不应被重复计算。

数据准备：
Create table If Not Exists courses (student varchar(255), class varchar(255));

insert into courses (student, class) values ('A', 'Math');
insert into courses (student, class) values ('B', 'English');
insert into courses (student, class) values ('C', 'Math');
insert into courses (student, class) values ('D', 'Biology');
insert into courses (student, class) values ('E', 'Math');
insert into courses (student, class) values ('F', 'Computer');
insert into courses (student, class) values ('G', 'Math');
insert into courses (student, class) values ('H', 'Math');
insert into courses (student, class) values ('I', 'Math');

思路：

select class from 
(select class,count(1) as con from courses
group by class) t 
where t.con>=5;

注意：

• group +having，不能搭配where

select class from courses
group by class
having count(1)>=5;

但需要注意：对每个学生进行去重distinct
比如下列情况：

所以需要加distinct

select class from courses
group by class
having count(distinct student)>=5;