那些年我们一起做过的[分组查询]
分组查询
在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 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
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
