数据计算与分组统计

本文讨论MySQL中如何进行数据的计算、分组汇总等操作，包括常用的算术运算、生成计算列、统计函数、select语句中group by和having子句的运用等。本文测试用表和数据创建SQL如下，可以复制到HeidiSQL中执行。

MySQL

use cdb_demo;

create table t2(
recid bigint not null auto_increment primary key,
f1 varchar(30) not null unique,
f2 varchar(30),
f3 varchar(15),
f4 int not null default 0,
f5 decimal(15,2),
f6 decimal(15,2),
f7 decimal(15,2)
)engine=innodb default charset=utf8mb4;

insert into t2(f1,f2,f3,f4,f5,f6,f7)
values('G0001','王一军','A组',1,65,75,85),
('G0002','张三','A组',2,68,69,75),
('G0003','李四','A组',2,78,78,81),
('G0004','刘彬','A组',1,85,79,82),
('G0005','苏红','A组',2,60,73,81),
('G0006','范志伟','B组',1,75,76,82),
('G0007','刘文博','B组',1,59,71,75),
('G0008','李瑞','B组',2,62,69,76),
('G0009','万鑫','B组',1,66,76,83),
('G0010','胡洋','B组',1,75,79,90),
('G0011','刘丽','C组',2,80,81,89),
('G0012','李兰','C组',2,71,73,79),
('G0013','李玉','C组',2,55,63,65),
('G0014','张文','C组',1,69,77,81),
('G0015','雷正','C组',1,71,73,80);

代码中创建了t2表，其中的字段可以假设为如下含义：

f1，学号。
f2，姓名。
f3，组别。
f4，性别。约定1表示男，2表示女。
f5、f6、f7，分别为三门功课的分数。

统计函数

首先了解5个基本的统计函数：

count()，计数，统计满足条件的记录数量。
sum()，求和。
avg()，求算术平均数。
min()，求最小值。
max()，求最大值。

下面的代码可以计算f5字段数据的平均数。

MySQL

use cdb_demo;

select avg(f5) as f5_avg from t2;

本例使用avg(f5)计算f5字段的平均数，然后使用as关键字定义计算结果的列名为f5_avg。

运行结果如下图所示。

查询结果

如果只需要显示1位小数，可以使用format()函数格式化，如下面的代码。

MySQL

use cdb_demo;

select format(avg(f5),1) as f5_avg from t2;

运行结果如下图所示。

查询结果

本例，format()函数使用了2个参数，其中，参数1指定数据或数据字段；参数2指定保留的小数位，多出的数据会四舍五入。

count()函数可以计算满足条件的记录数量，其参数可以指定字段名，也可以使用通配符*，如下面的代码可以计算f5大于等于60的记录数量。

MySQL

use cdb_demo;

select count(*) as f5_ge60 from t2 where f5>=60;

代码执行结果如下图所示。

查询结果

本例，count(*)修改为count(f5)可以得到相同的查询结果，甚至是修改为表中任意一个字段名都可以得到相同的结果。

计算列

前面通过统计函数可以对每一列的数据进行计算，而计算每一名同学的分数情况，如计算总分和平均分时，则可以通过增加计算列来完成，如下面的代码。

MySQL

use cdb_demo;

select f1,f2,f5,f6,f7,
(f5+f6+f7) as f_sum, 
format((f5+f6+f7)/3,1) as f_avg
from t2;

本例，首先会直接显示f1、f2，f5、f6、f7字段数据；然后通过f5、f6、f7字段数据求和得出每位同学的总分，并将数据列命名为f_sum；最后使用f5、f6、f7的和除以3得到三门功课的平均分，结果保留1位小数，并将数据列命名为f_avg。代码执行结果如下图所示。

查询结果

对于计算列的数据，还可以进行排序，如下面的代码会按总分从高到低排序。

MySQL

use cdb_demo;

select f1,f2,f5,f6,f7,
(f5+f6+f7) as f_sum, 
format((f5+f6+f7)/3,1) as f_avg
from t2
order by f_sum desc;

代码执行结果如下图所示。

查询结果

分组统计

分组统计是数据处理中常用的统计方法，如按性别统计、按组别统计、按区域统计等等。在select语句中可以使用group by子句添加分组字段，如下面的代码，可以按性别（f4）计算f5字段的平均数。

MySQL

use cdb_demo;

select f4,format(avg(f5),1) as f5_avg 
from t2
group by f4;

代码运行结果如下图所示。

查询结果

数据统计时，也可以对同一分组使用多个函数计算多个字段的数据，如下面的代码分别计算了不同分组（f3）中f5、f6、f7的总分和平均分。

MySQL

use cdb_demo;

select f3,
format(sum(f5),1) as f5_sum,format(avg(f5),1) as f5_avg,
format(sum(f6),1) as f6_sum,format(avg(f6),1) as f6_avg,
format(sum(f7),1) as f7_sum,format(avg(f7),1) as f7_avg
from t2
group by f3;

代码执行结果如下图所示。

查询结果

对分组计算结果排序时同样可以使用order by子句，如下面的代码会对不同分组（f3）的f5字字段的平均分降序排列。

MySQL

use cdb_demo;

select f3,format(avg(f5),1) as f5_avg
from t2
group by f3
order by f5_avg desc;

代码执行结果如下图所示。

查询结果

需要按条件过滤分组结果时需要使用having子句，不能使用where子句，如下面的代码会显示f5字段平均分小于70的组别。

MySQL

use cdb_demo;

select f3,format(avg(f5),1) as f5_avg
from t2
group by f3
having f5_avg<70
order by f5_avg desc;

代码执行结果如下图所示。

查询结果

下面的代码看一个较复杂一些的分组统计。

MySQL

use cdb_demo;

select f3,f4,
format(avg(f5),1) as f5_avg,
format(avg(f6),1) as f6_avg,
format(avg(f7),1) as f7_avg
from t2
group by f3,f4;

代码执行结果如下图所示。

查询结果

本例，在group by子句中使用了2个字段，首先是组别（f3），然后是性别（f4），其功能是分别统计了各分组中不同性别三门功课（f5、f6、f7）的平均分。

将结果保存到Excel工作表

在HeidiSQL中，可以将查询结果很方便的保存到Excel工作表中。查询执行完成后，可以通过HeidiSQL菜单“工具”>>“导出表格的行”项打开导出窗口，如下图所示。

导出表格的行

如图中所示，使用默认选项并点击“确定”按钮，此时，查询结果就会保存到剪贴板中；接下来，打开Excel中需要保存数据的表，在表中粘贴数据即可。需要注意的是，如果数据有包含有前导0、超15位等特殊数字格式时，可在工作表中先将这些列的单元格格式设置为“文本”，其它数据列可以根据需要设置单元格格式。按正确的格式粘贴数据后，就可以在Excel中进一步处理数据了。此外，在WPS表格等电子表格类应用中也可以进行类似操作。