sql学习记录

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 ;