本篇主要讲述如何使用 GROUP BY 和 HAVING 子句

数据分组

上一篇讲述了如何使用聚集函数汇总数据（如总数、平均值和最大最小值）

但是目前汇总的范围只能是整个表，或使用 WHERE 匹配一个指定的字段，例如下面返回由供应商 DLL01 提供的产品数目

1
2
3

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

但是，如果要分门别类地返回各个供应商的产品数目呢？

这就需要对数据进行分组，然后对每组分别计算

创建分组

分组是使用 GROUP BY 子句创建的，理解分组的最好方法是看一个例子

1
2
3

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id

由于使用了 GROUP BY ，DBMS 会自动依照 vend_id 来分组，然后对每一个组都执行一次聚集函数

下面是一些关于 GROUP BY 的重要规定

GROUP BY 可以包含任意数目的列，用来嵌套分组（用后面的字段进一步分割前面已经分好的组）
除聚集计算语句外，SELECT 中的每一列也必须出现在 GROUP BY 中
GROUP BY 中的每一列都必须是真实的列（可以不是 SELECT 中的）或有效的表达式，不能使用别名。如果在 SELECT 中使用表达式，则必须在 GROUP BY 中指定相同的表达式
大多数 DBMS 不允许在 GROUP BY 中使用可变长度数据类型的列（如文本或备注型字段）
如果包含了值为 NULL 的行，则这些行将作为一个分组返回
GROUP BY 子句必须出现 WHERE 之后，ORDER BY 之前

提示：GROUP BY ALL

如果使用了 ALL，查询结果将包括所有分组，即使是没有匹配上的内容

我感觉这东西并没有什么用，基本可以看作是禁用了 WHERE 子句，毕竟在最新的 Microsoft SQL Server 中，它已经被移除了

注意：通过相对位置指定列

有的 DBMS 允许根据 SELECT 列表中的位置指定 GROUP BY 的列。例如 GROUP BY 2, 1 表示按选择的第二个列分组，然后再按第一个列分组，不过这种方法很容易出错

众所周知，我们可以使用 WHERE 来过滤行，而类似地，我们可以用 HAVING 来过滤分组

HAVING 后面可以跟所有 WHERE 的操作符，这两个只是操作的对象不同而已

例如，我们先列出所有的顾客订单数（按顾客分组，然后汇集），再尝试使用 HAVING 来挑选出至少有两个订单的顾客（过滤分组）

1
2
3

SELECT cust_id ,COUNT(*) AS orders
FROM Orders
GROUP BY cust_id;

接下来尝试过滤分组，重新输入并加上下面这一行

1	HAVING COUNT(*) >= 2;

可以看到，已经成功过滤

如果同时使用 WHERE 和 HAVING ，其实是有执行的优先级的，具体过程为 WHERE 先进行行级过滤，然后分组，然后 HAVING 再过滤分组，这一点很重要，因为 WHERE 排除的行并不在分组中，这可能会改变计算值，从而影响 HAVING 子句

举一个同时使用 WHERE 和 HAVING 的例子：列出具有两个以上产品且价格大于等于 4 的供应商

SELECT vend_id , COUNT(*) AS num_prods
FROM Products
WHERE prod_price >=4 # 行级过滤
GROUP BY  vend_id    # 分组
HAVING COUNT(*) >=2; # 组级过滤

GROUP BY 并没有排序的功能，所以不要忘记在最后进行排序，下面是一个例子

检索三个或更多物品的订单号和订购物品的数目

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

再加上排序

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

现在回顾一下 SELECT 语句中各已经学过的子句以及它们的顺序