聚合查询
聚合查询
按照分组列（在GROUP子句中指定）值的个数n，
将数据源中指定的行（满足WHERE条件的行）分成n个组
（缺省GROUP的情况下，分成一个组）
并且可对每一个组做进一步的组筛选（由HAVING子句实现）
再针对每一组返回一个统计性的摘要行
（该摘要行中的统计数据由SELECT子句中的聚合函数提供）
三种实现形式
一、仅由聚合函数实现聚合查询
二、由聚合函数和GROUP子句 共同实现
三、由聚合函数、GROUP子句、HAVING子句 共同实现
仅由聚合函数实现聚合查询
COUNT SUM AVG MIN MAX（必须分组才能用 未分组则默认整张表为一组）
1.count 计数函数
统计学生人数
SELECT COUNT(*) FROM 学生
这样是没有列名的
同前面“从学院表中提取所有学院的名称、电话组成一个学院电话表”一样 加个AS
SELECT COUNT(*) AS 学生人数 FROM 学生 AS 命名为 什么什么
统计教师人数
SELECT COUNT(*) AS 教师人数 FROM 教师
其实它的功能就是简单的把表里的行数做个统计 没那么智能 要准确查找需要加更多语句
比如 SELECT * FROM 教师 一共七条记录 所以 count=7
Count（具体字段） 统计该字段下不为NULL的元素综上
Count（*） 表中总行数 （原理一样 又因为不存在全为NULL的记录）
妙用：select count(distinct job) from emp
与去重结合起来 就可以实现统计某字段种类数量的功能
2.sum 求和函数
--查询图书馆的图书总价值
SELECT SUM(price) AS 图书总价值 FROM books
数值的计算用sum 价钱 分数……
3.AVG 求平均值函数
--查询图书馆的图书平均价值
SELECT AVG(price) AS 图书平均价值 FROM books
同样是用于数值
4.MIN 求最小值函数
--查询图书馆的图书最低价值
SELECT MIN(price) AS 图书最低价值 FROM books
5.MAX 求最大值函数
--查询图书馆的图书最高价值
SELECT MAX(price) AS 图书最高价值 FROM books
由聚合函数和GROUP子句 共同实现
GROUP有筛除重复记录显示的功能（DISTINCT）
SELECT 列名 FROM 表名 GROUP BY 列名
比如：
单个字段分组
SELECT 成绩 FROM 选课成绩
加上GROUP
SELECT 成绩 FROM 选课成绩 GROUP BY 成绩
GROUP BY 还可以进行多个字段分组
SELECT 列名,列名…… FROM 表名 GROUP BY 列名，列名
比如：SELECT 课程编号,成绩 FROM 选课成绩 GROUP BY 课程编号,成绩
PS: 这里把课程编号和成绩看成一个整体，只要是课程编号相同，成绩不同，就是两条记录
注意：SELECT后面跟着的列名一定要与GROUP BY后面的列名一模一样 数量也是
GROUP BY与AVG连用
SELECT AVG(列名) AS 想显示的名字 FROM 表名 GROUP BY 列名
比如：
--计算平均成绩 （不用GROUP BY 就直接求了所有数据的平均）
SELECT AVG(成绩) AS 总平均成绩 FROM 选课成绩
--计算某学生的各科平均成绩
（GROUP BY 学号 内部应该是现将表中信息按学号分好 再进行平均数函数）
SELECT 学号,AVG(成绩) AS 学生平均成绩 FROM 选课成绩 GROUP BY 学号
--计算某课程的学生平均成绩 （GROUP BY 课程编号 同理）
SELECT 课程编号,AVG(成绩) AS 课程平均成绩 FROM 选课成绩 GROUP BY 课程编号
GROUP BY与MAX连用
SELECT MAX(列名) AS 想显示的名字 FROM 表名 GROUP BY 列名
比如：
--找到最高分
SELECT MAX(成绩) AS 最高成绩 FROM 选课成绩
--各科最高分
SELECT 课程编号,MAX(成绩) AS 科目第一 FROM 选课成绩 GROUP BY 课程编号
GROUP BY与MIN连用
SELECT MIN(列名) AS 想显示的名字 FROM 表名 GROUP BY 列名
比如：
--找到最低分
SELECT MIN(成绩) FROM 选课成绩
--各科最低分
SELECT 课程编号,MIN(成绩) AS 最低成绩 FROM 选课成绩 GROUP BY 课程编号
GROUP BY与COUNT连用
SELECT COUNT(列名) AS 想显示的名字 FROM 表名 GROUP BY 列名
比如：
--查询不同课堂的选课人数
SELECT 课堂编号,COUNT(*) AS 选课人数 FROM 选课成绩 GROUP BY 课堂编号
--查询不同学生的选课门数
SELECT 学号,COUNT(*) AS 选课门数 FROM 选课成绩 GROUP BY 学号
--查询不同班级男女生的人数（先按班级分再按性别分）
SELECT 专业班级,性别,COUNT(*) AS 人数 FROM 学生 GROUP BY 专业班级,性别
（两个条件有一个不同就是不同记录）
GROUP BY与SUM连用
--按出版社分组 找到各出版社出版的图书的价格总值
SELECT Publisher,SUM(price) AS 出版社图书价值总计 FROM books GROUP BY Publisher
用聚合函数的方式 想要显示完整的表 一定要是这两种情况：
SELECT 一,二 FROM 表名 GROUP BY 一,二
——select 后的字段 全都包含在group by 后面，两个字段分组。
2.SELECT 一,MAX(二) FROM 表名 GROUP BY 一
——select 后的字段 二 虽然不在 group by 后面，但是在聚合函数MAX(二）里面
也就是要显示的列名 一定要在聚合函数中或者GROUP BY子句中
GROUP BY 不仅可以加上聚合函数 还可以同时加上WHERE语句
SELECT 课堂编号,AVG(成绩) AS 平均成绩 FROM 选课成绩
WHERE 课堂编号 LIKE '2017-2018-1%' GROUP BY 课堂编号
用课堂编号分好组 保留类似于2017-2018-1%的记录 进行平均值函数运算
错！ 这是HAVING的过程
WHERE是 先保留类似于2017-2018-1%的记录 再将剩下的进行分组 然后再运算函数 具体在HAVING中解释
查询2017至2018 第二学期的选课成绩的平均分 显示课堂编号和平均成绩
SELECT 课堂编号,AVG(成绩) AS 平均成绩 FROM 选课成绩
WHERE 课堂编号 LIKE '2017-2018-2%' GROUP BY 课堂编号
由聚合函数、GROUP子句、HAVING子句 共同实现
查询 去掉无职称的职称种类统计结果
SELECT 职称,COUNT(*) AS 人数 FROM 教师 GROUP BY 职称 HAVING 职称 IS NOT NULL
--之前也遇过这种情况 当时是用WHERE
SELECT 职称,COUNT(*) AS 人数 FROM 教师 WHERE 职称 IS NOT NULL GROUP BY 职称
得到的结果一模一样
那为什么要多此一举多设置一个HAVING语句呢？
它们的区别在哪里？？？
ps：where 是先过滤，再分组；having 是分组后再过滤
有啥区别？？？i++ ++i？
HAVING 关键字和 WHERE 关键字都可以用来过滤数据
且 HAVING 支持 WHERE 关键字中所有的操作符和语法。
但是 WHERE 和 HAVING 关键字也存在以下几点差异：
1.一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
2.WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
--查询显示平均分大于85分的课堂编号和平均成绩
SELECT 课堂编号,AVG(成绩) AS 平均成绩 FROM 选课成绩
GROUP BY 课堂编号 HAVING AVG(成绩)>85
错误用法：
SELECT 课堂编号,AVG(成绩) AS 平均成绩 FROM 选课成绩
WHERE AVG(成绩)>85 GROUP BY 课堂编号
3.WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
--查询显示平均分大于85分的课堂编号和平均成绩
SELECT 课堂编号,AVG(成绩) AS 平均成绩 FROM 选课成绩
GROUP BY 课堂编号 HAVING AVG(成绩)>85
先按课堂编号分好组 再筛选平均分大于85分的
SELECT 课堂编号,AVG(成绩) AS 平均成绩 FROM 选课成绩
WHERE 课堂编号 LIKE '2017-2018-1%' GROUP BY 课堂编号
先保留类似于2017-2018-1%的记录 再将剩下的进行分组 然后再运算函数
4.WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE
根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
5.WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。
（得看是什么时候取的 from时取的就可以用）
实例示范：（待续）
查询2017至2018 第二学期的选课成绩的平均分 显示课堂编号和平均成绩
SELECT 课堂编号,AVG(成绩) AS 平均成绩 FROM 选课成绩
GROUP BY 课堂编号 HAVING 课堂编号 LIKE '2017-2018-2%'
查询不同职称的教师人数,并筛选出人数大于等于2的情况
SELECT 职称,COUNT(*) AS 人数 FROM 教师 GROUP BY 职称 HAVING COUNT(*)>=2
选课超过两门 且成绩都在80分以上的 学生学号
SELECT 学号 FROM 选课成绩 WHERE 成绩>=80 GROUP BY 学号 HAVING count(课程编号)>=2
统计学生表中 各省份男女总人数
SELECT 籍贯,SUM(case 性别 WHEN '男' THEN 1 else 0 end) AS 男生人数,SUM(case 性别
WHEN '女' THEN 1 else 0 end) AS 女生人数
FROM 学生 GROUP BY 籍贯
查找高等教育出版社出版的 定价高于所有图书平均定价的图书信息
SELECT * FROM books
WHERE Price>(select avg(price) FROM books) AND Publisher='高等教育出版社'
COMPUTE BY
compute by 子句可通过同一个select语句既查看明细行，又查看汇总行。
可计算子组的汇总值，也可计算整个结果集的汇总值。
1、可选的by关键字，指定按哪一列分组的基础上进行聚合。
所以如果使用by关键字，则之前必须使用order by ，并且分组的列和排序的列一致。
如果不带by关键字，则是对整个结果集进行汇总。
2、行聚合函数：count,max,min,sum,avg
3、使用compute [by]子句的select语句将产生2个结果集。
（1）一个为select 指定的明细行的结果集。
（2）另一个为compute [by] 指定聚合函数计算的汇总结果集。
用法：https://jingyan.baidu.com/article/39810a23b32d2db636fda61b.html
对比：https://blog.csdn.net/gditzmr/article/details/103834453
SELECT * FROM 选课成绩 WHERE 课程编号='A1002' ORDER BY 学号
compute max(成绩),min(成绩) BY 学号
server sql 2012 废弃了该功能
⬅️ 10-关系代数与单表查询 🏠 00-数据库 ➡️ 12-排序与语句执行顺序
💬 评论