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` VALUES (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;

数据表数据

查询各课程的考试学生人数

select object_no as 课程编号, count(DISTINCT students_no) 考试人数 from score group by object_no

查询各科成绩最高分和最低分

select object_no as 课程编号,max(score) as 最高分,min(score) as 最低分 from score group by object_no

查询每门课程被选修的学生数

select object_no as 课程编号, count(DISTINCT students_no) as 学生编号 from score group by object_no

查询男生、女生人数

select sex 性别,count(DISTINCT students_no) 数量 from students group by sex

查询平均成绩

select students_no as 学生编号,avg(score) as 平均成绩 from score group by students_no

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

select students_no as 学生编号,avg(score) as 平均成绩 from score group by students_no HAVING avg(score)>70

查询学生考试参与考试课程数量

select students_no as 学生编号,count(object_no) as 课程编号  from score   group by students_no

查询考试两门以上课程的学生学号

select students_no as 学生编号,count(object_no) as 课程编号  from score   group by students_no HAVING count( object_no)>2

查询同名学生名单并统计同名人数

select students_name as 学生名称,count(students_name) as 同名数量  from students group by students_name HAVING count(students_name)>1

计算每门课程的平均成绩

select object_no as 课程编号,avg(score) as 平均成绩 from score group by object_no

计算每门课程的平均成绩并且平均成绩大于等于70分

select object_no as 课程编号,avg(score) as 平均成绩 from score group by object_no HAVING avg(score)>70

查询不及格的课程并按课程号从大到小排列

select object_no as 课程编号,students_no 学生编号,score 分数 from score where score<60 order by object_no desc

查询每门课程的平均成绩，结果按平均成绩降序排列，如果平均成绩相同时，按课程号升序排列

select object_no as 课程编号,avg(score) as 平均成绩 from score group by object_no order by avg(score) desc,object_no asc