Leetcode 596. 超过5名学生的课_某小学最近得到一笔赞助-优快云博客

本文介绍了一种使用SQL查询特定课程中超过或等于5名学生的方法。通过一个具体的例子，展示了如何从一个包含学生和课程信息的表中筛选出符合条件的课程。首先，需要创建并填充一个包含学生和他们所上课程的表，然后使用SQL语句进行去重和分组，最后筛选出至少有5名学生选择的课程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

例如,表:

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+

应该输出:

+---------+
| class   |
+---------+
| Math    |
+---------+


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

下面的答案是不正确的，因为表中含有重复数据，需要先对表进行去重操作

---Write your MySQL query statement below
select class from courses group by class having count(class)>=5;

---Write your MySQL query statement below
select class from (select distinct * from courses) as a group by a.class having count(class)>=5