您好,登錄后才能下訂單哦!
這篇文章主要介紹“sql查詢語句之平均分、最高最低分及排序語句怎么寫”的相關(guān)知識,小編通過實際案例向大家展示操作過程,操作方法簡單快捷,實用性強,希望這篇“sql查詢語句之平均分、最高最低分及排序語句怎么寫”文章能幫助大家解決問題。
以mysql為例,匯總sql查詢最高分、最低分、平均分等sql語句,oracle語法類似,可自行修改以下sql語句
創(chuàng)建兩個數(shù)據(jù)庫表,一個學(xué)生表、一個考試成績表
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 '學(xué)號', `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;
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
查詢每門課程被選修的學(xué)生數(shù)
select object_no as '課程編號', count(DISTINCT students_no) as '學(xué)生編號' from score group by object_no
select sex '性別',count(DISTINCT students_no) '數(shù)量' from students group by sex
select students_no as '學(xué)生編號',avg(score) as '平均成績' from score group by students_no
select students_no as '學(xué)生編號',avg(score) as '平均成績' from score group by students_no HAVING avg(score)>70
select students_no as '學(xué)生編號',count(object_no) as '課程編號' from score group by students_no
select students_no as '學(xué)生編號',count(object_no) as '課程編號' from score group by students_no HAVING count( object_no)>2
select students_name as '學(xué)生名稱',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
select object_no as '課程編號',avg(score) as '平均成績' from score group by object_no HAVING avg(score)>70
select object_no as '課程編號',students_no '學(xué)生編號',score '分數(shù)' 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
關(guān)于“sql查詢語句之平均分、最高最低分及排序語句怎么寫”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識,可以關(guān)注億速云行業(yè)資訊頻道,小編每天都會為大家更新不同的知識點。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。