《SQL必知必会》第十课 分组数据 使用SQL的SELECT语句子句：GROUP BY子句和HAVING子句汇总表内容的子集

第十课 分组数据

#使用SQL的SELECT语句子句：GROUP BY子句和HAVING子句汇总表内容的子集
#使用GROUP BY子句对多组数据进行汇总计算，返回每个组的结果
#使用HAVING子句过滤特定的组
#ORDER BY与GROUP BY子句之间的差异
#WHERE与HAVING子句之间的差异

之前所有计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的，如下例：
Products表：

【1】SELECT COUNT(*) AS num_prods FROM products WHERE vend_id = 'DLL01';
#返回供应商DLL01提供的产品数目

若要返回每个供应商提供的产品数目或返回只提供一项产品的供应商产品或返回提供10个以上产品的供应商的产品数目，此时需要使用 分组

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

创建分组：

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

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

【2】SELECT vend_id,COUNT(*) as num_prods FROM products GROUP BY vend_id;
#SELECT语句指定两个列：
vend_id包含产品供应商的ID；
num_prods为用COUNT（*）函数建立的计算字段

#GROUP BY子句指示DBMS按vend_id排序并分组数据，即对每个vend_id而不是整个表计算num——prods

#结果显示：供应商BRS01有3个产品，供应商DLL01有4个产品

注意1：
使用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子句之前。

注意2：
Microsoft SQL Server等有些SQL实现在GROUP BY中支持可选的ALL子句。这个子句可用来返回所有分组，即使是没有匹配行的分组也返回（在此情况下，聚集将返回NULL）。

注意3：
有的SQL实现允许根据SELECT列表中的位置指定GROUP BY的列。例如，GROUP BY 2, 1可表示按选择的第二个列分组，然后再按第一个列分组。

过滤分组：

#SQL提供HAVING子句，允许使用过滤分组，规定包括哪些分组，排除哪些分组
#基于完整的分组而不是个别的行进行过滤
#WHERE过滤指定的是行而不是分组
#WHERE过滤行，HAVING过滤分组

注意4：
HAVING支持所有WHERE操作符

Orders表：

【3】SELECT cust_id FROM orders GROUP BY cust_id HAVING COUNT(*) >=2;
#HAVING子句过滤COUNT(*) >=2（两个以上订单的分组）
#WHERE子句在这里不起作用，因为过滤是基于分组聚集值

注意5：
HAVING和WHERE的差别：
#WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤

#有时需要在同一条语句中同时使用WHERE和HAVING子句，如进一步过滤上述语句，返回过去12个月内具有两个以上订单的顾客
#增加一条WHERE子句，过滤过去12个月内下过的订单
#再增加HAVING子句，过滤出具有两个以上订单的分组

【4】SELECT cust_id FROM orders WHERE YEAR(order_date)=2012 GROUP BY cust_id HAVING COUNT(*) >=2;
#返回在2012年具有两个以上订单的顾客

【5】SELECT vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*)>=2;
#列出具有两个以上产品且其价格大于4的供应商
#WHERE子句过滤出prod_price >= 4的行
#按vend_id分组数据
#HAVING子句过滤计数为2或2以上的分组
#若没有WHERE子句则多检索一行（供应商DLL01，销售4个产品，价格在4以下），如下语句所示

【6】SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2;

注意6：
使用HAVING和WHERE：
#HAVING与WHERE非常类似，如果不指定GROUP BY，则大多数DBMS会同等对待它们。
#使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。

分组和排序：

#GROUP BY子句和ORDER BY子句经常完成相同的工作，但它们非常不同

GROUP BY子句和ORDER BY子句的区别：

注意7：
（1）第一项差别极为重要
（2）GROUP BY语句分组的数据确实是以分组顺序输出，但并不总是这样，不是SQL规范所要求的
（3）即使特定的DBMS总按给出的GROUP BY语句排序数据，用户也会要求以不同的顺序排序
（4）以某种方式分组数据，并不表示以相同的方式排序输出
（5）应提供明确的ORDER BY子句，即使其效果等同于GROUP BY子句

注意8：
#不要忘记ORDER BY
[1]一般在使用GROUP BY子句时，应该也给出ORDER BY子句。
[2]这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

【7】SELECT order_num,COUNT(*) AS items FROM orderitems GROUP BY order_num HAVING COUNT(*) >= 3;

#检索包含3个或更多物品的订单号和订单物品数目
#若要按订购物品数目排序输出，需添加ORDER BY子句

【8】SELECT order_num,COUNT(*) AS items FROM orderitems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items,order_num;

#使用GROUP BY子句按订单号（order_num列）分组数据，以便COUNT(*)函数能够返回每个订单中的物品数目
#HAVING子句过滤数据，使得只返回包含三个或更多物品的订单
#ORDER BY子句排序输出

注意9：
Microsoft Access的不兼容性：
[1]Microsoft Access不允许按别名排序，因此这个例子在Access中将失败。
[2]解决方法是用实际的计算或字段位置替换items（在ORDER BY子句中），即ORDER BY COUNT(＊), order_num或ORDER BY 2, order_num。

SELECT子句顺序：

下回顾 SELECT语句中子句的顺序：
在SELECT语句中使用时必须遵循的次序，列出迄今为止所学过的子句：

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

需要完整代码或书籍PDF版的小伙伴可关注微信公众号：菜田里守望者

打开微信扫一扫关注吧，你们的支持就是我的动力

参考文献：
【1】《SQL必知必会》第四版 人民邮电出版社 [美] Ben Forta 著 钟鸣 刘晓霞 译