MySQL——自学笔记Day10

莫道桑榆晚，为霞尚满天。

---

分组数据

1.数据分组

1.1 代码演示：返回供应商DLL01提供的产品数目

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

运行结果：

1.2 说明

（1）从上篇文章可以知道使用SQL聚合函数可以汇总数据，本小节在聚合函数的基础上延伸，当遇到需要返回每个供应商产品数目的问题，或是返回只提供一项产品的供应商的产品，再或者返回提供10个以上产品的供应商的产品的问题

2. 创建分组

2.1 代码演示

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

运行结果

 2.2 说明

在使用GROUP BY的时候，需要注意下面一些规定

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

3.过滤分组

3.1代码演示：返回订单中出现两次及以上的客户编号

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

运行结果

3.2 说明

（1）这条SELECT语句的前三行类似于上面的语句。最后一行增加了HAVING子句,它过COUNT(*)>=2(两个以上订单)的那些分组。可以看到,WHERE子句在这里不起作用,因为过滤是基于分组聚集值,而不是特定行的值。

（2）说明:HAVING和WHERE的差别这里有另一种理解方法,WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。这是一个重要的区别,WHERE排除的行不包括在分组中。这可能会改变计算值,从而影响HAVING子句中基于这些值过滤掉的分组。

（3）不过也会有一条语句中同时使用WHERE和HAVING的情况。假如想进一步过滤上面的语句,使它返回过去12个月内具有两个以上订单的顾客。为此,可增加一条WHERE子句,过滤出过去12个月内下过的订单,然后再增加HAVING子句过滤出具有两个以上订单的分组。为了更好地理解,来看下面的例子,它列出具有两个以上产品且其价格大于等于4的供应商:

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

运行结果

 而如果上面的代码没有WHERE子句，就会多检索出一行，因为供应商上中，DLL01销售的4个产品，价格都在4以下

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

运行结果

 

 4.分组和排序

GROUP BY和ORDER BY都有分组的功能，但是也有一些差别

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

4.1代码演示：检索包含三个或更多物品的订单号和购物品的数目

select order_num, count(*) as items
from OrderItems
group by order_num
having count(*) >= 3;

运行结果

4.2 说明

以上代码只是用GROUP BY筛选出了结果，如果想按照items来进行排序，则还需要加上ORDER BY代码，如下所示：

select order_num, count(*) as items
from OrderItems
group by order_num
having count(*) >= 3
order by items,order_num;

运行结果

5.回顾

在这里简单回顾一下SELECT语句中子句的顺序。如下表所示，在SELECT语句中使用时必须遵循的次序，列出迄今为止所学的子句。

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

---

总结

       本章节主要学习如何使用GROUP BY子句对多组数据进行汇总计算，返回每个组的结果，还有使用HAVING子句过滤特定的组，并且简单总结了一下ORDER BY和GROUP BY之间以及WHERE和HAVING之间的差异。