?

分组查询

?

在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子句的执行顺序不同。上面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 Subject

group by GradeId

order by 总学时数

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

?

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

?

select studentno as 学员编号,AVG(studentresult) as 平均分

from Result

group by StudentNo

?

?

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

?

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

?

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

?

select subjectid as 课程编号 , SUM(studentresult) as 平均分

from Result

group by SubjectId

order by 平均分 desc

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

?

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

?

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

?

select studentno as 学生编号 ,SUM(StudentResult) as 总分

from Result

group by StudentNo

order by 总分 desc

?

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

?

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

?

select * from Subject

select gradeid as 年级编号,COUNT(classhour) as 课程数

from Subject

where classhour>50

group by GradeId?

?

?

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

?

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

?

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

from Student

group by StudentNo

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

?

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

?

select gradeid as 年级编号,COUNT(studentno) as 学生人数

from Student

where Address like '%北京%'

group by GradeId

?

?

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

?

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

?