本文所用的知识点、例子和语法均参考《SQL基础教程》第2版。 本章主要内容有：

对表进行聚合查询对表进行分组为聚合结果指定条件对查询结果进行排序

1 对表进行聚合查询

聚合函数通常用来对表中的列进行计算和或者平均值等。聚合函数不能对null进行汇总，但是count函数可以，count（*）可以查出包含null在内的全部数据的行数。 distinct删除重复值的操作在前面我们已经见过。

聚合函数

常用的5个函数：

count：计算表中的记录数（行数）sum：计算表中数值列中数据的合计值avg：计算表中数值列中数据的平均值max：求出表中任意列中数据的最大值min：求出表中任意列中数据的最小值

所谓聚合函数就是输入多行输出一行。

计算表中数据的行数

计算全部数据的行数用count，例如：

mysql> select count(*) from product; +----------+ | count(*) | +----------+ | 8 | +----------+

也可以计算某一列中非空行数

mysql> select count(purchase_price) from product; +-----------------------+ | count(purchase_price) | +-----------------------+ | 6 | +-----------------------+

括号中带星号是count函数特有的，其他函数如果用星号作参数会报错。count（*）会得到包含null的数据行数，而count（<列名>）会得到null之外的数据行数。

计算合计值

使用sum函数来求销售单价和进货单价的合计值。

mysql> select sum(sale_price),sum(purchase_price) from product; +-----------------+---------------------+ | sum(sale_price) | sum(purchase_price) | +-----------------+---------------------+ | 16780 | 12210 | +-----------------+---------------------+

purchase_price列中有两个空值，在计算和的时候，并不是将两个空值当作0处理，而是有null的两行被无视，不算到计算中去。

计算平均值

计算销售单价和进货单价的平均值，当计算purchase_price时，有两个空值，先把两个空值去掉，然后将剩余不是空值的数加和在取平均（除以的是6不是8）

mysql> select avg(sale_price),avg(purchase_price) from product; +-----------------+---------------------+ | avg(sale_price) | avg(purchase_price) | +-----------------+---------------------+ | 2097.5000 | 2035.0000 | +-----------------+---------------------+

计算最大值和最小值

max、min与sum、avg不同的是，后两个只能对数值类型的列使用，而前两个原则上可适用于任何数据类型的列。

mysql> select max(sale_price),min(purchase_price),max(regist_date),min(regist_date) from product; +-----------------+---------------------+------------------+------------------+ | max(sale_price) | min(purchase_price) | max(regist_date) | min(regist_date) | +-----------------+---------------------+------------------+------------------+ | 6800 | 320 | 2009-11-11 | 2008-04-28 | +-----------------+---------------------+------------------+------------------+

使用聚合函数删除重复值（用distinct）

可在count函数中使用distinct来去重。

mysql> select count(distinct product_type) -> from product; +------------------------------+ | count(distinct product_type) | +------------------------------+ | 3 | +------------------------------+

观察下使用distinct和不使用distinct的结果，以sale_price为例。

mysql> select sum(sale_price),sum(distinct sale_price) from product; +-----------------+--------------------------+ | sum(sale_price) | sum(distinct sale_price) | +-----------------+--------------------------+ | 16780 | 16280 | +-----------------+--------------------------+

16280与16780之间差了500，这个500是个重复数据。

2 对表进行分组

通过使用group by子句可以将表分割聚合键中包含null时，在结果中会以“不确定”行（空行）的形式表现出来使用聚合函数和group by子句需要注意以下几点：1.只能写在select子句中。2. group by子句中不能使用select子句中列的别名。3.group by子句的聚合结果是无序的。4.where子句中不能使用聚合函数

group by子句

语法如下：

select <列名1>,<列名2>,<列名3>,…… from <表名> group by <列名1>,<列名2>,<列名3>,……; 按照商品种类统计数据行数

mysql> select product_type,count(*) -> from product -> group by product_type; +--------------+----------+ | product_type | count(*) | +--------------+----------+ | 办公用品 | 2 | | 厨房用具 | 4 | | 衣服 | 2 | +--------------+----------+

group by子句中指定的列称为聚合键或者分组列。 SQL子句的书写顺序为：select>from>where>group by

聚合键中包含null的情况

mysql> select purchase_price,count(*) from product -> group by purchase_price; +----------------+----------+ | purchase_price | count(*) | +----------------+----------+ | NULL | 2 | | 320 | 1 | | 500 | 1 | | 790 | 1 | | 2800 | 2 | | 5000 | 1 | +----------------+----------+

聚合键中包含null时，在结果中会以”不确定“行（空行）的形式表现出来。

使用where子句时group by的执行结果

