【学习06】MySQL分组查询与子查询

一、分组数据

这涉及两个新SELECT语句子句，分别是GROUP BY子句和HAVING子句。
表数据如下：

1.1 创建分组

输入：

SELECT vend_id,count(*) AS num_prods
FROM vendors
ORDER BY vend_id;

输出：

分析： 上面的SELECT语句指定了两个列，vend_id包含产品供应商的ID，num_prods为计算字段（用COUNT(*)函数建立）。GROUP BY子句指
示MySQL按vend_id排序并分组数据。GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。
注意：

• GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
• 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上
  进行汇总。换句话说，在建立分组时，指定的所有列都一起计算
  （所以不能从个别的列取回数据）
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式
  （但不能是聚集函数）。如果在SELECT中使用表达式，则必须在
  GROUP BY子句中指定相同的表达式。不能使用别名。
• 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子
  句中给出。
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列
  中有多行NULL值，它们将分为一组。
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

1.2 过滤分组

  除了能用GROUP BY分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，可能想要列出至少有两个订单的所有顾客。为得出这种数据，必须基于完整的分组而不是个别的行进行过滤。
  HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。
  WHERE没有分组的概念

输入：

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

输出：

分析：
这条SELECT语句的前3行类似于上面的语句。最后一行增加了
HAVING子句，它过滤 COUNT(*) > 2（两个产品数量）的那些
分组。

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

假如想进一步过滤上面的语句，使它返回过去12个月内具有两个以上订单的顾客。为达到这一点，可增加一条WHERE子句，过滤出过去12个月内下过的订单。然后再增加HAVING子句过滤出具有两个以上订单的分组.

为更好地理解，请看下面的例子，它列出具有2个（含）以上、价格
为10（含）以上的产品的供应商：

输入：

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

输出：

分析：
这条语句中，第一行是使用了聚集函数的基本SELECT，它与前面的例子很相像。WHERE子句过滤所有prod_price至少为10的行。然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。如果没有WHERE子句，将会多检索出两行（供应商1002，销售的所有产品价格都在10以下；供应商1001，销售3个产品，但只有一个产品的价格大于等于10）

1.3 分组和排序

差别：

我们经常发现用GROUP BY分组的数据确实是以分组顺序输出的。但情况并不总是这样，它并不是SQL规范所要求的。此外，用户也可能会要求以不同于分组的顺序排序。仅因为你以某种方式分组数据（获得特定的分组聚集值），并不表示你需要以相同的方式排序输出。应该提供明确的ORDER BY子句，即使其效果等同于GROUP BY子句也是如此。

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

请看例子：它检索总计订单价格大于等于50的订单的订单号和总计订单价格

输入：

SELECT order_num, SUM(quantity * item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price) >= 50;

输出：

为按总计订单价格排序输出，需要添加ORDER BY子句，如下所示：
输入：

SELECT order_num, SUM(quantity * item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price) >= 50
ORDER BY ordertotal;

输出：

分析：
在这个例子中，GROUP BY子句用来按订单号（order_num列）分组数据，以便SUM(*)函数能够返回总计订单价格。HAVING子句过滤数据，使得只返回总计订单价格大于等于50的订单。最后，用ORDER BY子句排序输出

二、子查询

SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

2.1 利用子查询进行过滤

订单存储在两个表中。对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。

• orders表
  
• orderitems表
  
• customers

需求： 假如需要列出订购物品TNT2的所有客户，应该怎样检索？下
面列出具体的步骤。
 (1) 检索包含物品TNT2的所有订单的编号。
 (2) 检索具有前一步骤列出的订单编号的所有客户的ID。
 (3) 检索前一步骤返回的所有客户ID的客户信息。

输入：

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN(SELECT cust_id
		 FROM orders
		 WHERE order_num IN(SELECT order_num
				    FROM orderitems
				    WHERE prod_id = 'TNT2'));

输出：

分析： 为了执行上述SELECT语句，MySQL实际上必须执行3条SELECT语句。最里边的子查询返回订单号列表，此列表用于其外面的
子查询的WHERE子句。外面的子查询返回客户ID列表，此客户ID列表用于最外层查询的WHERE子句。最外层查询确实返回所需的数据。

在WHERE子句中使用子查询能够编写出功能很强并且很灵活的
SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于
性能的限制，不能嵌套太多的子查询。

列必须匹配 在WHERE子句中使用子查询（如这里所示），应
该保证SELECT语句具有与WHERE子句中相同数目的列。通常，
子查询将返回单个列并且与单个列匹配，但如果需要也可以
使用多个列

2.2 作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示customers
表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
为了执行这个操作，遵循下面的步骤。
 (1) 从customers表中检索客户列表。
 (2) 对于检索出的每个客户，统计其在orders表中的订单数目

正如前两章所述，可使用SELECT COUNT(*)对表中的行进行计数，并
且通过提供一条WHERE子句来过滤某个特定的客户ID，可仅对该客户的订单进行计数。

为了对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查
询。请看下面的代码：

输入：

SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders
			     WHERE orders.`cust_id` = customers.`cust_id`) AS orders
FROM customers
ORDER BY cust_name;

输出：

分析： 这 条 SELECT 语句对 customers 表中每个客户返回 3 列 ：
cust_name、cust_state和orders。orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索出了5个客户。