存储在数据库的海量数据,不仅可以根据项目需求实现数据的简单增、删、改、查操作,还可用于数据的统计分析,让每条数据变得更有价值。例如,电商网站根据用户的偏好(经常浏览/购买的商品种类)为其推荐最新最火爆的商品。在MySQL中提供分组操作的目的就是为了统计,其中为了方便统计还提供了大量的聚合函数。本节将针对MySQL中分组和聚合函数的使用进行详细讲解。
分组
在MySQL中,可以使用GROUPBY根据一个或多个字段进行分组,字段值相同的为一组。另外,对于分组的数据可以使用HAVING进行条件筛选。接下来为了便于读者理解,通过几种常用的方式对分组进行详细讲解。
- 分组统计
在查询数据时,在WHERE条件后添加GROUPBY即可根据指定的字段进行分组,其基本语法格式如下。
SELECT [select 选项] 字段列表 FROM 数据表名
[WHERE 条件表达式] GROUP BY 字段名;
上述语法在MySQL5.7中分组后,SELECT获取的字段列表只能是GROUPBY分组的字段,或使用了聚合函数的非分组字段,若在获取非分组字段时没有使用聚合函数,MySQL会报错误提示。
为了读者更好地理解,下面通过聚合函数MAX()获取每个分类下商品的最高价格。具体SQL语句及执行结果如下。
mysql> SELECT category_id, MAX(price) FROM sh_goods GROUP BY category_id;
+-------------+------------+
| category_id | MAX(price) |
+-------------+------------+
| 3 | 15.00 |
| 6 | 1999.00 |
| 8 | 69.00 |
| 9 | 109.00 |
| 10 | 2000.00 |
| 12 | 5999.00 |
| 15 | 299.00 |
| 16 | 48.00 |
+-------------+------------+
8 rows in set (0.00 sec)
上述语句中,根据categoryid进行分组,然后获取每个category_id分组下商品的最高价格。其中,MAX()是MySQL提供的一个聚合函数,用于获取price字段的最大值。
另外,在MySQL 5.6等老版本中,分组后获取的字段列表,若非分组字段没有使用聚合函数,默认情况下只保留每组中的第一条记录,但是此操作在MySQL5.7及以上版本中已被禁止。那么,为了避免项目开发MySQL版本升级带来的问题,推荐读者在编写分组SQL语句时按照MySQL5.7版本更严格的方式进行设计。
- 分组排序
在MySQL中,默认情况下为分组操作的字段提供了升序排序的功能,因此在分组时可以为指定的字段进行升序或降序排序,其基本语法格式如下。
SELECT [select 选项] 字段列表 FROM 数据表名
[WHERE 条件表达式] GROUP BY 字段名 [ASC | DESC];
需要注意的是,GROUPBY分组排序的实现不需要使用ORDERBY,直接在分组字段后添加ASC(升序,默认值可省略)或DESC(降序)即可。
下面根据sh_goods表中的分类id进行分组降序操作,查询并显示分组后每组的商品id以及商品的名称。具体SQL语句及执行结果如下。
mysql> SELECT category_id, GROUP_CONCAT(id), GROUP_CONCAT(name)
-> FROM sh_goods GROUP BY category_id DESC;
+-------------+------------------+--------------------+
| category_id | GROUP_CONCAT(id) | GROUP_C