分组查询之牛刀小试!

1.查询每个年级的总学时数,并按照升序排列

select * from Subjectselect gradeid, sum(classhour) as 总学时数from Subjectgroup by GradeIdorder by sum(classhour)

2.查询每个参加考试的学员的平均分

select * from Resultselect studentno as 学员编号,AVG(studentresult) as 平均分from Result group by studentno

3.查每门课程的平均分,并按照降序排列

select * from Resultselect subjectid as 课程编号,AVG(studentResult) as 平均分from Resultgroup by SubjectIdorder by 平均分 desc

4.查询每个学生参加的所有考试的总分,并按照降序排列

select * from Resultselect studentno,sum(StudentResult) as 学员分数from Resultgroup by studentnoorder by 学员分数 desc

ok,在见证了这么easy的小知识后,小编也不再藏着掖着了,接下来看看我们的杀手锏吧!

1.查询每学期学时数超过50的课程数

select * from Subjectselect GradeId,COUNT(SubjectId) as 总时数 from Subjectwhere ClassHour>50group by GradeId

2.查询每学期学生的平均年龄

3.查询北京地区的每学期人数

4.查询参加考试的学生中，平均分及格的学生记录(学号，平均分)，按照降序排列

select * from Resultselect studentno,AVG(StudentResult) as 平均分from Resultgroup by StudentNohaving AVG(StudentResult)>=60order by 平均分 desc

5.查询考试时间为2009-9-9课程的及格平均分

select subjectid,AVG(studentresult) as 平均分from Resultwhere ExamDate>='2009-9-9' and ExamDate<'2009-9-10'group by SubjectIdhaving AVG(StudentResult)>=60

6.统计至少有一次不及格的学生学号和次数

select studentno,COUNT(1) as 次数from Resultwhere StudentResult<60group by StudentNo

having字句的位置不要记错了