有一个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:
学生在每个课中不应被重复计算。
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/classes-more-than-5-students
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
题解:
第一步:先构建临时表,按class进行分类,求出每组class的总数(注:对student进行去重)
select
count(*) as sum,class
from
courses
group by
class
order by
sum desc;
第二步:从临时表里获取sum > 5 的数据
select
tem.class
from
tem
where
tem.sum >= 5
完整SQL
select
tem.class
from (select
count(distinct(student)) as sum,class
from
courses
group by
class
order by
sum desc
) as tem
where tem.sum >= 5