MySQL必知必会——第十三章分组数据

2022-02-14 16:09:30

分组数据

本章将介绍如何分组数据，以便能汇总表内容的子集。这涉及两个新SELECT语句子句，分别是GROUP BY和HAVING子句。

数据分组

上章（MySQL必知必会——第十二章汇总数据）我们使用SQL聚集函数来汇总数据。这使我们不用检索所有数据就能对行进行计数、计算和平均数等。

目前为止的计算都在表的所有数据或匹配特定的WHERE子句的数据上进行的。
例如，返回供应商1003提供的产品数目：

mysql> SELECT COUNT(*) AS num_prods
    -> FROM products
    -> WHERE vend_id = 1003;
+-----------+
| num_prods |
+-----------+
|         7 |
+-----------+
1 row in set (0.01 sec)

但如果我们想返回每个供应商提供的产品数目，或返回只提供单项产品的供应商提供的产品，或返回提供10个以上产品的供应商怎么办？。

明显这简单的聚集函数很难办到，我们可以使用分组。分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

创建分组

分组是在SELECT语句的GROUP BY子句中建立的。

返回每个供应商提供的产品数目：

mysql> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM products
    -> GROUP BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
4 rows in set (0.00 sec)

此SELECT语句指定了两个列，vend_id包含产品供应商的ID，num_prods为计算字段。GROUP BY子句指示MySQL按vend_id排序并分组数据。即，COUNT(*)对每个vend_id计算一次。

GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

一些使用GROUP BY子句的重要规定：

GROUP BY子句可以包含任意数目的列。这能对分组进行嵌套，为数据分组提供更细致的控制。
如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总（不能从个别的列取回数据）。
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中年指定相同的表达式。不能使用别名。
除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中出现。
如果分组列中具有NULL值，则NULL将作为一个分组返回。
GROUP BY子句必须出现在WHERE子句后，ORDER BY子句前。

使用ROLLUP 使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别的值，例如

mysql> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM products
    -> GROUP BY vend_id WITH ROLLUP;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
|    NULL |        14 |
+---------+-----------+
5 rows in set (0.00 sec)

过滤分组

除了能用GROUP BY分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。

谈过滤，我们会想到WHERE子句（MySQL必知必会——第六章过滤数据）。但WHERE过滤指定的行，并非分组，所以我们需要另外的子句，HAVING子句。

HAVING非常类似于WHERE。目前所以WHERE子句都可以用HAVING代替，而唯一的区别是，WHERE过滤行，HAVING过滤分组。

HAVING支持所有WHERE操作符 我们所学过的有关WHERE的所有技术和选项都适用于HAVING。

过滤拥有两个及两个以上订单的分组：

mysql> SELECT cust_id, COUNT(*) AS orders
    -> FROM orders
    -> GROUP BY cust_id
    -> HAVING COUNT(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
1 row in set (0.01 sec)

HAVING和WHERE的差别 WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。WHERE排除的行不包括在分组中，这可能改变计算值，从而影响HAVING子句中过滤的分组。

我们可以同时使用WHERE和HAVING子句。

列出具有2个（含）以上、价格为10（含）以上的产品的供应商：

mysql> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM products
    -> WHERE prod_price >= 10
    -> GROUP BY vend_id
    -> HAVING COUNT(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
2 rows in set (0.00 sec)

此语句中，WHERE子句过滤所有prod_price至少为10的行，然后按vend_id进行分组数据，再通过HAVING子句过滤计数至少为2的分组。

无WHERE的对比：

mysql> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM products
    -> GROUP BY vend_id
    -> HAVING COUNT(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
4 rows in set (0.00 sec)

分组和排序

GROUP BY和ORDER BY经常完成相同的工作，但它们是非常不一样的。

ORDER BY与GROUP BY的差别：

ORDER BY	GROUP BY
排序产生的结果	分组行。但输出可能不是分组的顺序
任意列都可以使用（甚至非选择列）	只能使用选择列或表达式列，且必须使用选择表达式
不一定需要	与聚集函数一起使用列（或表达式），则有必要

我们经常发现GROUP BY分组的数据是以分组的顺序输出的，但并不总是这样。我们应该明确提供ORDER BY子句，来进行排序。

不要忘记ORDER BY 一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。

检索订单总价大于等于50的订单的订单号和订单总价：

mysql> SELECT order_num, SUM(quantity*item_price) AS ordertotal
    -> FROM orderitems
    -> GROUP BY order_num
    -> HAVING SUM(quantity*item_price) >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
+-----------+------------+
4 rows in set (0.00 sec)

我们在按订单总价排序输出：

mysql> SELECT order_num, SUM(quantity*item_price) AS ordertotal
    -> FROM orderitems
    -> GROUP BY order_num
    -> HAVING SUM(quantity*item_price) >= 50
    -> ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+
4 rows in set (0.00 sec)

SELECT子句顺序

回顾当前SELECT语句中子句的顺序，列表顺序：

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表中选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在分组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

码农公寓