596. Classes More Than 5 Students

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

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

GROUP BY + Subquery

建表语句：

create table 表名( //表名一般以 t_ 或 tbl_ 开始，且不能以数字开头

字段名1 数据类型,

字段名2 数据类型,

…

);

可能存在重复记录

Solution

mysql> select * from t_596;
+---------+----------+
| student | class    |
+---------+----------+
| A       | Math     |
| B       | English  |
| C       | Math     |
| D       | Biology  |
| E       | Math     |
| F       | Computer |
| G       | Math     |
| H       | Math     |
| I       | Math     |
+---------+----------+
9 rows in set (0.00 sec)

mysql> select * from t_596 group by class;
+---------+----------+
| student | class    |
+---------+----------+
| A       | Math     |
| B       | English  |
| D       | Biology  |
| F       | Computer |
+---------+----------+
4 rows in set (0.00 sec)

mysql> select count(DISTINCT student),class from t_596 group by class;
+----------------+----------+
| count(student) | class    |
+----------------+----------+
|              6 | Math     |
|              1 | English  |
|              1 | Biology  |
|              1 | Computer |
+----------------+----------+
4 rows in set (0.00 sec)

mysql> select class from (select count(distinct student) num,class from t_596 group by class) t where num>=5;
+-------+
| class |
+-------+
| Math  |
+-------+
1 row in set (0.00 sec)