sql请列出学生数大于等于5的课程 distinct去重

leetcode:

题目：There is a table courses with columns: student and class

Please list out all classes which have more than or equal to 5 students.

For example, the table:

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

Should output:

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

 Note:

The students should not be counted duplicate in each course.

需要注意的就是表中可能会出现重复数据，此处我们使用distinct去重。

解题思路：

1. 先从表courses中查找出每个课程的学生人数，并使用distinct去除重复的数据。

2.将返回数据，新增列count 存到一个新表中

3.从中筛选出count值大于等于5的class

 

select class from

(select class ,count( distinct student) as count from courses group by class )as c
where count >= 5