首页 / MYSQL / 【MySQL学习】分组数据，使用子查询

【MySQL学习】分组数据，使用子查询

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了【MySQL学习】分组数据，使用子查询，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9008字，纯文字阅读大概需要13分钟。

内容图文

【MySQL学习】分组数据，使用子查询

分组数据

介绍两个新的SELECT 语句字句，分别是GROUP BY 和 HAVING。

数据分组

之前我们介绍过使用聚集函数汇总数据，比如对1003供应商进行汇总

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

说明这个供应商提供7个产品

+-----------+
| num_prods |
+-----------+
|         7 |
+-----------+

当我们需要返回每个供应商提供的产品数目，或者返回只提供单项产品的供应商所提供的产品，或者返回提供十个以上产品的供应商，我们就需要进行数据分组，以便能对每个组进行聚集计算。

创建分组

分组使用 SELECT 语句中的 GROUP BY 字句建立的
举个例子

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

这个语句制定了数据库按照供应商id排序并进行数据分组，导致每次计算产品数量不是对着所有产品，而是对某个供应商的所有产品计数

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

这个语句有这些注意事项

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

过滤分组

我们已经看到了 WHERE 子句的作用。但是，在这个例子中 WHERE 不能完成任务，因为 WHERE 过滤指定的是行而不是分组。事实上， WHERE 没有分组的概念。想要过滤分组，则使用 HAVING 子句, HAVING 非常类似于 WHERE 。事实上，目前为止所学过的所有类型的 WHERE 子句都可以用 HAVING 来替代。唯一的差别是
WHERE 过滤行，而 HAVING 过滤分组。

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

这样输出的就是含有两个以上订单的组。


+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+

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

为了理解这两种子句的不同，我们再看一个例子，如何列出具有两个以上、价格为10的产品供应商

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

这里的 WHERE 只调用价格大于等于10的商品，而 HAVING 则筛选了有两个以上（价格大于等于10）的商品的供应商。

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+

同时我们应该注意到，WHERE 的触发是在前面的，我们去掉 WHERE 子句来看看结果：

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

输出是：

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

分组和排序

分组和排序分别采用 GROUP BY 和 ORDER BY，两者常常联合起来完成工作，因为ORDER BY 是唯一能保证数据正确排序的方法。

我们举一个简单的例子，下面的例子检索总计订单价格大于等于50的订单的订单号和总计订单价格：

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

+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
+-----------+------------+

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

SELECT字句顺序

这里回顾一下 SELECT 语句中的子句的顺序

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

使用子查询

MySQL 4.1引入了对子查询的支持，所以要想使用下面描述的SQL，必须使用MySQL 4.1或更高级的版本。SELECT语句 是SQL的查询。迄今为止我写的所有 SELECT 语句都是简单查询，即从单个数据库表中检索数据的单条语句。

SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。为什么要这样做呢？理解这个概念的最好方法是考察几个例子。

使用子查询进行过滤

测试数据中中使用的数据库表都是关系表（关于每个表及关系的描述，请参阅《MySQL必知必会》附录B）。订单存储在两个表中。对于包含订单号、客户ID、订单日期的每个订单， orders 表存储一行。各订单的物品存储在相关的orderitems 表中。 orders 表不存储客户信息。它只存储客户的ID。实际的客户信息存储在 customers 表中。

现在，假如需要列出订购物品 TNT2 的所有客户，应该怎样检索？下面列出具体的步骤。

  1. 检索包含物品 TNT2 的所有订单的编号。
  2. 检索具有前一步骤列出的订单编号的所有客户的ID。
  3. 检索前一步骤返回的所有客户ID的客户信息。

上述每个步骤都可以单独作为一个查询来执行。可以把一条 SELECT语句返回的结果用于另一条 SELECT 语句的 WHERE 子句。

也可以使用子查询来把3个查询组合成一条语句。

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
					FROM orderitems
					WHERE prod_id ='TNT2');

值得注意的是，子查询总是自内向外处理。

+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+

现在我们得到了所有订购物品 TNT2 的客户的 ID （10001,10004），下一步是检索这些客户的信息：

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'));

+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

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

虽然子查询一般与 IN 操作符结合使用，但也可以用于测试等于（ = ）、不等于（ <> ）等。

作为计算字段使用

使用子查询的另一方法是创建计算字段。假如需要显示 customers表中每个客户的订单总数。订单与相应的客户ID存储在 orders 表中。

为了执行这个操作，遵循下面的步骤。

  1. 从 customers 表中检索客户列表。
  2. 对于检索出的每个客户，统计其在 orders 表中的订单数目。

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

例如，下面的代码对客户 10001 的订单进行计数：

SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001

为了对每个客户执行 COUNT() 计算，应该将 COUNT() 作为一个子查询。

请看下面的代码：

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


+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      2 |
| E Fudd         | IL         |      1 |
| Mouse House    | OH         |      0 |
| Wascals        | IN         |      1 |
| Yosemite Place | AZ         |      1 |
+----------------+------------+--------+

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

子查询中的 WHERE 子句与前面使用的 WHERE 子句稍有不同，因为它使用了完全限定列名（在第4章中首次提到）。下面的语句告诉SQL比较orders 表中的 cust_id 与当前正从 customers 表中检索的 cust_id ：


WHERE orders.cust_id = customers.cust_id

这种类型的子查询称为相关子查询 (correlated subquery) 。任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔)。

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

表中有两个相同的 cust_id 列，一个在 customers 中，另一个在orders 中，需要比较这两个列以正确地把订单与它们相应的顾客匹配。如果不完全限定列名，MySQL将假定你是对 orders 表中的 cust_id 进行自身比较。而 SELECT COUNT(*) FROM orders WHERE cust_id = cust_id;总是返回 orders 表中的订单总数（因为MySQL查看每个订单的 cust_id是否与本身匹配，当然，它们总是匹配的）。

所以输出是：

`+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      5 |
| E Fudd         | IL         |      5 |
| Mouse House    | OH         |      5 |
| Wascals        | IN         |      5 |
| Yosemite Place | AZ         |      5 |
+----------------+------------+--------+

值得注意的是，虽然这里给出的样例代码运行良好，但它并不是解决这种数据检索的最有效的方法。在后面的章节中我们还要遇到这个例子

内容总结

以上是互联网集市为您收集整理的【MySQL学习】分组数据，使用子查询全部内容，希望文章能够帮你解决【MySQL学习】分组数据，使用子查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/862528.html

来源：【匿名】