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

分组查询

在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进行降序排列.

select Studentno as 学生编号,AVG(Studentresult) as 平均分及格
from Result
group by StudentNo
having AVG(Studentresult)>=60
order 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 Result
where ExamDate>='2009-9-9' and  ExamDate<'2009-9-10'
group by SubjectId
having AVG(StudentResult)>=60

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

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

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

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

18813091329@163.com

QQ:2991635691