分组查询之牛刀小试!

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.查询每学期学生的平均年龄

`select * from Studentselect gradeid,AVG(DATEDIFF(YY,birthday,GETDATE())) as 平均年龄from Studentgroup by gradeid`

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

`select * from Studentselect gradeid,COUNT(1) as 总人数from studentwhere address like '%北京%'group by Gradeid`

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字句的位置不要记错了

Top