创建一个课程表,包括学生和课程
use runoob;
create table if not exists student(
student varchar(11) not null,
class varchar(11) not null)engine=innodb;
insert into student(student,class)
values('A','Math'),
('B','English'),
('C','Math'),
('D','Biology'),
('E','Math'),
('F','Computer'),
('G','Math'),
('H','Math'),
('I','Math'),
('A','Math');
结果为
下面列出所有超过或等于5名学生的课。学生在每个课中不应被重复计算。从表格中发现学生A重复计算了一次。所以书写一个查询语句
select
student,count(1)
from
`courses`
group by
student
having
count(1)>1;
可以发现学生A重复了两次
select class from runoob.courses group by class having count(distinct student)>=5;