LeetCode MySQL-596. Classes More Than 5 Students

    AllenLi
    LeetCode MySQL

    Write an SQL query to report all the classes that have at least five students.
    Return the result table in any order.

    寫一個 SQL 查詢有最少五個學生的班級。
可任意排序結果。

    Table: Courses

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

    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.

    Solution:
    1. 選擇標題 class 來自 Courses 的 table。
    2. 以 class 為群組分類。
    3. 有在這個 class 群組中不重複的 student 次數大於等於 5。

    +----------+----------+
| class    | count    |
+----------+----------+
| Biology  | 1        |
| Computer | 1        |
| English  | 1        |
| Math*    | 6        |
+----------+----------+

    Code.1:

    SELECT class FROM Courses
	GROUP BY class
    HAVING COUNT(DISTINCT student) >= 5;

    newCourses

    Related Posts