sql学习分享---分组数据
- 1.1 数据分组
- 1.2 创建分组
- 1.3 过滤分组
- 1.4 分组和排序
- 1.5 select子句顺序
- 小结：
今天整理分享如何分组数据，以便汇总表内容的子集。这涉及两个select语句子句：group by子句和having子句。（表结构说明在文章末尾！！！）
1.1 数据分组
通常我们使用sql聚集函数可以汇总数据。这样，我们就能够对行进行计数，运算与平均数的计算，不检索所有数据就可以获得最大值和最小值；但是这些计算都是在表的所有数据或匹配特别的where子句的数据上进行的。比如以下的案例（附表结构）：
- Products表包含产品目录，每行一个产品。每个产品都有唯一的ID（prod_id列），并借助vend_id(供应商的唯一id)与供应商相关联。
输入：
select count(*) as num_prods from products where vend_id='DLL01';输出：
那如果要返回每个供应商提供的产品数目，该怎么办？或者返回值提供一项产品的供应商的产品，或者返回提供10个以上产品的供应商的产品又该如何？
这就是这次分享的分组处理大显身手的时候了。使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。
1.2 创建分组
分组是使用select语句的group by子句建立的。理解分组的最好办法是看一个例子：
输入：
select p.vend_id,count(*) as num_prods from products p group by p.vend_id输出：
分析：
上面的select语句指定了两个列：vend_id包含产品的供应商的id，num_prods为计算字段（用count（*）函数建立）。group by子句只是DBMS按vend_id排序并分组数据。这就会对每个vend_id而不是整个表计算num_prods一次。从输出可以看到，供应商DLL01有两个产品,供应商DLL02有两个产品。
因为使用了group by，就不必指定要计算和估值的每个组了。系统会自动完成。group by 子句指示DBMS分组数据，然后对每个组而不是整个结果进行聚集。
在使用group by子句前，需要知道一些重要的规定：
- group by子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致的进行数据分组。
- 如果在group by子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换话而言，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）
- group by子句列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在select中使用表达式，则必须在group by子句中指定相同的表达式。不能使用别名。
- 大多数sql实现不允许group by列带有长度可变的数据类型（如：文本或备注型字段）
- 除了聚集计算语句以外，select语句中的每一列都必须在group by子句中给出。
- 如果分组列中包含具有null值的行，则null将作为一个分组返回。如果列中有多行null值，它们将分为以一组。
- group by 子句必须出现在where子句后，order by 子句之前。
1.3 过滤分组
除了可以用group by分组数据外，sql 还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，你可能想要列出至少有两个订单的所有客户。为此，必须基于完整的分组而不是个别出现的行进行过滤。
在不使用where的条件下，sql为此提供了另一个子句，就是having子句。having非常类似于where。事实上，目前为止所学过的所有类型的where子句都可以用having来替代。唯一的差别是，where过滤行，而having是过滤分组。
那么该怎么过滤分组呢？请看下例：
输入：
select cust_id,count(*) as orders from orders group by cust_id having count(*)>=2输出：
分析：
这条select语句的前三行类似于上面的语句。最后一行增加了having子句，他过滤count(*)>=2（两个以上订单）的那些分组。可以看到where子句在这里不起到作用，因为过滤是基于分组聚集值，而不是特定行的值。那么，有没有在一条语句中同时使用where和having子句的需要呢？事实上，确实有。假如想进一步过滤上面的语句，使它返回过去12个月内具有两个以上订单的顾客。为此可以增加一条where子句，过滤出过去12个月内下过的订单，然后再增加having子句过滤出具有两个以上订单的分组。
为了更好的理解，可以参考下面的例子：列出具有两个以上产品且其价格大于等于4的供应商：
输入：
select vend_id,count(*)as nums_prods from products where prod_price>=4 group by vend_id having count(*)>=2输出：
这条语句中，首先是使用了聚集函数的基本select语句，然后用where子句过滤所有prod_price至少为4的行，然后按vend_id分组数据，having子句过滤计数为2或2以上的分组。如果没有where子句，结果就会有所改变，prod_price小于4的也会被统计在内。结果如下：
使用having和where的说明：
having和where很相似，如果不指定group by，则大多数DBMS会同等对待它们。不过，我们自己要能区分这一点。使用having时应该结合group by子句，而where子句用于标准的行级过滤。
1.4 分组和排序
group by和order by经常完成相同的工作，但是他们是不同的，区分两者也是很关键的。
order by | group by |
对产生的输出结果排序 | 对统计后的行进行分组，但是输出可能不是按照分组的顺序 |
任意列都可以使用（甚至非选择的列也可以使用） | 只可能使用选择列或表达式列，而且必须使用每个选择列表达式 |
不一定需要使用 | 如果与聚集函数一起使用列（或表达式），则必须使用 |
其中表格中第一行的区别极为重要，我们经常在使用时可以发现，用group by分组的数据确实是以分组顺序输出的。但并不是每次都这样，这不是sql规范所要求的。除此之外，即使特定的DBMS总是按给出的group by 子句排序数据，用户也可能会要求以不同的顺序排序。就因为以某种方式分组数据（获得特定的分组聚集值），并不表示你需要以相同的方式排序输出。所以应该提供明确的order by 子句，即使其效果等同于group by 子句。（一般在使用group by的时候，应该也给出order by子句。这是保证数据正确排序的唯一方法。千万不要依赖group by排序数据）
为了说明两者的使用方法，简单地举一个例子：
需求：主要是想检索包含一个或更多的物品的订单号和订购物品的数量。
输入：
select order_num,count(*) as items from orderitems group by order_num having count(*)>=1输出：
如果要按照订购物品的数目排序输出，需要添加order by子句，如下所示：
select order_num,count(*) as items from orderitems group by order_num having count(*)>=1 order by items,order_num输出：
在上述的例子中，使用group by子句按照订单号（order_num列）分组数据，以便count（*）函数能够返回每个订单中的物品数目。having子句过滤数据，使得只返回包含一个或更多物品的订单。最后用order by 子句排序输出。
1.5 select子句顺序
子句 | 说明 | 是否必须使用 |
select | 要返回的列或表达式 | 是 |
from | 从中检索数据的表 | 仅在从表选择数据时使用 |
where | 行级过滤 | 否 |
group by | 分组说明 | 仅在按组计算聚集时使用 |
having | 组级过滤 | 否 |
order by | 输出排序顺序 | 否 |
小结：
今天主要学习了如何使用group by子句对多组数据进行汇总计算，返回每个组的结果，分享了如何使用having子句过滤特定的组，还知道了order by和group by之间以及where 和having之间的差异。
