MySQL — 聚合函数

来自MySQL的学习笔记，写的不对的地方大家多多指教哦

聚合函数能够使分组更加的高效，聚合函数主要有COUNT()、SUM()、AVG()、MAX()、MIX()，且聚合函数需要与GROUP BY一起使用。

内容中涉及了三张表，分别是：

销售明细表（transactiondetails)：流水编号、商品编号、销售数量、销售金额、销售总金额

image.png

销售单头表（transactionhead)：流水编号、流水单号、收款机编号、会员编号、操作员编号、交易时间

image.png

商品信息表（goodsmaster）：商品编号、条码、商品名称、商品类型、单位、销售金额

image.png

一、COUNT() — 计数

• COUNT（*）：统计一共有多少条记录；
• COUNT（字段）：统计有多少个不为空的字段值。

1.COUNT（*）

COUNT()单独使用，表示统计表中所有的记录数；如果COUNT(*)与GROUP BY一起使用，则表示统计分组中的数据。

例1：查询销售明细表（transactiondetails)中的所有记录数

SELECT COUNT(*) FROM transactiondetails

执行语句，结果为：

image.png

例2：查询销售明细表（transactiondetails)，对商品编号进行分组，计算每个商品编号对应的记录数是多少？

SELECT transactionid,COUNT(*) FROM transactiondetails GROUP BY transactionid

执行语句，结果为：

image.png

2.COUNT（字段）

COUNT（字段）用来统计分组内这个字段的值出现了多少次。如果字段值是空，就不统计。

例1：查询销售单头表（transactionhead)，统计字段“operatorid”出现的次数

SELECT COUNT(operatorid) FROM transactionhead

--- 结果为：3

例2：查询销售单头表（transactionhead)，统计字段“memberid”出现的次数

SELECT COUNT(memberid) FROM transactionhead

--- 结果为：1

由例子2得知：根据字段进行统计的，只计算非空部分的数量，为空的记录不计算。

二、SUM() — 求和

SUM()函数可以返回指定字段值的和。

例子：根据上面三张表，查询每天，每种商品的销售总计数据

SELECT
    LEFT(a.transdate,10),
    c.goodsname,
    SUM(b.quantity),
    SUM(b.salesvalue)
FROM
     transactionhead AS a
JOIN
    transactiondetails AS b
ON
    (a.transactionid=b.transactionid)
JOIN
    goodsmaster AS c
ON
    (b.Itemnumber=c.itemnumber)
GROUP BY
    LEFT(a.transdate,10),c.goodsname

执行语句，结果为：

image.png

知识点：

由于实际日期有包含具体时间，所以这边用LEFT关键字截取前面的年月日，用法为：

LEFT(str，n)：表示返回字符串 str 最左边的 n 个字符。这里的 LEFT（a.transdate,10），表示返回交易时间字符串最左边的 10 个字符。

三、AVG() — 平均值

AVG()函数可以返回指定字段值的平均值。

例子：根据上面三张表，查询每天，每种商品的平均销售数量和平均销售总金额

SELECT
    LEFT(a.transdate,10),
    c.goodsname,
    AVG(b.quantity),
    AVG(b.salesvalue)
FROM
     transactionhead AS a
JOIN
    transactiondetails AS b
ON
    (a.transactionid=b.transactionid)
JOIN
    goodsmaster AS c
ON
    (b.Itemnumber=c.itemnumber)
GROUP BY
    LEFT(a.transdate,10),c.goodsname

执行语句，结果为：

image.png

四、MAX() — 最大值

MAX()函数可以返回指定字段值的最大值。

例子：根据上面三张表，查询每天，每种商品的最大的销售数量和最大的销售金额

SELECT
    LEFT(a.transdate,10),
    c.goodsname,
    MAX(b.quantity),
    MAX(b.salesvalue)
FROM
     transactionhead AS a
JOIN
    transactiondetails AS b
ON
    (a.transactionid=b.transactionid)
JOIN
    goodsmaster AS c
ON
    (b.Itemnumber=c.itemnumber)
GROUP BY
    LEFT(a.transdate,10),c.goodsname

执行语句，结果为：

image.png

查看上面的结果会发现，“salesvalue”字段的最大值是错误的，显示的都是最小值，为什么呢？

因为“salesvalue”字段的类型是varchar类型，而“quantity”字段类型是int类型，在比较字符串的大小时，使用MAX()查询一个字符串类型的字段时，比较字符串类型大小是先比较首字符的ASCII码的大小，然后依次往后进行比较的。首字符大的即会默认为最大，首字符最小的即会默认为最小。

解决方法：

MAX(b.salesvalue+0)，在字段名后面+0即可，对上面的查询语句进行优化后，得到的结果为：

image.png

五、MIN() — 最小值

MIN()函数可以返回指定字段值的最小值。

例子：根据上面三张表，查询每天，每种商品的最小的销售数量和最小的销售金额

SELECT
    LEFT(a.transdate,10),
    MIN(b.quantity),
    MIN(b.salesvalue+0)
FROM
     transactionhead AS a
JOIN
    transactiondetails AS b
ON
    (a.transactionid=b.transactionid)
JOIN
    goodsmaster AS c
ON
    (b.Itemnumber=c.itemnumber)
GROUP BY
    LEFT(a.transdate,10)

执行上面的语句，结果为：

image.png