从0到1学数据库：分组函数

本章要点

• 分组函数的目的
• 分组函数基本概念
• SUM、AVG、MIN、MAX、COUNT函数使用
• GROUP BY子句
• HAVING子句

复制

分组函数

• 分组函数是对表中一组记录进行操作，每组只返回一个结果。即首先要对表记录进行分组，然后再进行操作汇总，每组返回 一个结果。分组时可能是整个表分为一组，也可能根据条件分 成多组。

• 分组函数常用到以下的五个函数：– MIN – MAX – SUM – AVG – COUNT

MIN函数和MAX函数

• MIN和MAX函数主要是返回每组的最小值和最大值。

– MIN([DISTINCT|ALL]表达式) – MAX([DISTINCT|ALL]表达式)

• 例 用户最低和最高分数的示例。

SELECT MIN(c_score), MAX(c_score) FROM student;

SUM函数和AVG函数

• SUM和AVG函数分别返回总和及平均值。

– SUM([DISTINCT|ALL]n)

– AVG([DISTINCT|ALL]n)

• SUM和AVG函数都是只能够对数字类型的列或表达式操作。

• 例 用户总分数和平均分数。

SELECT SUM(c_score), AVG(c_score) FROM student;

COUNT函数

• COUNT函数的主要功能是返回每组记录的条数。– COUNT({*|[DISTINCT|ALL]表达式})

• 例 用户总数。

SELECT COUNT(*) FROM student;

组函数中DISTINCT

• DISTINCT会消除重复记录后再使用组函数

• 例 班级总数。

SELECT COUNT(DISTINCT c_class) FROM student;

组函数中空值处理

• 所有组函数对空值都是忽略的。

• 例 获取平均分数——忽略空值。

SELECT AVG(c_score) FROM student;

分组函数

GROUP BY子句

SELECT FROM [WHERE [GROUP BY [ORDER BY

列名, 组函数(列名) 表名 条件] 分组列] 列名];

• 组函数忽略空值 • 结果集隐式按升序排列,如果需要改变排序方式可以使用 Order by 子句

• 例 每个班级的总分数。SELECT c_class, SUM(c_score) FROM student GROUP BY c_class ;

• 在GROUP BY子句使用中，有两点需要注意：– GROUP BY子句后的列可以不在SELECT语句中出现。– SELECT子句中出现的非分组函数列必须在GROUP BY子句 中出现。

• 例 查询每个班级平均分数，班级列不显示，同时结果按照平均分数排序。SELECT AVG(c_score) FROM student GROUP BY c_class ORDER BY AVG(c_score);

• 例 分组汇总错误示例 SELECT c_class, age, AVG(c_score) FROM student GROUP BY c_class;

• 例 分组汇总正确示例 SELECT c_class, age, AVG(c_score) FROM student GROUP BY c_class,age;

HAVING子句

• 例 组函数筛选示例。SELECT c_class, MAX(c_score) FROM student WHERE c_score>=80 GROUP BY c_class;

错误示例
SELECT c_class, MAX(c_score) FROM student WHERE MAX(c_score)>=80 GROUP BY c_class;
错误原因
WHERE子句中使用集函数。

复制

• 查询语句的执行顺序是：– FROM WHERE GROUP BY SELECT ORDER BY

• 语法结构如下：

SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY 列名, 组函数 表名 条件] 分组列] 组函数表达式] 列名];

• 例6-16 组函数筛选示例。

SELECT c_class, MAX(c_score) 
FROM student
GROUP BY c_class HAVING MAX(c_score)>=80;

复制

总结SELECT语句执行过程

– 通过FROM子句中找到需要查询的表；– 通过WHERE子句进行非分组函数筛选判断；– 通过GROUP BY子句完成分组操作；– 通过HAVING子句完成组函数筛选判断；– 通过SELECT子句选择显示的列或表达式及组函数；– 通过ORDER BY子句进行排序操作。

SELECT [DISTINCT|DISINCTROW|ALL] select_expression,... -- 查询结果 [ FROM table_references -- 指定查询的表 [WHERE where_definition] -- where子句，查询数据的过滤条件 [GROUP BY col_name,...] -- 对[匹配where子句的]查询结果进行分组 [HAVING where_definition] -- 对分组后的结果进行条件限制 [ORDER BY{unsigned_integer | col_name | formula} [ASC | DESC],...] -- 对查询结果进行排序 [LIMIT [offset,] rows] -- 对查询的显示结果进行条数限制 [PROCEDURE procedure_name] --查询存储过程返回的结果集数据 ]

• 例 组函数演示。

SELECT c_class, MAX(c_score) 
FROM student 
WHERE c_score>=80 
GROUP BY c_class;
HAVING MAX(c_score)>=80 
ORDER BY MAX(c_score);

复制

组函数的嵌套

• 组函数可以实现嵌套操作，嵌套级数是2级(select)。

• 例 组函数嵌套演示。获取用户表中，班级最多的班级

聚合函数不可以直接嵌套使用，比如：max（count（*））

错误示例：SELECT MAX(COUNT(id)) FROM student GROUP BY c_class;
这里会报错：Invalid use of group function

思路:可以嵌套子查询使用（先分组取出count值， 再将count值倒序排列，取第一个值就是最大的）
select  max(total) from ( select count(*) as total from student group by c_class) as aa;


复制