语法如下： select <列名1>,<列名2>,<列名3>,…… from <表名> where group by <列名1>,<列名2>,<列名3>,……; 此语句先用where进行过滤，再进行汇总。

mysql> select purchase_price,count(*) from product where product_type='衣服' group by purchase_price; +----------------+----------+ | purchase_price | count(*) | +----------------+----------+ | 500 | 1 | | 2800 | 1 | +----------------+----------+

group by 和where并用时，执行顺序如下：from>where>group by>select

注意事项：

使用group by 子句时，select子句中不能出现聚合键之外的列名。例如聚合键中若只有purchase_price，那么在select中就只能有purchase_price列，不能有product_name列。在group by子句中不能使用select子句中定义的别名。（这样的写法在mysql,postgresql中都不会执行错误，但是起别名这样的写法在其他DBMS中不是通用的。group by子句结果的显示是无序的。在where中使用聚合函数会报错，只有select子句和having子句（以及order by子句）中能够使用聚合函数。

3 为聚合结果指定条件

用count函数等对表中的数据进行汇总操作时，不能用where语句，要用到having子句。having子句要写在group by子句之后。where子句是用来指定数据行的条件，having子句用来指定分组的条件。

having子句

语法如下： select <列名1>,<列名2>,<列名3>,…… from <表名> group by <列名1>,<列名2>,<列名3>,…… having<分局结果对应的条件>; select语句的顺序：select>from>where>group by>having 例如，将product_type多数据分组，然后提取出包含的数据行数为2的结果

mysql> select product_type,count(*) from product group by product_type having count(*)=2; +--------------+----------+ | product_type | count(*) | +--------------+----------+ | 办公用品 | 2 | | 衣服 | 2 | +--------------+----------+

接下来，我们还是按照product_type进行分组，把条件变成 sale_price的平均值大于等于2500

mysql> select product_type,avg(sale_price) from product group by product_type having avg(sale_price)>=2500; +--------------+-----------------+ | product_type | avg(sale_price) | +--------------+-----------------+ | 厨房用具 | 2795.0000 | | 衣服 | 2500.0000 | +--------------+-----------------+

having子句的构成要素

常数聚合函数group by子句中指定的列名（即聚合键）

在前面例子中，having count(*)=2 ,count是聚合函数，2 是常数，满足上述条件。

having与where的使用情况： where子句=指定行所对应的条件 having子句=指定组所对应的条件 聚合键所对应的条件不应该写在having子句中，应该写在where子句中。 where子句的运行速度比having快。

4 对查询结果进行排序（order by)

在order by子句中列名的后面使用asc进行升序，使用desc进行降序order by子句中可以指定多个排序键排序键中包含null时，会在开头或末尾进行汇总order by子句中可以使用select子句中定义的列的别名order by子句中可使用select子句中未出现的列或者聚合函数order by子句中不能使用列的编号

order by子句

语法为： select <列名1>,<列名2>,<列名3>,…… from <表名> order by <排序基准列1>,<排序基准列2>,…… 书写顺序：select>from>where>group by>having>order by 例如，按照销售单价升序排序（默认是升序）

mysql> select product_id,product_name,sale_price,purchase_price from product order by sale_price; +------------+--------------+------------+----------------+ | product_id | product_name | sale_price | purchase_price | +------------+--------------+------------+----------------+ | 0008 | 圆珠笔 | 100 | NULL | | 0002 | 打孔器 | 500 | 320 | | 0006 | 叉子 | 500 | NULL | | 0007 | 擦菜板 | 880 | 790 | | 0001 | T恤衫 | 1000 | 500 | | 0004 | 菜刀 | 3000 | 2800 | | 0003 | 运动T恤 | 4000 | 2800 | | 0005 | 高压锅 | 6800 | 5000 | +------------+--------------+------------+----------------+

指定升序或降序

例如按照销售单价降序排列：

mysql> select product_id,product_name,sale_price,purchase_price from product order by sale_price desc; +------------+--------------+------------+----------------+ | product_id | product_name | sale_price | purchase_price | +------------+--------------+------------+----------------+ | 0005 | 高压锅 | 6800 | 5000 | | 0003 | 运动T恤 | 4000 | 2800 | | 0004 | 菜刀 | 3000 | 2800 | | 0001 | T恤衫 | 1000 | 500 | | 0007 | 擦菜板 | 880 | 790 | | 0002 | 打孔器 | 500 | 320 | | 0006 | 叉子 | 500 | NULL | | 0008 | 圆珠笔 | 100 | NULL | +------------+--------------+------------+----------------+

指定多个排序列

在上述例子中，sale_price=500的有两个，其排序是随机的。如果想要更细致的排序，就要再添加一个排序键。以添加的排序键为升序为例

