问题
有一个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:
学生在每个课中不应被重复计算。
准备
create database leecote596;
use leecote596;
create table courses (
student varchar(20),
class varchar(20));
insert into courses values
('A','Math'),
('B','English'),
('C','Math'),
('D','Biology'),
('E','Math'),
('F','Computer'),
('G','Math'),
('H','Math'),
('I','Math');
解答
#方法一：
SELECT class
FROM
(SELECT class, COUNT(DISTINCT student) AS num FROM courses GROUP BY class) AS temp_table
WHERE num >= 5;
#方法二：
SELECT class FROM courses
GROUP BY class HAVING COUNT(DISTINCT student) >= 5;
