有一个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 |
Note:
学生在每个课中不应被重复计算。
思路1
1、先按照class进行分组(class是有限的),然后对每个分组进行判断。不通过,缺少对学生class唯一性判断。
select c.class from courses c group by c.class having count(c.student) >= 5;
2、加入子查询,通过。
select c.class from (select distinct * from courses) c
group by c.class having count(c.student) >= 5;
思路2
select c.class from (select distinct * from courses) c
group by c.class having count(c.student) >= 5;