Mysql基础练习题 596.查询至少有5个学生的所有班级 （力扣）

596.查询至少有5个学生的所有班级

建表插入数据：

Create table If Not Exists Courses (student varchar(255), class varchar(255))
Truncate table Courses
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')

思路分析：

查询至少有5个学生的班级，按班级进行分组，用窗口函数over（）新增一列，count（）求每个班级的学生数量，找出数量大于5的即为所求。

画图分析：

代码实现：

with t1 as
    ( select class,count(*) count from courses group by class )
select class from t1 where count>=5;