汇总sql 查询最⾼分、最低分、平均分等sql 语句
以mysql为例，汇总sql查询最⾼分、最低分、平均分等sql语句，oracle语法类似，可⾃⾏修改以下sql语句
创建两个数据库表，⼀个学⽣表、⼀个考试成绩表
伦勃朗什么主义数据表数据DROP TABLE IF EXISTS `score `;CREATE TABLE `score ` ( `u_id ` varchar (11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '编号', `object_no ` varchar (11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程编号', `students_no ` varchar (11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学号', `score ` int (11) NULL DEFAULT NULL COMMENT '分数') ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact ;INSERT INTO `score ` VALUES ('1', '100', 's100', 80);INSERT INTO `score ` VALUES ('2', '100', 's200', 59);INSERT INTO `score ` VALUES ('3', '100', 's300', 79);INSERT INTO `score ` VALUES ('4', '200', 's100', 54);INSERT INTO `score ` VALUES ('5', '200', 's200', 96);INSERT INTO `score ` VALUES ('6', '200', 's300', 74);INSERT INTO `score ` VALUES ('7', '300', 's100', 65);INSERT INTO `score ` VALUES ('8', '300', 's200', 80);INSERT INTO `score ` V
ALUES ('9', '200', 's400', 62);INSERT INTO `score ` VALUES ('10', '300', 's400', 56);INSERT INTO `score ` VALUES ('11', '100', 's400', 70);SET FOREIGN_KEY_CHECKS = 1;DROP TABLE IF EXISTS `students `;CREATE TABLE `students ` ( `students_no ` varchar (12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编号', `students_name ` varchar (12) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `sex ` varchar (1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别', PRIMARY KEY (`students_no `) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact ;INSERT INTO `students ` VALUES ('s100', '张三', '1');INSERT INTO `students ` VALUES ('s200', '李四', '2');INSERT INTO `students ` VALUES ('s300', '张三', '1');INSERT INTO `students ` VALUES ('s400', '王五', '1');SET FOREIGN_KEY_CHECKS = 1;
1
2
3
4
5
6
7
8
9
10
mysql面试题汇总11
insert select语句的功能是12
13
14
15
三类容器指的是什么
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
查询各课程的考试学⽣⼈数androidsdk30下载
查询各科成绩最⾼分和最低分
查询每门课程被选修的学⽣数select object_no as '课程编号', count (DISTINCT students_no ) '考试⼈数' from score group by object_no
1select object_no as '课程编号',max (score ) as '最⾼分',min (score ) as '最低分' from score group by object_no
1select object_no as '课程编号', count (DISTINCT students_no ) as '学⽣编号' from score group by object_no
1
查询男⽣、⼥⽣⼈数
查询平均成绩
查询平均成绩⼤于70
分学⽣的学号和平均成绩
查询学⽣考试参与考试课程数量select sex '性别',count (DISTINCT students_no ) '数量' from students group by sex
excel连接oracle数据库1select students_no as '学⽣编号',avg (score ) as '平均成绩' from score group by students_no
1select students_no as '学⽣编号',avg (score ) as '平均成绩' from score group by students_no HAVING avg (score )>70
1select students_no as '学⽣编号',count (object_no ) as '课程编号' from score group by students_no
1
查询考试两门以上课程的学⽣学号
查询同名学⽣名单并统计同名⼈数
计算每门课程的平均成绩
计算每门课程的平均成绩并且平均成绩⼤于等于70分select students_no as '学⽣编号',count (object_no ) as '课程编号' from score group by students_no HAVING count ( object_no )>21select students_name as '学⽣名称',count (students_name ) as '同名数量' from students group by students_name HAVING count (students_name )>1select object_no as '课程编号',avg (score ) as '平均成绩' from score group by object_no
1select object_no as '课程编号',avg (score ) as '平均成绩' from score group by object_no HAVING avg (score )>70
1
查询不及格的课程并按课程号从⼤到⼩排列
查询每门课程的平均成绩，结果按平均成绩降序排列，如果平均成绩相同时，按课程号升序排列select object_no as '课程编号',students_no '学⽣编号',score '分数' from score where score <60 order by object_no desc 1select object_no as '课程编号',avg (score ) as '平均成绩' from score group by object_no order by avg (score ) desc ,object_no asc 1
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论