leetcode上的blog
有下表。
+---------+------------+ | student | class | +---------+------------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+------------+
统计选课人数大于5的科目。
输出
:
+---------+ | class | +---------+ | Math | +---------+
解决:
第一种:
第一步:
SELECT class, COUNT(DISTINCT student) FROM courses GROUP BY class ;
output:
| class | COUNT(student) | |----------|----------------| | Biology | 1 | | Computer | 1 | | English | 1 | | Math | 6 |
第二步:
SELECT class FROM (SELECT class, COUNT(DISTINCT student) AS num FROM courses GROUP BY class) AS temp_table WHERE num >= 5 ;第二种:
Mysql
SELECT class FROM courses GROUP BY class HAVING COUNT(DISTINCT student) >= 5 ;