mysql---数据分组

最新推荐文章于 2024-09-09 17:02:14 发布

原创最新推荐文章于 2024-09-09 17:02:14 发布 · 786 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

15 篇文章

订阅专栏

将数据进行分组可以把数据分为多个逻辑组，便于对每个组进行对应的操作。通过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 |
+-----------+------------+