596. Classes More Than 5 Students

SQL 50道

内容持续更新…（始于2024年08月03日）

1.问题

Table: Courses

±------------±--------+
| Column Name | Type |
±------------±--------+
| student | varchar |
| class | varchar |
±------------±--------+
(student, class) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates the name of a student and the class in which they are enrolled.

Write a solution to find all the classes that have at least five students.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:
Courses table:
±--------±---------+
| student | class |
±--------±---------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
±--------±---------+
Output:
±--------+
| class |
±--------+
| Math |
±--------+
Explanation:

• Math has 6 students, so we include it.
• English has 1 student, so we do not include it.
• Biology has 1 student, so we do not include it.
• Computer has 1 student, so we do not include it.

2. 解题思路

方法1：

1.GROUP BY class: 按课程名分组，以便对每个课程计算学生的数量。
2.COUNT(student): 计算每个课程的学生数量。
3.HAVING COUNT(student) >= 5: 只选择那些学生数量大于或等于5的课程。

3. 代码

代码1：

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