查询平均成绩大于等于90分的学生的学号和平均成绩

where 子句筛选和having子句筛选实例

在分组前筛选使用where子句，分组后筛选使用having子句。

创建表

-- 创建学生课程表
create table tb_record
(
recid 		int auto_increment comment '选课记录编号',
sid 		int not null comment '选课学生',
cid 		int not null comment '所选课程',
seldate     date not null comment '选课时间日期',
score 		decimal(4,1) comment '考试成绩',
primary key (recid),
foreign key (sid) references tb_student (stuid),
foreign key (cid) references tb_course (couid),
unique (sid, cid)
);

导入信息

-- 插入数据
insert into tb_record (sid, cid, seldate, score) values 
(1001, 1111, '2017-09-01', 95),
(1001, 2222, '2017-09-01', 87.5),
(1001, 3333, '2017-09-01', 100),
(1001, 4444, '2018-09-03', null),
(1001, 6666, '2017-09-02', 100),
(1002, 1111, '2017-09-03', 65),
(1002, 5555, '2017-09-01', 42),
(1033, 1111, '2017-09-03', 92.5),
(1033, 4444, '2017-09-01', 78),
(1033, 5555, '2017-09-01', 82.5),
(1572, 1111, '2017-09-02', 78),
(1378, 1111, '2017-09-05', 82),
(1378, 7777, '2017-09-02', 65.5),
(2035, 7777, '2018-09-03', 88),
(2035, 9999, curdate(), null),
(3755, 1111, curdate(), null),
(3755, 8888, curdate(), null),
(3755, 9999, '2017-09-01', 92);

如图所示

– 查询平均成绩大于等于90分的学生的学号和平均成绩

1.首先使用having在分组后进行筛选

SELECT
	sid AS 学号,
	AVG( score ) AS 平均分 
FROM
	tb_record 
GROUP BY
	sid 
HAVING
	AVG( score )>= 90;

这个比较简单，直接在group by 分组后添加having 条件就可以了。

2.使用where 进行筛选

SELECT DISTINCT
	b.sid AS 学号,
	b.a AS 平均分 
FROM
	tb_record e
	INNER JOIN ( SELECT sid, avg( score ) AS a FROM tb_record GROUP BY sid ) b ON e.sid = b.sid 
WHERE
	b.a >= 90;

因为要求平均成绩，我们知道where 后面不允许跟聚合函数，所以我们只能采用子查询，把平均成绩查出来当作一个表，再和这个表链接查询，最后用where 筛选就可以了。