5.3 GROUP BY分组数据

---

---

1.概述

上一节我们介绍了聚合函数，可以用来汇总数据。这使我们能够对行进行计数，计算和取平均数，获得最大值最小值而不用查询全部的数据，如：

输入：

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

输出：

我们获取了供应商1003提供的全部产品数目。但如果我们想查询每个供应商提供的产品数目该怎么办？或者查询只提供单项产品的供应商所提供的产品，或查询提供10个以上产品的供应商怎么办？

这时候我们就可以使用分组查询了，分组查询允许将数据分为多个逻辑组，以便能够对每个数据组进行聚合计算。

2.创建分组

分组是在SELECT语句中的GROP BY子句中创建的。我们通过下面的例子来理解分组：

输入：

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

输出：

上面的SQL语句查询了俩个列，vend_id返回产品供应商的ID，num_prods为计算字段。GROUP BY子句指示MySql以vend_id排序并分组数据，这导致对每个vend_id而不是整个表计算num_prods一次。从输出结果可以看出，供应商1001有3个产品，供应商1002有2个产品，供应商1003有7个产品，供应商1005有2个产品。

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

3.使用GROUP BY分组数据时的注意事项

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

4.过滤分组

除了使用GROUP BY分组数据外，MySql还允许过滤分组，规定包括哪些分组，排除哪些分组。我们使用WHERE关键字过滤行，使用HAVING关键字过滤组，HAVING关键字支持所有的WHERE操作符，所有适用于WHERE关键字的命令，都可以使用HAVING关键字。我们通过下面的例子来了解如果过滤组：

输入：

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

输出：

上面的SQL语句最后一行增加了HAVING关键字，它过滤了俩个以上订单的分组。假如我们想进一步过滤上面的语句，返回2005年具有一个订单的顾客，可以同时使用WHERE关键字进行过滤：

输入：

SELECT cust_id, COUNT(*) AS orders
FROM orders
WHERE YEAR(order_date) = 2005
GROUP BY cust_id
HAVING COUNT(*) = 1;

输出：

5.分组和排序

虽然GROUP BY和ORDER BY经常完成同样的工作，但他们是有很大区别的。GROUP BY分组的数据并不总是按照分组的顺序输出的，在分组数据时，应该也给出ORDER BY子句，这是保证正确排序的唯一方法，而不是依赖GROUP BY排序数据。

例如，我们要查询订单价格大于等于50的订单的订单号和总计订单价格：

输入：

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

输出：

如果我们需要按照订单价格排序输出，需要添加ORDER BY 子句：

输入：

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

输出：

6.LIMIT关键字限制返回结果

6.1 用于强制返回指定的记录行数

在查询中，经常要返回前几条或者中间某几行数据时，用到limit
语法如下：

select * from table_name limit [offset，] rows

参数说明：
offset：指定第一个返回记录行的偏移量（即从哪一行开始返回），注意：初始行的偏移量为0。
rows：返回具体行数。

总结：如果limit后面是一个参数，就是检索前多少行。如果limit后面是2个参数，就是从offset+1行开始，检索rows行记录。

例如，我们上面的例子，只想检索第2行和第三行的数据，那么SQL应该是这样的：

输入：

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

输出：

6.2 分页

客户端通过传递start(页码)，pageSize(每页显示的条数)两个参数去分页查询数据库表中的数据，那我们知道MySql数据库提供了分页的函数limit m,n，但是该函数的用法和我们的需求不一样，所以就需要我们根据实际情况去改写适合我们自己的分页语句，具体的分析如下：
比如：

查询第1条到第10条的数据的sql是：select * from table limit 0,10; ->对应我们的需求就是查询第一页的数据：select * from table limit (1-1)*10,10;

查询第10条到第20条的数据的sql是：select * from table limit 10,10; ->对应我们的需求就是查询第二页的数据：select * from table limit (2-1)*10,10;

查询第20条到第30条的数据的sql是：select * from table limit 20,10; ->对应我们的需求就是查询第三页的数据：select * from table limit (3-1)*10,10;

总结：
通过上面的分析，可以得出符合我们需求的分页sql格式是：

select * from table limit (start-1)*pageSize,pageSize; 

其中start是页码，pageSize是每页显示的条数。

7.SELECT子句顺序

在使用SELECT语句时，其子句必须保持以下的顺序：

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表中查询数据时使用
WHERE	行级数据过滤	需要设置行级数据过滤时使用
GROUP BY	分组说明	需要按组计算聚集时使用
HAVING	组级数据过滤	需要设置组级数据过滤时使用
ORDER BY	输出排序顺序	需要对输出结果进行排序时使用
LIMIT	要查询的行数	需要对查询结果限制输出行数时使用