1.题目
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.
翻译:有一张叫courses的表有 学生列和课程列。
列举出所有有超过或等于5个学生的课程。
注意:学生不能重复
2.思路
复制一张新表,根据class进行分组,选出学生人数(不重复)>=5的人
3.算法
select courses_new.class from
(select student,class from courses)courses_new
group by courses_new.class
having count(distinct courses_new.student)>=5
4.总结
如果不复制新表,直接在原表上同时select和group操作会有错误。