MySQL 学习笔记 —— 10、分组数据

数据分组

使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算

例如，下面的代码

SELECT COUNT(*) AS num_prods
FROM Products
WHERE vend_id = '1001';

可以返回供应商 1001 提供的产品数目

如果要返回每个供应商提供的产品数目，
或者返回只提供一项产品的供应商的产品，
或者返回提供 10 个以上产品的供应商的产品，
这时就需要用的分组了

分组：
分组，允许把数据分为多个逻辑组，以便能够对每个组进行聚集计算

---

创建分组

分组是使用 SELECT 语句的 GROUP BY 子句建立的

看下面这个例子：

mysql> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM Products
    -> GROUP BY vend_id;

上面的 SELECT 语句中指定了两个列：
vend_id 包含产品供应商的 ID，
num_prods 为计算字段（用COUNT(*) 函数建立）

GROUP BY 子句指示 MySQL 按 vend_id 排序并分组数据；
这就会对每个 vend_id 计算一次，而不是整个表计算 num_prods 一次

从输出中可以看出，供应商1001、1002、1003、1005分别有产品3、2、7、2个

因为使用了 GROUP BY， 就不必指定要计算和估值的每个组了，系统会自动完成

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

关于 GROUP BY 子句的一些规定：

1. GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组
2. 如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总；即，建立分组时，指定的所有列都一起计算，所以不能从个别的列中取回数据
3. GROUP BY 子句中列出的每一列都必须时检索列或者有效的表达式（但是不能是聚集函数）；如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式，不能使用别名
4. 大多数 SQL 实现不允许 GROUP BY 列带有长度可变的数据类型（如文本或者备注型字段）
5. 除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY子句中给出
6. 如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回；如果列中有多行 NULL值，它们将分为一组
7. GROUP BY 子句必须出现在 WHERE 子句后，ORDER BY 子句之前

使用 ROLLUP :
使用 WITH ROLLUP 关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组） 的值

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;

---

过滤分组

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

例如，可能想要列出至少有两个订单的所有顾客。为得出这种数据，必须基于完整的分组而不是个别的行进行过滤

注意，这里说的过滤对象是分组，而不是行，所以不可以使用 WHERE，需要使用 HAVING 子句

HAVING 子句非常类似于 WHERE，之前所有的 WHERE 子句都可以使用 HAVING 代替
区别是：WHERE 过滤行，而 HAVING 过滤分组

mysql> SELECT cust_id, COUNT(*) AS orders
    -> FROM Orders
    -> GROUP BY cust_id
    -> HAVING COUNT(*) >= 2;

如上面的 SQL 语句，最后一行的 HAVING 子句，可以过滤 COUNT(*) >= 2 （两个以上订单）的哪些分组

因为过滤是基于分组聚集值，而不是特定行的值，所以 WHERE 子句在这里不起作用

WHERE 与 HAVING：

1. WHERE 是在数据分组前进行过滤，而 HAVING 是在数据分组后进行过滤
2. WHERE 排除的行不包括在分组中，这可能会改变计算值，从而影响 HAVING 子句中基于这些值过滤掉的分组
3. 在同一条语句中，可以同时使用 WHERE 和 HAVING 子句

如下面的一个例子，列出了具有两个以上产品，且其价格大于等于4的供应商，同时使用了 WHERE 和 HAVING　子句

mysql> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM Products
    -> WHERE prod_price >= 4
    -> GROUP BY vend_id
    -> HAVING COUNT(*) >= 2;

下面两个图分别时存在 WHERE 子句时 和 不存在 WHERE 子句时的输出

---

分组和排序

GROUP BY 和 ORDER BY 经常完成相同的工作，但是他们非常不同，下面是对它们不同的汇总

1. GROUP BY 分组的数据确实是以分组顺序输出的，但不总是这样，这不是 SQL 规范要求的
2. 即使特定的 DBMS 总是按给出的 GROUP BY 子句排序数据，用户也可能会要求以不同的顺序排序；因为以某种方式分组数据，并不表示需要以相同的方式排序输出
3. 应该提供明确的 ORDER BY 子句，即使其效果等同于 GROUP BY 子句

如下两个语句以及对应的输出，可以看到不加 ORDER BY 与加上这个语句后的输出差别
如果想要按照订购物品的数目排序输出，则需要添加 ORDER BY 语句

mysql> SELECT order_num, COUNT(*) AS items
    -> FROM OrderItems
    -> GROUP BY order_num
    -> HAVING COUNT(*) >= 1;

mysql> SELECT order_num, COUNT(*) AS items
    -> FROM OrderItems
    -> GROUP BY order_num
    -> HAVING COUNT(*) >= 1
    -> ORDER BY items, order_num;

---

SELECT 子句顺序

以在 SELECT 语句中使用时必须遵循的次序，列出之前所说的子句

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