--- title: "11-聚合查询" created: 2025-11-25 tags: - 项目筑基 --- # 聚合查询 ### 聚合查询 按照分组列（在GROUP子句中指定）值的个数n， 将数据源中指定的行（满足WHERE条件的行）分成n个组 （缺省GROUP的情况下，分成一个组） 并且可对每一个组做进一步的组筛选（由HAVING子句实现） 再针对每一组返回一个统计性的摘要行 （该摘要行中的统计数据由SELECT子句中的聚合函数提供） 三种实现形式 一、仅由聚合函数实现聚合查询 二、由聚合函数和GROUP子句 共同实现 三、由聚合函数、GROUP子句、HAVING子句 共同实现 #### 仅由聚合函数实现聚合查询 COUNT SUM AVG MIN MAX（必须分组才能用 未分组则默认整张表为一组） 1.count 计数函数 统计学生人数 ```sql SELECT COUNT(*) FROM 学生 ``` 这样是没有列名的 同前面“从学院表中提取所有学院的名称、电话组成一个学院电话表”一样 加个AS `SELECT COUNT(*) AS 学生人数 FROM 学生` AS 命名为 什么什么 统计教师人数 ```sql SELECT COUNT(*) AS 教师人数 FROM 教师 ``` 其实它的功能就是简单的把表里的行数做个统计 没那么智能 要准确查找需要加更多语句 比如 `SELECT * FROM 教师` 一共七条记录 所以 count=7 Count（具体字段） 统计该字段下不为NULL的元素综上 Count（\*） 表中总行数 （原理一样 又因为不存在全为NULL的记录） 妙用：`select count(distinct job) from emp` 与去重结合起来 就可以实现统计某字段种类数量的功能 2.sum 求和函数 ```sql --查询图书馆的图书总价值 SELECT SUM(price) AS 图书总价值 FROM books ``` 数值的计算用sum 价钱 分数…… 3.AVG 求平均值函数 ```sql --查询图书馆的图书平均价值 SELECT AVG(price) AS 图书平均价值 FROM books ``` 同样是用于数值 4.MIN 求最小值函数 ```sql --查询图书馆的图书最低价值 SELECT MIN(price) AS 图书最低价值 FROM books ``` 5.MAX 求最大值函数 ```sql --查询图书馆的图书最高价值 SELECT MAX(price) AS 图书最高价值 FROM books ``` #### 由聚合函数和GROUP子句 共同实现 GROUP有筛除重复记录显示的功能（DISTINCT） ```sql SELECT 列名 FROM 表名 GROUP BY 列名 ``` 比如： 单个字段分组 ```sql SELECT 成绩 FROM 选课成绩 ``` 加上GROUP ```sql SELECT 成绩 FROM 选课成绩 GROUP BY 成绩 ``` GROUP BY 还可以进行多个字段分组 ```sql SELECT 列名,列名…… FROM 表名 GROUP BY 列名，列名 ``` 比如：`SELECT 课程编号,成绩 FROM 选课成绩 GROUP BY 课程编号,成绩` PS: 这里把课程编号和成绩看成一个整体，只要是课程编号相同，成绩不同，就是两条记录 注意：SELECT后面跟着的列名一定要与GROUP BY后面的列名一模一样 数量也是 **GROUP BY与AVG连用** ```sql SELECT AVG(列名) AS 想显示的名字 FROM 表名 GROUP BY 列名 ``` 比如： ```sql --计算平均成绩 （不用GROUP BY 就直接求了所有数据的平均） SELECT AVG(成绩) AS 总平均成绩 FROM 选课成绩 ``` --计算某学生的各科平均成绩 （GROUP BY 学号 内部应该是现将表中信息按学号分好 再进行平均数函数） ```sql SELECT 学号,AVG(成绩) AS 学生平均成绩 FROM 选课成绩 GROUP BY 学号 ``` ```sql --计算某课程的学生平均成绩 （GROUP BY 课程编号 同理） SELECT 课程编号,AVG(成绩) AS 课程平均成绩 FROM 选课成绩 GROUP BY 课程编号 ``` **GROUP BY与MAX连用** ```sql SELECT MAX(列名) AS 想显示的名字 FROM 表名 GROUP BY 列名 ``` 比如： --找到最高分 ```sql SELECT MAX(成绩) AS 最高成绩 FROM 选课成绩 ``` --各科最高分 ```sql SELECT 课程编号,MAX(成绩) AS 科目第一 FROM 选课成绩 GROUP BY 课程编号 ``` **GROUP BY与MIN连用** ```sql SELECT MIN(列名) AS 想显示的名字 FROM 表名 GROUP BY 列名 ``` 比如： --找到最低分 ```sql SELECT MIN(成绩) FROM 选课成绩 ``` --各科最低分 ```sql SELECT 课程编号,MIN(成绩) AS 最低成绩 FROM 选课成绩 GROUP BY 课程编号 ``` **GROUP BY与COUNT连用** ```sql SELECT COUNT(列名) AS 想显示的名字 FROM 表名 GROUP BY 列名 ``` 比如： --查询不同课堂的选课人数 ```sql SELECT 课堂编号,COUNT(*) AS 选课人数 FROM 选课成绩 GROUP BY 课堂编号 ``` --查询不同学生的选课门数 ```sql SELECT 学号,COUNT(*) AS 选课门数 FROM 选课成绩 GROUP BY 学号 ``` --查询不同班级男女生的人数（先按班级分再按性别分） ```sql SELECT 专业班级,性别,COUNT(*) AS 人数 FROM 学生 GROUP BY 专业班级,性别 ``` （两个条件有一个不同就是不同记录） **GROUP BY与SUM连用** --按出版社分组 找到各出版社出版的图书的价格总值 ```sql SELECT Publisher,SUM(price) AS 出版社图书价值总计 FROM books GROUP BY Publisher ``` **用聚合函数的方式 想要显示完整的表 一定要是这两种情况：** 1. `SELECT 一,二 FROM 表名 GROUP BY 一,二` ——select 后的字段 全都包含在group by 后面，两个字段分组。 2.`SELECT 一,MAX(二) FROM 表名 GROUP BY 一` ——select 后的字段 二 虽然不在 group by 后面，但是在聚合函数MAX(二）里面 也就是要显示的列名 一定要在聚合函数中或者GROUP BY子句中 **GROUP BY 不仅可以加上聚合函数 还可以同时加上WHERE语句** ```sql SELECT 课堂编号,AVG(成绩) AS 平均成绩 FROM 选课成绩 WHERE 课堂编号 LIKE '2017-2018-1%' GROUP BY 课堂编号 ``` 用课堂编号分好组 保留类似于2017-2018-1%的记录 进行平均值函数运算 错！ 这是HAVING的过程 WHERE是 先保留类似于2017-2018-1%的记录 再将剩下的进行分组 然后再运算函数 具体在HAVING中解释 查询2017至2018 第二学期的选课成绩的平均分 显示课堂编号和平均成绩 ```sql SELECT 课堂编号,AVG(成绩) AS 平均成绩 FROM 选课成绩 WHERE 课堂编号 LIKE '2017-2018-2%' GROUP BY 课堂编号 ``` #### 由聚合函数、GROUP子句、HAVING子句 共同实现 查询 去掉无职称的职称种类统计结果 ```sql SELECT 职称,COUNT(*) AS 人数 FROM 教师 GROUP BY 职称 HAVING 职称 IS NOT NULL ``` --之前也遇过这种情况 当时是用WHERE ```sql 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 第二学期的选课成绩的平均分 显示课堂编号和平均成绩 ```sql SELECT 课堂编号,AVG(成绩) AS 平均成绩 FROM 选课成绩 GROUP BY 课堂编号 HAVING 课堂编号 LIKE '2017-2018-2%' ``` 查询不同职称的教师人数,并筛选出人数大于等于2的情况 ```sql SELECT 职称,COUNT(*) AS 人数 FROM 教师 GROUP BY 职称 HAVING COUNT(*)>=2 ``` 选课超过两门 且成绩都在80分以上的 学生学号 ```sql SELECT 学号 FROM 选课成绩 WHERE 成绩>=80 GROUP BY 学号 HAVING count(课程编号)>=2 ``` 统计学生表中 各省份男女总人数 ```sql SELECT 籍贯,SUM(case 性别 WHEN '男' THEN 1 else 0 end) AS 男生人数,SUM(case 性别 WHEN '女' THEN 1 else 0 end) AS 女生人数 FROM 学生 GROUP BY 籍贯 ``` 查找高等教育出版社出版的 定价高于所有图书平均定价的图书信息 ```sql 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] 指定聚合函数计算的汇总结果集。 用法： 对比： ```sql SELECT * FROM 选课成绩 WHERE 课程编号='A1002' ORDER BY 学号 compute max(成绩),min(成绩) BY 学号 ``` server sql 2012 废弃了该功能 --- ⬅️ [[10-关系代数与单表查询|10-关系代数与单表查询]] 🏠 [[00-数据库|00-数据库]] ➡️ [[12-排序与语句执行顺序|12-排序与语句执行顺序]]