经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 程序设计 » SQL语言 » 查看文章
sql查询语句之平均分、最高最低分及排序语句
来源:jb51  时间:2022/5/30 9:33:52  对本文有异议

前言

以mysql为例,汇总sql查询最高分、最低分、平均分等sql语句,oracle语法类似,可自行修改以下sql语句

创建两个数据库表,一个学生表、一个考试成绩表

  1. DROP TABLE IF EXISTS `score`;
  2. CREATE TABLE `score` (
  3. `u_id` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '编号',
  4. `object_no` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程编号',
  5. `students_no` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学号',
  6. `score` int(11) NULL DEFAULT NULL COMMENT '分数'
  7. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
  8.  
  9. INSERT INTO `score` VALUES ('1', '100', 's100', 80);
  10. INSERT INTO `score` VALUES ('2', '100', 's200', 59);
  11. INSERT INTO `score` VALUES ('3', '100', 's300', 79);
  12. INSERT INTO `score` VALUES ('4', '200', 's100', 54);
  13. INSERT INTO `score` VALUES ('5', '200', 's200', 96);
  14. INSERT INTO `score` VALUES ('6', '200', 's300', 74);
  15. INSERT INTO `score` VALUES ('7', '300', 's100', 65);
  16. INSERT INTO `score` VALUES ('8', '300', 's200', 80);
  17. INSERT INTO `score` VALUES ('9', '200', 's400', 62);
  18. INSERT INTO `score` VALUES ('10', '300', 's400', 56);
  19. INSERT INTO `score` VALUES ('11', '100', 's400', 70);
  20. SET FOREIGN_KEY_CHECKS = 1;
  21.  
  22. DROP TABLE IF EXISTS `students`;
  23. CREATE TABLE `students` (
  24. `students_no` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编号',
  25. `students_name` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  26. `sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  27. PRIMARY KEY (`students_no`) USING BTREE
  28. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
  29.  
  30. INSERT INTO `students` VALUES ('s100', '张三', '1');
  31. INSERT INTO `students` VALUES ('s200', '李四', '2');
  32. INSERT INTO `students` VALUES ('s300', '张三', '1');
  33. INSERT INTO `students` VALUES ('s400', '王五', '1');
  34. SET FOREIGN_KEY_CHECKS = 1;

数据表数据

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

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

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

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

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

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

查询男生、女生人数

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

查询平均成绩

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

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

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

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

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

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

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

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

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

计算每门课程的平均成绩

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

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

  1. select 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

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

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

总结

到此这篇关于sql查询语句之平均分、最高最低分及排序语句的文章就介绍到这了,更多相关mysql平均分查询语句内容请搜索w3xue以前的文章或继续浏览下面的相关文章希望大家以后多多支持w3xue!

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号