数据库表分组查询的那些事
示例表一(MySchool表):
示例表二(Subject表):
示例表三(Result表):
示例表4(Grade表):
--查询每个年级的总学时数，并按照升序排列
select GradeId,SUM(ClassHour) as 总学时数
from Subject
group by GradeId
order by sum(ClassHour)
--查询每个参加考试的学员平均分
select studentno,AVG(studentresult) as 平局分
from Result
group by StudentNo
--查询每门课程的平均分，并按照降序排列
select subjectid,AVG(studentresult)as 平均分
from Result
group by SubjectId
order by 平均分 desc
--查询每个学生参加所有考试的总分，并按照降序排列
select studentno,SUM(studentresult) as 总分
from Result
group by StudentNo
order by 总分 desc
--多列分组
--每个年级 男女生总人数(gradeid,gender,人数)
select gradeid,gender,COUNT(1)
from student
group by GradeId,Gender
order by GradeId,Gender
--having
--每个年级的总人数，满足总人数必须大于等于3
select gradeid,COUNT(1) as 总人数
from student
group by GradeId
having COUNT(1)>=3
--查询每年级学时数超过50 的课程数 S1
select gradeid,COUNT(subjectid) as 课程数
from Subject
where ClassHour>50
group by GradeId
--查询每年级学生的平均年龄:
select gradeid,AVG(DATEDIFF(yy,birthday,GETDATE())) as 平均年龄
from student
group by gradeid
--查询每个年级包含北京的学生总数
select gradeid,COUNT(1) as 总人数
from student
where address like '%北京%'
group by Gradeid
select * from student
order by GradeId
--查询参加考试的学生中，平均分及格的学生记录(学号，平均分)，按照降序排列
select studentno,AVG(StudentResult) as 平均分
from Result
group by StudentNo
having AVG(StudentResult)>=60
order by 平均分 desc
--查询考试时间为2009-9-9课程的及格平均分
--课程编号 所有课程平均分
select subjectid,AVG(studentresult) as 平均分
from Result
where ExamDate>='2009-9-9' and ExamDate<'2009-9-10'
group by SubjectId
having AVG(StudentResult)>=60
select * from student
where Birthday>='2015-07-10' and Birthday<'2015-07-11'
--统计至少有一次不及格的学生学号和次数。
select studentno,COUNT(1) as 次数
from Result
where StudentResult<60
group by StudentNo