sql - group & having example

于 2013-08-29 22:53:13 发布 · 162 阅读

文章标签：

#数据库

db 专栏收录该内容

6 篇文章

订阅专栏

本文将展示如何使用SQL语言，特别是MySQL，来查询数据库中成绩低于60分的课程数量超过两门的学生。通过创建特定的表结构并填充数据，我们将演示如何执行复杂查询，最终得到目标学生的名单。

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