十、分组数据

GROUP BY.png

两个SELECT语句子句：GROUP BY子句和HAVING子句。

1、数据分组

从上一篇文章我们可以知道：使用SQL聚集函数可以汇总数据。这样，就能够对行进行计数、求和、求平均数，不检索所有数据就获得最大值和最小值。

思考：
如果要返回每个品牌提供的产品数目，该怎么办？
或者返回只提供一项产品的品牌的产品，该怎么办？
或者返回提供10个以上产品的供应商的产品，该怎么办？

这种需求就可以使用分组了。是用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

2、创建分组

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

• 举例：

SELECT brand， COUNT(*) AS  number_goods
FROM Goods
GROUP BY  brand;

输出:

brand	number_goods
ALibaba	25
MengNiu	30
WangWang	35
YiLi	20

• 分析：
  SELECT语句指定了两个列：brand包含商品 品牌的列，number_goods为计算字段（用COUNT(*)函数建立）。GROUP BY子句指示DBMS按 brand 排序并分组数据。这就会对每个brand而不是整个表计算number_goods一次。从输出中可以看到ALibaba有25个商品，MengNiu有30个...。因为使用了GROUP BY,就不必指定要计算和估值的每个组了。系统会自动完成。
  GROUP BY子句指示DBMS分组数据，然后对每个组而不是整个结果集进行聚集。

• 规定：

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

• tips: 有些SQL允许根据SELECT列表中的位置指定GROUP BY的列。例如GROUP BY 2,1标识按选择的第二个列分组，然后在按第一个列分组。但并非所有的SQL实现都支持，且它容易在编辑SQL语句时候出错。

3、过滤分组

WHERE没有分组的概念，它过滤的是行，不是分组；
HAVING过滤分组。

HAVING和WHERE语法是相同的。HAVING支持目前（文章四、五）介绍过的所有WHERE操作符。

例如：检索出所有订单不少于两笔订单的分组。

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

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

WHERE 和HAVING同时使用的场景举例：

①找出12月份具有两个以上订单的分组(或者顾客)：

SELECT cust_id, COUNT(*) AS orders
FROM  Orders
WHERE moth  = 12
GROUP BY cust_id
HAVING COUNT(*) > =2;

②具有两个以上产品且价格大于等于4的供应商：

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

• 这里说明了规定③：不能使用别名num_prods，只能使用检索列或有效的表达式：vend_id

说明：使用HAVING 和 WHERE
它们非常类似，如果不指定GROUP BY，则大多数DBMS会等同对待它们。不过我们要能区分这一点。使用HAVING 时应该结合GROUP BY子句，而WHERE子句用于标准的行级多虑。

4、分组和排序

GROUP BY 和 ORDER BY经常完成相同的工作，但它们非常不同，具体如下表：

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