亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

sql查詢語句之平均分、最高最低分及排序語句

 更新時間：2022年05月29日 15:30:54   作者：法海不再愛代碼  
最近工作中遇到個需求,需要查詢每門課程的平均成績,結(jié)果按平均成績降序排列,平均成績相同時,按課程編號升序排列,這篇文章主要給大家介紹了關(guān)于sql查詢語句之平均分、最高最低分及排序語句的相關(guān)資料,需要的朋友可以參考下

前言

以mysql為例，匯總sql查詢最高分、最低分、平均分等sql語句，oracle語法類似，可自行修改以下sql語句

創(chuàng)建兩個數(shù)據(jù)庫表，一個學生表、一個考試成績表

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 '分數(shù)'
) 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;

數(shù)據(jù)表數(shù)據(jù)

查詢各課程的考試學生人數(shù)

select object_no as '課程編號', count(DISTINCT students_no) '考試人數(shù)' from score group by object_no

查詢各科成績最高分和最低分

select object_no as '課程編號',max(score) as '最高分',min(score) as '最低分' from score group by object_no

查詢每門課程被選修的學生數(shù)

select object_no as '課程編號', count(DISTINCT students_no) as '學生編號' from score group by object_no

查詢男生、女生人數(shù)

select sex '性別',count(DISTINCT students_no) '數(shù)量' 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

查詢學生考試參與考試課程數(shù)量

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

查詢同名學生名單并統(tǒng)計同名人數(shù)

select students_name as '學生名稱',count(students_name) as '同名數(shù)量'  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 '分數(shù)' from score where score<60 order by object_no desc

查詢每門課程的平均成績，結(jié)果按平均成績降序排列，如果平均成績相同時，按課程號升序排列

select object_no as '課程編號',avg(score) as '平均成績' from score group by object_no order by avg(score) desc,object_no asc

總結(jié)

到此這篇關(guān)于sql查詢語句之平均分、最高最低分及排序語句的文章就介紹到這了,更多相關(guān)mysql平均分查詢語句內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評論