将数据进行分组可以把数据分为多个逻辑组,便于对每个组进行对应的操作。通过GROUP BY和HAVING子句可以实现数据分组。
1. 创建分组
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
SELECT语句指定了连个列,vend_id包含产品供应商的ID,num_prods为计算字段(用COUNT(*)函数建立)。GROUP BY子句指示mysql按照vend_id排序并分组数据。这表示对每个vend_id而不是整个表计算num_prods。
使用GROUP BY子句的规定
- GROUP BY子句可以包含任意数目的列。即分组中可以嵌套分组
- 如果在GROUP BY子句中嵌套了分组,数据将在最后规定的分组上进行汇总。
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
- 除聚集计算语句外,SELECT语句中每个列都必须在GROUP BY子句中给出。
- 如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
ROLLUP
使用WITH ROLLUP关键字可以得到每个分组以及每个分组汇总值。对于NULL的汇总如果不使用ROLLUP是不会展现出来的,如下所示。
使用ROLLUP
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
| NULL | 14 |
+---------+-----------+
不使用ROLLUP
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+
2. 过滤分组
mysql可以通过WHERE过滤行数据,但无法过滤分组,为了解决过滤分组,mysql提供了HAVING子句。
没有分组:
SELECT cust_id, COUNT(*) AS ords
FROM orders
GROUP BY cust_id;
+---------+------+
| cust_id | ords |
+---------+------+
| 10001 | 2 |
| 10003 | 1 |
| 10004 | 1 |
| 10005 | 1 |
+---------+------+
分组之后:
SELECT cust_id, COUNT(*) AS ords
FROM orders
GROUP BY cust_id
HAVING ords >= 2;
+---------+------+
| cust_id | ords |
+---------+------+
| 10001 | 2 |
+---------+------+
HAVING与WHERE的区别
WHERE在数据分组前进行过滤,HAVING在数据分组后过滤。WHERE排除的行不被包含在HAVING分组中,从而影响HAVING分组。
WHERE与HAVING同时使用
未使用WHERE
SELECT vend_id, COUNT(*) num_prods
FROM products
GROUP BY vend_id
HAVING num_prods >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+
使用WHERE
SELECT vend_id, COUNT(*) num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING num_prods >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1003 | 4 |
| 1005 | 2 |
+---------+-----------+
3. 分组和排序
ORDER BY | GROUP BY |
---|---|
排序产生输出 | 分组行。但输出可能不是分组的顺序 |
任意列都可以使用(甚至非选择的列也可以使用) | 只可能使用选择列或者表达式列,而且必须使用每个选择列表达式 |
不一定需要 | 如果与聚集很熟一起使用列(或表达式),则必须使用 |
一般在使用GROUP BY子句时,应该也给出ORDER BY子句。这是保证排序正确的唯一方法。
通过下面的例子说明使用ORDER BY的区别
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING ordertotal >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20005 | 149.87 |
| 20006 | 55.00 |
| 20007 | 1000.00 |
| 20008 | 125.00 |
+-----------+------------+
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING ordertotal >= 50
ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20006 | 55.00 |
| 20008 | 125.00 |
| 20005 | 149.87 |
| 20007 | 1000.00 |
+-----------+------------+
4. SELECT子句顺序
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT