有成绩表如下,表名称是sc,字段s_id是学生编号,c_id是课程编号,score是课程成绩:
一下是sql代码:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`s_id` int(2) UNSIGNED ZEROFILL NULL DEFAULT NULL,
`c_id` int(2) UNSIGNED ZEROFILL NULL DEFAULT NULL,
`score` float(10, 2) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (1, 01, 01, 80.00);
INSERT INTO `sc` VALUES (2, 01, 02, 90.00);
INSERT INTO `sc` VALUES (3, 01, 03, 99.00);
INSERT INTO `sc` VALUES (4, 02, 01, 70.00);
INSERT INTO `sc` VALUES (5, 02, 02, 60.00);
INSERT INTO `sc` VALUES (6, 02, 03, 80.00);
INSERT INTO `sc` VALUES (7, 02, 01, 80.00);
INSERT INTO `sc` VALUES (8, 03, 02, 80.00);
INSERT INTO `sc` VALUES (9, 03, 03, 80.00);
INSERT INTO `sc` VALUES (10, 03, 01, 50.00);
INSERT INTO `sc` VALUES (11, 04, 02, 30.00);
INSERT INTO `sc` VALUES (12, 04, 03, 20.00);
INSERT INTO `sc` VALUES (13, 04, 01, 76.00);
INSERT INTO `sc` VALUES (14, 05, 02, 87.00);
INSERT INTO `sc` VALUES (15, 05, 01, 31.00);
INSERT INTO `sc` VALUES (16, 06, 03, 34.00);
INSERT INTO `sc` VALUES (17, 06, 02, 89.00);
INSERT INTO `sc` VALUES (18, 07, 03, 98.00);
SET FOREIGN_KEY_CHECKS = 1;
having语句是分组后过滤的条件,在group by之后使用,也就是如果要用having语句,必须要先有group by语句。
group by的功能是分组聚合,将多条记录变成比较少的记录,而having的功能是由多变少之后,再变少的过程。另外having后面可以跟多种运算形式,但是运算的结果只能是一个逻辑值(0或者非0的数值)。
题目1:求平均成绩>=60分的学生编号及平均成绩
整体思路如下:
1.看是否有条件,如果条件是从数据库获取数据时的筛选条件,应该用where语句;如果是分组聚合之后的条件,应该用having语句。本题的条件是平均成绩>=60,平均成绩是聚合的结果,应属于聚合后的条件,应该使用having语句;
2.先不考虑having语句,将分组聚合语句写出:select s_id,avg(score) 平均成绩 from sc group by s_id;
3.将过滤条件加在group by后面,完整语句:select s_id,avg(score) 平均成绩 from sc group by s_id having avg(score)>=60;
数据运算的过程如下:
1.根据s_id,把数据分成7各组,2.根据having后面的条件,判断每个组是否满足,3.满足的组输出指定字段,不满足的组不输出。
# 题目2:查询至少两门课程及格的学生学号
分析题意:输出学生学号,条件是至少两门课程及格,从方法论上看,最后输出的结果是小于等于7条的,记录是由多变少再表少的过程,应该用having语句。
另外条件是每位同学及格的课程数量>=2,及格的课程数量可以用sum(score>=60)来表示,score>=60,满足的返回1,不满足的返回0,最后进行求和,得到的是及格的课程数量,sum(score)>=60涉及到聚合,条件应该放在having后面。
最后的语句:select s_id from sc group by s_id having sum(score>=60)>=2;
数据运算的过程如下:1.根据s_id,把数据分成7各组,2.根据having后面的条件,判断score>=60,满足的返回1,不满足返回0,3.针对score>=60的结果进行求和,在和2作比较,sum(score>=60)>=2,满足的返回1,不满足返回0,4.满足的组输出指定字段,不满足的组不输出。
题目3:查询仅学过01和02号课程的学生编号
分析题意:根据方法论,最后结果的记录是小于等于7条的,而成绩表有18条记录,是由多变少再变少的过程,需要使用having语句,先不用考虑having的条件,把前面的语句先写完:select s_id from sc group by s_id;
再考虑having后面的条件,仅学过01和02课程,可以把条件分为3个,一是学过01,而是学过02,三是只学了两门课,满足这三个条件,就说明仅学了01和02。三个条件通过and连接,语句如下:select s_id from sc group by s_id having count(c_id)=2 and sum(c_id=“01”)=1 and sum(c_id=“02”)=1;
满足count(c_id)=2 这个条件说明只学了两门课,满足sum(c_id=“01”)=1这个条件说明学过01号课程,满足sum(c_id=“02”)=1这个条件说明学过02号课程。
转载于知乎文章:https://www.zhihu.com/column/c_1270739862048739328