欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

sql - group & having example

程序员文章站 2022-06-05 18:21:30
...

problem:

    there is table store course score of students, now write a sql to query student who has at least 2 course failed, means score<60,

 

sql: (mysql)

# drop table
drop table if exists score;

# create table
create table score(
	id integer AUTO_INCREMENT primary key,
	student_num varchar(32) not null,
	course_name varchar(64),
	course_score float,
	unique key(student_num, course_name)
) engine=MYISAM DEFAULT CHARSET=utf8;

# clear data
truncate score;

# prepare data
insert into score(student_num, course_name, course_score)
values
("001", "English", 100),
("001", "Math", 100),
("002", "English", 90),
("002", "Math", 90),
("003", "English", 20),
("003", "Math", 20),
("003", "Music", 20),
("004", "English", 20),
("004", "Math", 20),
("005", "English", 70),
("005", "Math", 30);

# query
select student_num, count(course_name) as cc
from score
where course_score < 60
group by student_num having cc>=2