阅读整理自《MySQL 必知必会》- 朱晓峰，详细内容请登录 极客时间 官网购买专栏。
文章目录SUM()AVG()MAX() MIN()COUNT()
count(*)count(字段)
MySQL 中有 5 种聚合函数较为常用，分别是求和函数 SUM()、求平均函数 AVG()、最大值函数 MAX()、最小值函数 MIN() 和计数函数 COUNT()。
一个超市项目需求：超市经营者提出，他们需要统计某个门店，每天、每个单品的销售情况，包括销售数量和销售金额等。这里涉及 3 个数据表，具体信息如下所示：
销售明细表（demo.transactiondetails)
| transactionid | itemnumber | quantity | price | salesvalue |
|---|---|---|---|---|
| 1 | 1 | 2 | 89 | 178 |
| 1 | 2 | 5 | 5 | 25 |
| 2 | 1 | 3 | 89 | 267 |
| 2 | 2 | 6 | 5 | 30 |
| 3 | 1 | 1 | 89 | 89 |
| 3 | 2 | 10 | 5 | 50 |
销售单头表（demo.transactionhead)
| transactionid | transactionno | cashierid | memberid | operatorid | transdate |
|---|---|---|---|---|---|
| 1 | 0120201201000001 | 1 | 1 | 1 | 2020-12-01 14:25:56 |
| 2 | 0120201202000001 | 1 | NULL | 2 | 2020-12-02 10:50:50 |
| 3 | 0120201202000002 | 1 | NULL | 1 | 2020-12-02 12:10:05 |
商品信息表（demo.goodsmaster）
| itemnumber | barcode | goodsname | specification | unit | salesprice |
|---|---|---|---|---|---|
| 1 | 1 | 书 | 16开 | 本 | 89 |
| 2 | 2 | 笔 | 10支装 | 包 | 5 |
Mysql 数据准备：
demo.transactiondetails
create table demo.transactiondetails ( transactionid int, itemnumber int, quantity decimal(10,3), price decimal(5,2), salesvalue decimal(5,2) ); describe transactiondetails; insert into demo.transactiondetails (transactionid, itemnumber, quantity, price, salesvalue) values (1, 1, 2, 89, 178); insert into demo.transactiondetails (transactionid, itemnumber, quantity, price, salesvalue) values (1, 2, 5, 5, 25); insert into demo.transactiondetails (transactionid, itemnumber, quantity, price, salesvalue) values (2, 1, 3, 89, 267); insert into demo.transactiondetails (transactionid, itemnumber, quantity, price, salesvalue) values (2, 2, 6, 5, 30); insert into demo.transactiondetails (transactionid, itemnumber, quantity, price, salesvalue) values (3, 1, 1, 89, 89); insert into demo.transactiondetails (transactionid, itemnumber, quantity, price, salesvalue) values (3, 2, 10, 5, 50);
mysql> select * from demo.transactiondetails; +---------------+------------+----------+-------+------------+ | transactionid | itemnumber | quantity | price | salesvalue | +---------------+------------+----------+-------+------------+ | 1 | 1 | 2.000 | 89.00 | 178.00 | | 1 | 2 | 5.000 | 5.00 | 25.00 | | 2 | 1 | 3.000 | 89.00 | 267.00 | | 2 | 2 | 6.000 | 5.00 | 30.00 | | 3 | 1 | 1.000 | 89.00 | 89.00 | | 3 | 2 | 10.000 | 5.00 | 50.00 | +---------------+------------+----------+-------+------------+
demo.transactionhead
create table demo.transactionhead ( transactionid int, transactionno text, cashierid int, memberid int, operatorid int, transdate datetime ); describe transactionhead; insert into demo.transactionhead (transactionid, transactionno, cashierid, memberid, operatorid, transdate) values (1, '0120201201000001', 1, 1, 1, '2020-12-01 14:25:56'); insert into demo.transactionhead (transactionid, transactionno, cashierid, memberid, operatorid, transdate) values (2, '0120201202000001', 1, null, 2, '2020-12-02 10:50:50'); insert into demo.transactionhead (transactionid, transactionno, cashierid, memberid, operatorid, transdate) values (3, '0120201202000002', 1, null, 1, '2020-12-02 12:10:05'); select * from demo.transactionhead;
mysql> select * from demo.transactionhead; +---------------+------------------+-----------+----------+------------+---------------------+ | transactionid | transactionno | cashierid | memberid | operatorid | transdate | +---------------+------------------+-----------+----------+------------+---------------------+ | 1 | 0120201201000001 | 1 | 1 | 1 | 2020-12-01 14:25:56 | | 2 | 0120201202000001 | 1 | NULL | 2 | 2020-12-02 10:50:50 | | 3 | 0120201202000002 | 1 | NULL | 1 | 2020-12-02 12:10:05 | +---------------+------------------+-----------+----------+------------+---------------------+
demo.goodsmaster
create table demo.goodsmaster ( itemnumber int, barcode int, goodsname text, specification text, unit text, salesprice decimal(10,3) ); insert into demo.goodsmaster (itemnumber, barcode, goodsname, specification, unit, saleprice) values (1, 1, 书, 16开, 本, 89.00); insert into demo.goodsmaster (itemnumber, barcode, goodsname, specification, unit, saleprice) values (2, 2, 笔, 10支装, 支, 5.00);
mysql> select * from demo.goodsmaster; +------------+---------+-----------+---------------+------+-----------+ | itemnumber | barcode | goodsname | specification | unit | saleprice | +------------+---------+-----------+---------------+------+-----------+ | 1 | 0001 | 书 | 16开 | 本 | 89.00 | | 2 | 0002 | 笔 | 10支装 | 支 | 5.00 | +------------+---------+-----------+---------------+------+-----------+
SUM()
SUM(）函数可以返回指定字段值的和。
可以用它来获得用户某个门店，每天，每种商品的销售总计数据：
mysql> select left(b.transdate, 10), c.goodsname, sum(a.quantity), sum(a.salesvalue)
-> from demo.transactiondetails as a
-> join demo.transactionhead as b on (b.transactionid = a.transactionid)
-> join demo.goodsmaster as c on (c.itemnumber = a.itemnumber)
-> group by left(b.transdate, 10), c.goodsname
-> order by left(b.transdate, 10), c.goodsname;
+-----------------------+-----------+-----------------+-------------------+
| left(b.transdate, 10) | goodsname | sum(a.quantity) | sum(a.salesvalue) |
+-----------------------+-----------+-----------------+-------------------+
| 2020-12-01 | 书 | 2.000 | 178.00 |
| 2020-12-01 | 笔 | 5.000 | 25.00 |
| 2020-12-02 | 书 | 4.000 | 356.00 |
| 2020-12-02 | 笔 | 16.000 | 80.00 |
+-----------------------+-----------+-----------------+-------------------+
left(str，n)：表示返回字符串 str 最左边的 n 个字符。这里 left（a.transdate,10）表示返回交易时间字符串最左边的 10 个字符。在 MySQL 中，DATETIME 类型的默认格式是：YYYY-MM-DD，也就是说，年份 4 个字符，之后是“-”，然后是月份 2 个字符，之后又是“-”，然后是日 2 个字符，所以完整的年月日是 10 个字符。用户要求按照日期统计，所以，我们需要从日期时间数据中，把年月日的部分截取出来。
order by：表示按照指定的字段排序。超市经营者指定按照日期和单品统计，那么，统计的结果按照交易日期和商品名称的顺序排序，会更加清晰。
这个查询是如何执行的，用图表来直观地演示一下各个步骤：
完成 3 个表的连接（省略了一些在这一步不重要的字段）：
对结果集按照交易时间和商品名称进行分组，可以分成下面 4 组：
12.1号-商品1
12.1号-商品2
12.2号-商品1
12.2号-商品2
对各组的销售数量和销售金额进行统计，并且按照交易日期和商品名称排序：
+-----------------------+-----------+-----------------+-------------------+ | left(b.transdate, 10) | goodsname | sum(a.quantity) | sum(a.salesvalue) | +-----------------------+-----------+-----------------+-------------------+ | 2020-12-01 | 书 | 2.000 | 178.00 | | 2020-12-01 | 笔 | 5.000 | 25.00 | | 2020-12-02 | 书 | 4.000 | 356.00 | | 2020-12-02 | 笔 | 16.000 | 80.00 | +-----------------------+-----------+-----------------+-------------------+
如果用户需要知道全部商品销售的总计数量和总计金额，我们也可以把数据集的整体看作一个分组，进行计算。这样就不需要分组关键字 GROUP BY，以及排序关键字 ORDER BY 了，甚至不需要从关联表中获取数据，也就不需要连接了。就像下面这样：
mysql> select sum(quantity), sum(salesvalue)
-> from demo.transactiondetails;
+---------------+-----------------+
| sum(quantity) | sum(salesvalue) |
+---------------+-----------------+
| 27.000 | 639.00 |
+---------------+-----------------+
求和函数获取的是分组中的合计数据，所以要对分组的结果有准确的把握，否则就很容易搞错。
这也就是说，要知道是按什么字段进行分组的。如果是按多个字段分组，要知道字段之间有什么样的层次关系；如果是按照以字段作为变量的某个函数进行分组的，要知道这个函数的返回值是什么，返回值又是如何影响分组的等。
AVG()
通过计算分组内指定字段值的和，以及分组内的记录数，算出分组内指定字段的平均值。
举例，用户需要计算每天、每种商品，平均一次卖出多少个、多少钱：
mysql> select left(b.transdate, 10), c.goodsname, avg(a.quantity), avg(a.salesvalue)
-> from demo.transactiondetails as a
-> join demo.transactionhead as b on (b.transactionid = a.transactionid)
-> join demo.goodsmaster as c on (c.itemnumber = a.itemnumber)
-> group by left(b.transdate, 10), c.goodsname
-> order by left(b.transdate, 10), c.goodsname;
+-----------------------+-----------+-----------------+-----------------+
| left(b.transdate, 10) | goodsname | avg(a.quantity) | avg(salesvalue) |
+-----------------------+-----------+-----------------+-----------------+
| 2020-12-01 | 书 | 2.0000000 | 178.000000 |
| 2020-12-01 | 笔 | 5.0000000 | 25.000000 |
| 2020-12-02 | 书 | 2.0000000 | 178.000000 |
| 2020-12-02 | 笔 | 8.0000000 | 40.000000 |
+-----------------------+-----------+-----------------+-----------------+
MAX() MIN()
MAX() 表示获取指定字段在分组中的最大值，MIN() 表示获取指定字段在分组中的最小值。
假如用户要求计算每天里的一次销售的最大数量和最大金额：
mysql> select left(b.transdate, 10), max(a.quantity), max(a.salesvalue)
-> from demo.transactiondetails as a
-> join demo.transactionhead as b on (b.transactionid = a.transactionid)
-> group by left(b.transdate, 10)
-> order by left(b.transdate, 10);
+-----------------------+-----------------+-------------------+
| left(b.transdate, 10) | max(a.quantity) | max(a.salesvalue) |
+-----------------------+-----------------+-------------------+
| 2020-12-01 | 5.000 | 178.00 |
| 2020-12-02 | 10.000 | 267.00 |
+-----------------------+-----------------+-------------------+
千万不要以为 max(a.quantity), max(a.salesvalue) 算出的结果一定是同一条记录的数据。实际上，MySQL 是分别计算的。
max(字段) 这个函数返回分组集中最大的那个值。如果要查询max(字段1)和max(字段2) ，且它们是相互独立、分别计算的，那么就不要想当然地认为结果在同一条记录上。
COUNT()要计算记录数，就要用到 COUNT() 函数了。这个函数有两种情况：
COUNT（*）：统计一共有多少条记录；COUNT（字段）：统计有多少个不为空的字段值 count(*)
如果 count(*) 与 group by 一起使用，就表示统计分组内有多少条数据。它也可以单独使用，这就相当于数据集全体是一个分组，统计全部数据集的记录数。
mysql> select count(*) from demo.transactiondetails; +----------+ | count(*) | +----------+ | 6 | +----------+
超市经营者想知道，每天、每种商品都有几次销售，我们就需要按天、按商品名称，进行分组查询：
mysql> select left(b.transdate, 10), c.goodsname, count(*)
-> from demo.transactiondetails as a
-> join demo.transactionhead as b on (b.transactionid = a.transactionid)
-> join demo.goodsmaster as c on (c.itemnumber = a.itemnumber)
-> group by left(b.transdate, 10), c.goodsname
-> order by left(b.transdate, 10), c.goodsname;
+-----------------------+-----------+----------+
| left(b.transdate, 10) | goodsname | count(*) |
+-----------------------+-----------+----------+
| 2020-12-01 | 书 | 1 |
| 2020-12-01 | 笔 | 1 |
| 2020-12-02 | 书 | 2 |
| 2020-12-02 | 笔 | 2 |
+-----------------------+-----------+----------+
count(字段)
COUNT（字段）用来统计分组内这个字段的值出现了多少次。如果字段值是空，就不统计。
mysql> select * from demo.goodsmaster; +------------+---------+-----------+---------------+------+-----------+ | itemnumber | barcode | goodsname | specification | unit | saleprice | +------------+---------+-----------+---------------+------+-----------+ | 1 | 0001 | 书 | 16开 | 本 | 89.00 | | 2 | 0002 | 笔 | 10支装 | 支 | 5.00 | | 3 | 003 | 纸 | NULL | 张 | 0.10 | +------------+---------+-----------+---------------+------+-----------+ 3 rows in set (0.01 sec) mysql> select count(goodsname) from demo.goodsmaster; +------------------+ | count(goodsname) | +------------------+ | 3 | +------------------+ 1 row in set (0.00 sec) mysql> select count(specification) from demo.goodsmaster; +----------------------+ | count(specification) | +----------------------+ | 2 | +----------------------+ 1 row in set (0.00 sec)
