全部章节   >>>>

---

本章目录

6.1 sum、max 和 min 聚合函数

6.1.1 聚合函数介绍

6.1.2 sum 函数

6.1.3 max/min 函数

6.2 avg 和 count 函数

6.2.1 avg 函数

6.2.2 count 函数

6.3 分组查询 group by 子句

6.3.1 group by 子句

6.3.2 创建分组

6.3.3 比较 order by 和 group by

6.3.4 使用 where 子句实现分组之前过滤数据

6.3.5 分组函数中的非聚合函数列

6.4 多字段分组和 having 子句

6.4.1 多字段分组

6.4.2 使用 having 指定分组后的筛选条件

6.4.3 比较 where 子句和 having 子句

6.4.4 group by 子句的 with rollup 选项

总结：

---

6.1 sum、max 和 min 聚合函数

在数据库查询过程中，不仅只返回数据的基础信息，有时还需对这些数据进行统计和汇总。MySQL 提供了聚合函数，用于实现这些高级功能。

聚合函数用于对一组值进行计算并返回一个汇总值，使用聚合函数可以统计记录行数、计算某个字段值的总和以及这些值的最大值、最小值和平均值等。

函数名称

功能

sum

返回选取的某列值的总和

max

返回选取的某列的最大值

min

返回选取的某列的最小值

avg

返回选取的某列的平均值

count

返回选取的某列或记录的行数

示例：计算所有线路总里程数。

select sum(miles) 线路总里程数 from line;

示例：计算公交二公司所属线路总里程数。

select sum(miles) 线路总里程数 from line where company=' 公交二公司 ';

示例： 对所有线路的总站点数进行汇总。

select sum(number) 线路总站点数 from line;

示例：计算起始站在南湖地区线路的总站点数。

select sum(number) 线路总站点数 from line where from_station like '% 古田 %' or end_station like '% 古田 %'

示例：获取所有线路里程的最大值和最小值。

select max(miles) 最长线路里程 , min(miles) 最短线路里程 from line;

示例：查询年龄最大的公交司机的出生年月。

select min(birthday) 最年长司机生日 ,
ceil(datediff(curdate(),min(birthday))/365) 该司机年龄 from driver;

内置函数 curdate() 显示当前日期。

内置函数 datediff(date1,date2) 用于计算日期 date1 和 date2 之间相隔的天数。

内置函数 ceil(num1) 的值为大于等于 num1 的最小整数。

6.2 avg 和 count 函数

avg(column) 用于返回数字型字段 column 的平均值，注意：列值为 null 的字段不参与运算。

示例：获取线路里程的平均值。

select avg(miles) 平均里程数 from line;

示例：获取公交二公司所属线路的平均里程数。

select avg(miles) 平均里程数 from line where company=' 公交二公司 ';

count 函数用于统计记录行数。使用 count 函数时，必须指定一个列的名称或使用“*”。使用“*”可获取整张表的记录行数。注意：列值为 null 的字段不参与运算。

示例：统计所有公交线路的数量。

select count(*) 线路数 from line;
或
select count(lineNo) 线路数 from line;

示例：统计出生年月大于 1985-1-1 之后的公交司机人数。

select count(*) AS 司机数 from driver where birthday>'1985-1-1';

示例：统计有出生记录的司机人数。

select count(birthday) AS 司机数 from driver;
或
select count(*) AS 司机数 from driver where birthday is not null;

6.3 分组查询 group by 子句

使用 group by 子句可以将数据划分到不同的组中，实现对记录的分组查询。group by 从英文字面的意义上可以理解为“根据（by）一定的规则进行分组（group）”，该子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对这若干个小区域进行统计汇总。

group by 字段列表 [having 条件表达式 ]

group by 子句通常与聚合函数同时使用，如 max()、min()、count()、sum() 和 avg()。

示例：统计每一家公交公司的线路数和线路长度平均值、最大值和最小值，按线路数升序显示。

分析：

• 依据线路所属公司分组统计线路相关数据。
• 线路数和线路里程的平均值、最小值和最大值均依据线路所属公司分组计算所得。

select company 公司 , count(*) 线路数 , truncate(avg(miles),1) 平均线路里程 , max(miles) 最长线路里程 , min(miles) 最短线路里程 from line
group by company order by count(*)

解释：函数 truncate(x,d) 用于返回 x 保留到小数点后 d 位的值。

示例：统计拥有不同运营车辆数的公交线路数量，按运营车辆数升序显示。

分析：

• 依据运营车辆数的不同，分组统计其所对应的公交线路的数量。
• 分组列为运营车辆数。

