Mysql中的数据分组和子查询

MySQL中的数据分组和子查询

一、数据分组

1、创建分组

分组是在SELECT语句的GROUP BY子句中建立的。

示例：SELECT book_id,COUNT(*) AS book_num FROM book GROUP BY book_id

上述语句首先根据book_id进行分组，把所有book_id相同的条目放在一起，然后使用COUNT(*)统计每一组的数目。最有进行显示。

因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。 GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

注意：

1）GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

2）GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

3）如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

4）GROUP BY子句必须出现在WHERE子句之后， ORDER BY子句之前。

2、过滤分组

除了能用GROUP BY分组数据外， MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。可以通过 HAVING实现。HAVING非常类似于WHERE。事实上，之前所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。HAVING支持所有WHERE操作符。

示例：SELECT book_id,COUNT(*) AS book_num FROM book GROUP BY book_id HAVING COUNT(*)>10

上述语句的最后增加了HAVING子句，它过滤COUNT(*) >=2（两个以上的订单）的那些分组。这里WHERE子句不起作用，因为这里需要的过滤是基于分组聚集值而不是特定行值的。

HAVING和WHERE的差别：

WHERE在数据分组前进行过滤， HAVING在数据分组后进行过滤。 WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

3、分组和排序的区别

虽然GROUP BY和ORDER BY经常完成相同的工作，但它们是有很大区别的。

order by 是按表中某字段排列表中数据。group by 是按某些字段分类。

例如：

按年龄排序表中的记录

select * from users order by age

按年龄分类表中数据（就是求各个年龄的人数）

select age,count(*) as number1 from users group by age

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

不要仅依赖GROUP BY排序数据。

4、SELECT子句的书写顺序和执行顺序

Select子句书写顺序：

Select … from tbl1, tbl2 where … group by … having ……order by (asc | desc )

当SELECT语句被DBMS执行时，其子句会按照固定的先后顺序执行：

（1）FROM 子句。

（2）WHERE 子句。

（3）GROUP BY 子句。

（4）HAVING 子句。

（5）SELECT 子句。

（6）ORDER BY 子句。

基本的工作原理：FROM子句先被执行，通过FROM子句获得一个虚拟表，然后通过WHERE子句从虚拟表中获取满足条件的记录，生成新的虚拟表。将新虚拟表中的记录通过GROUP BY子句分组后得到更新的虚拟表，而后HAVING子句在最新的虚拟表中筛选出满足条件的记录组成另外一个虚拟表中，SELECT子句会将指定的列提取出来组成更新的虚拟表，最后ORDER BY子句对其进行排序得出最终的虚拟表。通常这个最终的虚拟表被称为查询结果集。

二、子查询

1、利用子查询进行过滤

子查询（ subquery） ，即嵌套在其他查询中的查询。

现在假设一个订单存储的情境。orders表存储一行包含订单号、客户ID、订单日期。各订单的物品存储在相关的orderitems表中。 orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。

三个表的数据如下：

orders

orderitems

customers

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

(1) 检索包含物品TNT2的所有订单的编号。

(2) 检索具有前一步骤列出的订单编号的所有客户的ID。

(3) 检索前一步骤返回的所有客户ID的客户信息。

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

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子句。最外层查询确实返回所需的数据。

2、作为计算字段使用子查询

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

(1) 从customers表中检索客户列表。

(2) 对于检索出的每个客户，统计其在orders表中的订单数目。

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是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名。这种类型的子查询称为相关子查询。任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。例如orders和customers表中都有cust_id这个字段，这样就存在歧义。