mysql> select product_id,product_name,sale_price,purchase_price from product order by sale_price ,product_id; +------------+--------------+------------+----------------+ | product_id | product_name | sale_price | purchase_price | +------------+--------------+------------+----------------+ | 0008 | 圆珠笔 | 100 | NULL | | 0002 | 打孔器 | 500 | 320 | | 0006 | 叉子 | 500 | NULL | | 0007 | 擦菜板 | 880 | 790 | | 0001 | T恤衫 | 1000 | 500 | | 0004 | 菜刀 | 3000 | 2800 | | 0003 | 运动T恤 | 4000 | 2800 | | 0005 | 高压锅 | 6800 | 5000 | +------------+--------------+------------+----------------+

null的顺序

使用含有null的列作为排序键时，null会在结果的开头或末尾汇总显示。

mysql> select product_id,product_name,sale_price,purchase_price from product order by purchase_price; +------------+--------------+------------+----------------+ | product_id | product_name | sale_price | purchase_price | +------------+--------------+------------+----------------+ | 0006 | 叉子 | 500 | NULL | | 0008 | 圆珠笔 | 100 | NULL | | 0002 | 打孔器 | 500 | 320 | | 0001 | T恤衫 | 1000 | 500 | | 0007 | 擦菜板 | 880 | 790 | | 0003 | 运动T恤 | 4000 | 2800 | | 0004 | 菜刀 | 3000 | 2800 | | 0005 | 高压锅 | 6800 | 5000 | +------------+--------------+------------+----------------+

在排序键中使用先使用的别名

group by子句不能使用select子句中定义的别名，但是order by子句中允许。例如：

**mysql> select product_id as id,product_name ,sale_price as sp,purchase_price -> from product -> order by sp,id; +------+--------------+------+----------------+ | id | product_name | sp | purchase_price | +------+--------------+------+----------------+ | 0008 | 圆珠笔 | 100 | NULL | | 0002 | 打孔器 | 500 | 320 | | 0006 | 叉子 | 500 | NULL | | 0007 | 擦菜板 | 880 | 790 | | 0001 | T恤衫 | 1000 | 500 | | 0004 | 菜刀 | 3000 | 2800 | | 0003 | 运动T恤 | 4000 | 2800 | | 0005 | 高压锅 | 6800 | 5000 | +------+--------------+------+----------------+

那么为什么group by子句不能使用别名，而order by中可以呢？因为使用having子句时select语句的顺序是：

from->where->group by->having->select->order by。select子句的执行顺序在group by之后，order by之前（所以having子句也不能使用别名）。

order by子句中可以使用的列

order by 子句可以使用存在于表中，但并不包含在select子句之中的列。例如：

mysql> select product_name ,sale_price,purchase_price from product order by product_id; +--------------+------------+----------------+ | product_name | sale_price | purchase_price | +--------------+------------+----------------+ | T恤衫 | 1000 | 500 | | 打孔器 | 500 | 320 | | 运动T恤 | 4000 | 2800 | | 菜刀 | 3000 | 2800 | | 高压锅 | 6800 | 5000 | | 叉子 | 500 | NULL | | 擦菜板 | 880 | 790 | | 圆珠笔 | 100 | NULL | +--------------+------------+----------------+ -- 使用聚合函数 mysql> select product_type,count(*) from product group by product_type order by count(*); +--------------+----------+ | product_type | count(*) | +--------------+----------+ | 衣服 | 2 | | 办公用品 | 2 | | 厨房用具 | 4 | +--------------+----------+ mysql> select product_type from product group by product_type order by count(*); +--------------+ | product_type | +--------------+ | 衣服 | | 办公用品 | | 厨房用具 | +--------------+

不要使用列编号

下面的两种代码执行结果是相同的

mysql> -- 通过列名指定 mysql> select product_id,product_name,sale_price,purchase_price from product order by sale_price desc,product_id; mysql> -- 通过列编号指定 mysql> select product_id,product_name,sale_price,purchase_price from product order by 3 desc,1; +------------+--------------+------------+----------------+ | product_id | product_name | sale_price | purchase_price | +------------+--------------+------------+----------------+ | 0005 | 高压锅 | 6800 | 5000 | | 0003 | 运动T恤 | 4000 | 2800 | | 0004 | 菜刀 | 3000 | 2800 | | 0001 | T恤衫 | 1000 | 500 | | 0007 | 擦菜板 | 880 | 790 | | 0002 | 打孔器 | 500 | 320 | | 0006 | 叉子 | 500 | NULL | | 0008 | 圆珠笔 | 100 | NULL | +------------+--------------+------------+----------------+