这篇文章主要介绍“sql查询语句之平均分、最高最低分及排序语句怎么写”的相关知识,小编通过实际案例向大家展示操作过程,操作方法简单快捷,实用性强,希望这篇“sql查询语句之平均分、最高最低分及排序语句怎么写”文章能帮助大家解决问题。前言以m
这篇文章主要介绍“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` 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
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
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
关于“sql查询语句之平均分、最高最低分及排序语句怎么写”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识,可以关注编程网精选频道,小编每天都会为大家更新不同的知识点。
--结束END--
本文标题: sql查询语句之平均分、最高最低分及排序语句怎么写
本文链接: https://www.lsjlt.com/news/330432.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0