sql语句中 having与where区别
HAVING语句通常与GROUP BY语句联合使用,用来过滤由GROUP BY语句返回的记录集。
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
CREATE TABLE `tp5_student` (
`id` int(11) DEFAULT NULL,
`no` int(11) DEFAULT NULL COMMENT '学号',
`course` varchar(255) DEFAULT NULL COMMENT '课程',
`score` int(11) DEFAULT NULL COMMENT '分数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SELECT id,no, COUNT(course) as numcourse, AVG(score) as avgscore
FROM tp5_student
GROUP BY no
SELECT id,no, COUNT(course) as numcourse, AVG(score) as avgscore
FROM tp5_student
GROUP BY no HAVING AVG(score)>=85
SELECT id,no, COUNT(course) as numcourse, AVG(score) as avgscore
FROM tp5_student
WHERE id>3
GROUP BY no