sql查询语句之平均分、最高最低分及排序语句怎么写(SQL,开发技术)

前言

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

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

DROPTABLEIFEXISTS`score`;CREATETABLE`score`(`u_id`varchar(11)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'编号',`object_no`varchar(11)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'课程编号',`students_no`varchar(11)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'学号',`score`int(11)NULLDEFAULTNULLCOMMENT'分数')ENGINE=InnoDBCHARACTERSET=utf8COLLATE=utf8_general_ciROW_FORMAT=Compact;INSERTINTO`score`VALUES('1','100','s100',80);INSERTINTO`score`VALUES('2','100','s200',59);INSERTINTO`score`VALUES('3','100','s300',79);INSERTINTO`score`VALUES('4','200','s100',54);INSERTINTO`score`VALUES('5','200','s200',96);INSERTINTO`score`VALUES('6','200','s300',74);INSERTINTO`score`VALUES('7','300','s100',65);INSERTINTO`score`VALUES('8','300','s200',80);INSERTINTO`score`VALUES('9','200','s400',62);INSERTINTO`score`VALUES('10','300','s400',56);INSERTINTO`score`VALUES('11','100','s400',70);SETFOREIGN_KEY_CHECKS=1;DROPTABLEIFEXISTS`students`;CREATETABLE`students`(`students_no`varchar(12)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULLCOMMENT'编号',`students_name`varchar(12)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'姓名',`sex`varchar(1)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'性别',PRIMARYKEY(`students_no`)USINGBTREE)ENGINE=InnoDBCHARACTERSET=utf8COLLATE=utf8_general_ciROW_FORMAT=Compact;INSERTINTO`students`VALUES('s100','张三','1');INSERTINTO`students`VALUES('s200','李四','2');INSERTINTO`students`VALUES('s300','张三','1');INSERTINTO`students`VALUES('s400','王五','1');SETFOREIGN_KEY_CHECKS=1;

数据表数据

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

selectobject_noas'课程编号',count(DISTINCTstudents_no)'考试人数'fromscoregroupbyobject_no

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

selectobject_noas'课程编号',max(score)as'最高分',min(score)as'最低分'fromscoregroupbyobject_no

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

selectobject_noas'课程编号',count(DISTINCTstudents_no)as'学生编号'fromscoregroupbyobject_no

查询男生、女生人数

selectsex'性别',count(DISTINCTstudents_no)'数量'fromstudentsgroupbysex

查询平均成绩

selectstudents_noas'学生编号',avg(score)as'平均成绩'fromscoregroupbystudents_no

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

selectstudents_noas'学生编号',avg(score)as'平均成绩'fromscoregroupbystudents_noHAVINGavg(score)>70

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

selectstudents_noas'学生编号',count(object_no)as'课程编号'fromscoregroupbystudents_no

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

selectstudents_noas'学生编号',count(object_no)as'课程编号'fromscoregroupbystudents_noHAVINGcount(object_no)>2

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

selectstudents_nameas'学生名称',count(students_name)as'同名数量'fromstudentsgroupbystudents_nameHAVINGcount(students_name)>1

计算每门课程的平均成绩

selectobject_noas'课程编号',avg(score)as'平均成绩'fromscoregroupbyobject_no

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

selectobject_noas'课程编号',avg(score)as'平均成绩'fromscoregroupbyobject_noHAVINGavg(score)>70

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

selectobject_noas'课程编号',students_no'学生编号',score'分数'fromscorewherescore<60orderbyobject_nodesc

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

selectobject_noas'课程编号',avg(score)as'平均成绩'fromscoregroupbyobject_noorderbyavg(score)desc,object_noasc