你的位置:首页 > 数据库

[数据库]那些年我们一起做过的[分组查询]


分组查询

在SQL Server中使用的分组查询是ORDER BY子句,使用ORDER BY子句要同聚合函数配合使用才能完成分组查询,在SELECT查询的字段中如果字段没有使用聚合函数就必须出现在ORDER BY子句中(即SELECT后边的字段名要么出现在聚合函数中,要么在ORDER BY子句中使用)

使用group by进行分组查询
在使用group by关键字时,在select列表中可以指定的项目是有限制的,select语句中仅许以下几项:
*被分组的列
*为每个分组返回一个值得表达式,例如用一个列名作为参数的聚合函数

注意:group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面

HAVING子句与WHERE子句的区别

HAVING子句和WHERE子句的相似之处在于,它也定义搜索条件。但与WHERE子句不同,HAVING子句与组有关,而不是与单个的行有关。

1、如果指定了GROUP BY子句,那么HAVING子句定义的搜索条件将作用于这个GROUP BY子句创建的那些组。

2、如果指定WHERE子句,而没有指定GROUP BY子句,那么HAVING子句定义的搜索条件将作用于WHERE子句的输出,并把这个输出看作是一个组。

3、如果既没有指定GROUP BY子句也没有指定WHERE子句,那么HAVING子句定义的搜索条件将作用于FROM子句的输出,并把这个输出看作是一个组。

4、在SELECT语句中,WHERE和HAVING子句的执行顺序不同。在本书的5.1.2节介绍的SELECT语句的执行步骤可知,WHERE子句只能接收来自FROM子句的输入,而HAVING子句则可以接收来自GROUP BY子句、WHERE子句和FROM子句的输入。

S-TQL语句执行顺序

select         5:投影 ,映射为对应的列
from           1:定位到表
where          2:进行分组前的第一次筛选
group by      3:分组
having         4:对分组后的数据进行第二次筛选
order by      6:对映射出的结果进行排序

 

注意:where后面不能加聚合函数
     三个顺序:
     01where:对表中记录进行筛选(分组前)
     02group by 分组依据
     03having 对分组后的数据进行筛选
     见到having,之前必须有group by,因为having是对分组后的数据进行筛选
     三者使用顺序不可颠倒

eg:

myschool数据库中有四张表,分别为student(学生表)result(成绩表)subject(科目表)grade(年级表)

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

题目是每个年级的总学时数,每个年级肯定做为group by的分组依据,总学时则利用sum()函数

至于order by后也可以跟SUM(classhour).写总学时数的目的是为了更详细的理解语句的执行顺序

select gradeid as 年级编号,SUM(classhour) as 总学时数from Subjectgroup by GradeIdorder by 总学时数

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

每个参加考试的学员平均分,学员对应学员编号肯定为group by的分组依据,平均分利用avg()函数计算出来

select studentno as 学员编号,AVG(studentresult) as 平均分from Resultgroup by StudentNo

 

--03查询每门课程的平均分,并按照降序排列

每门课的平均分,按降序排列,group by的分租依据是subjectid(课程编号)平均分则利用avg()函数计算出来

利用order by 平均分 desc 来进行降序排列

select subjectid as 课程编号 , SUM(studentresult) as 平均分from Resultgroup by SubjectIdorder by 平均分 desc

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

每个学生所有考试总分,按降序排列,group by的分组依据是studentno(学生编号),总分利用sum()函数进行计算

最后利用order by总分desc来进行降序排序

select studentno as 学生编号 ,SUM(StudentResult) as 总分from Resultgroup by StudentNoorder by 总分 desc

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

每学期学时超过50的课程数,group by的分组依据是gradeid(年级编号),现有一个限定条件学时不超过50,这里用where来限定他classhour>50

select * from Subjectselect gradeid as 年级编号,COUNT(classhour) as 课程数from Subjectwhere classhour>50group by GradeId 

 

06查询每学期学生的平均年龄

每学期学生的平均年龄,group by的分组依据是studentno(学生编号),平均年龄用datediff()进行处理得出年龄,用avg()在进行处理,得出平均年龄.

select studentno as 学生编号,AVG(DATEDIFF(yy,birthday,GETDATE())) as 平均年龄from Studentgroup by StudentNo

07查询北京地区的每学期学生人数

北京地区的每学期学生人数.group by的分组依据是gradeid ,学生人数用count(studentno)得出学生人数,最后有个限定条件,只查询北京地区的,用where进行限定,address like '%北京%'%代表任意长度的字符.

select gradeid as 年级编号,COUNT(studentno) as 学生人数from Studentwhere Address like '%北京%'group by GradeId

 

08查询参加考试的学生中平均分及格的学生记录,并按照成绩降序排列

参加考试的学生中平均分及格的学生记录,按降序排列,group by 分组依据是studentno,平均分用av(studentresult)进行计算出平均分,having进行判定avg(studentresult)>60是否及格,在用order by  平均分及格 desc进行降序排列.

select Studentno as 学生编号,AVG(Studentresult) as 平均分及格from Resultgroup by StudentNohaving AVG(Studentresult)>=60order by 平均分及格 desc

 

09查询考试日期为2009年9月9号的课程的及格平均分

考试日期为2009年9月9号的课程的及格平均分,group by 的分组依据是课程编号,限定条件是考试日期为2009年9月9号

where ExamDate>='2009-9-9' and  ExamDate<'2009-9-10'.最后进行过滤及格平均分having avg(studentresult)>=60.

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


 

10查询至少一次考试不及格的学生学号,不及格次数

考试不及格的学生学号,不及格次数,先限定条件不及格的学生成绩,在按照学生学号进行分组,group by studentno

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

 谢谢大家抽出宝贵的时间来看,希望对你有所帮助,如果觉得写得还可以的请支持,加关注!如果有啥问题请发送到我的邮箱

18813091329@163.com

QQ:2991635691