select number 运营车辆数, count(lineNo) 公交线路数 from line group by number order by number

select * from line order by company;

select * from line group by company;

说明：通常情况下， group by 子句需要与聚合函数一同使用。

统计非“通恒公司”所属线路的数量和营运里程的平均值、最大值和最小值，并按照线路数升序显示查询结果。

分析：

• 分组之前需要把所属公司为非“通恒公司”的线路排除掉。
• 分组列为“所属公司”。

select company 公司 , count(*) 线路数 , truncate(avg(miles),1) 平均线路里程 ,
max(miles) 最长线路里程 , min(miles) 最短线路里程 from line where company<> ' 通恒公司 ' group by company order by count(*)

思考：如果还想进一步获取每个公司所属线程里程最长的线路名，该如何编写 SQL 语句？

select company 公司 , lineNo 线路号 , max(miles) 最长线路里程 from line group by company

列出公交二公司所有线路的营运里程，按里程数升序显示：

select  lineNo 线路号 , miles 里程数 from line where company=' 公交二公司 ' order by milescompany

分析：

出现上述查询逻辑错误的原因在于：lineNo 为非聚合函数列，但却不是分组列字段。所以， 当我们在使用 group by 子句进行分组查询时，出现在 select 子句中的非聚合函数列一定要出现在 group by 子句的分组字段列表当中。

6.4 多字段分组和 having 子句

group by 子句的分组字段是一个字段列表，即 MySQL 支持按多个字段进行分组。具体的分组策略：分组优先级从左至右，即先按第一个字段进行分组，然后在第一个字段值相同的记录中，再根据第二个字段的值进行分组， 依次类推。

示例：依据公交线路所属公司和营运车辆数，分组统计平均营运里程。

select company 公司 , number 营运车辆数 , truncate(avg(miles),1) 平均营运里程 from line group by company, number

示例：依据公交车辆型号和车辆类型，分组统计车辆数。

select model 型号 , type 类型 , count(*) 车辆数 from vehicle group by model, type

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据。having 子句的位置放在 group by 子句之后，常包含聚合函数。

示例：分别统计每家公交公司中最长线路里程大于 24 的分组信息，按最长线路里程升序显示。

分析：

• 依据公交公司分组统计求出每一家公司最长线路里程。
• 对分组的结果使用 having 子句过滤得到最长线路里程大于 24 的分组信息。

select company 公司 , max(miles) 最长线路里程 from line
group by company having max(miles)>24 order by max(miles)

示例：依据公交车辆型号和车辆类型，统计车辆数大于 30 的分组信息，按车辆数降序显示。

分析：

• 依据型号和类型分组统计求出车辆数。
• 对分组的结果使用 having 子句过滤得到车辆数大于 30 的分组信息。

select model 型号 , type 类型 , count(*) 车辆数 from vehicle
group by model, type having 车辆数 >30 order by 车辆数 desc

示例：统计公交公司为非“通恒公司”的最长线路里程大于 24 的分组信息，按最长线路里程数升序显示。

分析：

• 需要在分组之前排除“通恒公司”。
• 按所属公司分组求出每家公司最长线路里程。
• 使用 having 子句过滤出最长线路里程大于 24 的分组信息。

select company 公司 , max(miles) 最长线路里程 from line where company!=' 通恒公司 ' group by company having max(miles)>24 order by max(miles)

where：分组之前选择记录

having：分组之后过滤数据

注意：

having 子句和 where 子句均用于过滤数据，两者的区别在于：where 子句对结果集进行过滤筛选，而 having 子句则对分组的结果进行筛选。

group by 子句将结果集分为若干个组，使用聚合函数可以对每个组内的数据进行信息统计，有时对各个组进行汇总计算时，需要在分组之后再加上一条汇总记录，这个任务可以通过 with rollup 选项来实现。

示例：统计每个公交公司的线路数量，并在查询结果集的最后一条记录后附上所有公交公司的总线路数。

select company 公司 , count(*) 线路数 from line  group by company with rollup

总结：

• count 和 sum 函数的区别：

sum 函数是对符合条件的记录的数值列求和，count 函数的值是满足查询条件的结果（或记录）个数。count(column) 用于统计 column 列不为 null 的记录个数。

• 如何对查询结果进行过滤之后再分组统计：

先使用 where 子句表达式对查询结果进行过滤筛选；然后，再使用 group by 子句对 where 子句的输出进行分组统计。

• having 子句的用法：

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据。having 子句的位置放在 group by 子句之后，常包含聚合函数。