分组数据

表结构
CREATE TABLE products (
prod_id char(10) NOT NULL,
vend_id int NOT NULL,
prod_name char(255) NOT NULL,
prod_price decimal(8,2) NOT NULL,
prod_desc text,
PRIMARY KEY (prod_id),
KEY fk_products_vendors (vend_id),
CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

表数据
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01 | 1001 | .5 ton anvil | 5.99 | .5 ton anvil, black, complete with handy hook |
| ANV02 | 1001 | 1 ton anvil | 9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03 | 1001 | 2 ton anvil | 14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR | 1003 | Detonator | 13.00 | Detonator (plunger powered), fuses not included |
| FB | 1003 | Bird seed | 10.00 | Large bag (suitable for road runners) |
| FC | 1003 | Carrots | 2.50 | Carrots (rabbit hunting season only) |
| FU1 | 1002 | Fuses | 3.42 | 1 dozen, extra long |
| JP1000 | 1005 | JetPack 1000 | 35.00 | JetPack 1000, intended for single use |
| JP2000 | 1005 | JetPack 2000 | 55.00 | JetPack 2000, multi-use |
| OL1 | 1002 | Oil can | 8.99 | Oil can, red |
| SAFE | 1003 | Safe | 50.00 | Safe with combination lock |
| SLING | 1003 | Sling | 4.49 | Sling, one size fits all |
| TNT1 | 1003 | TNT (1 stick) | 2.50 | TNT, red, single stick |
| TNT2 | 1003 | TNT (5 sticks) | 10.00 | TNT, red, pack of 10 sticks |
+---------+---------+----------------+------------+----------------------------------------------------------------+

分组概念

分组允许把数据分为多个逻辑组，以便能对每个组进行聚焦计算

SELECT vend_id, COUNT(*)AS num_prods FROM products GROUP BY vend_id
结果：
+---------+----------+
| vend_id | COUNT() |
+---------+----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+----------+
上面的SELECT语句指定了两个列，vend_id包含产品供应商的ID，num_prods为计算字段(用COUNT()函数建立)。GROUP BY 字句指示mysql按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次。

过滤分组

除了能用GROUP BY 分组之外，Mysql还允许过滤分组，规定包括那些分组，排除哪些分组
WHERE过滤指定的是行而不是分组

SELECT cust_id, COUNT() FROM orders GROUP BY cust_id HAVING COUNT() < 2;
以cust_id分组，使用HAVING过滤COUNT(*) < 2的分组

HAVING和WHERE的差别：

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

分组数据

原文：https://www.cnblogs.com/bibicode/p/13723501.html