MariaDB 教程 / 用 GROUP BY 分组并用 HAVING 筛选汇总结果
GROUP BY 把分组键相同的行汇总为组级结果,HAVING 则筛选这些组。WHERE 在分组前筛选原始行,因此把条件从 WHERE 移到 HAVING,可能改变参与统计的数据和最终结果。
先定义结果中的一行代表什么
商品明细是一行代表一件商品,分类报表则是一行代表一个分类。选择输出列时,每个非聚合值都应在这个层级上有明确含义。只按分类分组,却同时显示商品名,并没有说明多件商品中哪一个名称应该代表整个分类。
| 子句 | 处理对象 | 本例用途 |
|---|---|---|
| WHERE | 单条输入记录 | stock > 0,只留下有库存的商品 |
| GROUP BY | 分组键相同的记录 | 按 category 形成分类组 |
| HAVING | 已经计算的组 | COUNT(*) >= 2,保留至少两条记录的组 |
| ORDER BY | 最终结果的顺序 | category ASC,按分类排列 |
先熟悉 COUNT、SUM 与 AVG 的计数和缺失值规则,再理解同一函数如何分别作用于各组。分组、筛选与非分组列的规则可查阅 MariaDB 官方的 GROUP BY 文档。分组键决定结果粒度,并不是在原始明细旁边随意加一个总数。
准备分类数量不同的完整样本
以 course_user 连接学习服务器,先检查 SELECT VERSION(), DATABASE(); 与 SHOW TABLES;。本节使用独立表 products_23_lab;若已存在同名表,应先读取定义与记录,确认用途,不要覆盖业务表。
以下代码在 MariaDB 客户端中执行。也可以保存为 UTF-8 文件,按照 SQL 文件与 SOURCE 教程用实际路径加载,例如 SOURCE C:/mariadb-lab/lesson.sql;。不要在操作系统命令行直接执行这些 SQL。
USE shopcourse;
CREATE TABLE products_23_lab (
product_id INT NOT NULL AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
note VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (product_id),
CONSTRAINT ck_p23_price CHECK (price >= 0),
CONSTRAINT ck_p23_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_23_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
(2, '볼펜', '문구', 1500.00, 0, ''),
(3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
(4, '스탠드', '생활', 25000.00, 5, NULL),
(5, '파일', '문구', 1500.00, 50, ''),
(6, '케이블', '전자', 8000.00, 0, '호환 확인');
示例保留原始韩文字符串:分类 문구 表示文具,생활 表示生活用品,전자 表示电子产品。编号 1、2、5 属于文具,3、4 属于生活用品,6 属于电子产品,因此三个分类分别有三件、两件和一件商品。
第一条汇总查询包含全部六行,包括库存为零的商品。先区分“商品数量”和“有库存的商品数量”,再检查输出别名;SELECT 表达式与别名教程说明了如何让结果列的含义更清楚。
为每个分类生成一行汇总
SELECT category,COUNT(*) AS product_count,SUM(stock) AS total_stock,
SUM(price*stock) AS inventory_value
FROM products_23_lab GROUP BY category ORDER BY category;
| 分类含义 | 商品数 | 库存件数合计 | 库存金额 |
|---|---|---|---|
| 文具 | 3 | 70 | 145000.00 |
| 生活用品 | 2 | 17 | 221000.00 |
| 电子产品 | 1 | 0 | 0.00 |
COUNT 与 SUM 在各分类内部独立计算。上表翻译了分类含义,实际查询仍返回原来的韩文键。电子产品库存为零,但因为编号 6 这条商品记录存在,该组的商品数仍然是 1,而不是没有这个组。
三个组的商品数相加为 6,库存相加为 87,金额相加为 366000.00,与整表汇总应一致。这样可以同时检查组内计算和分组是否漏掉记录。显式 ORDER BY 表达希望得到的顺序,不应只凭一次执行看到的分组顺序来设计应用。
文本键是否被视为同一组,可能受排序规则影响,例如大小写、重音与尾随空格的比较。看起来不同的标签被合并时,应查看原始值、类型与排序规则,而不是立即修改 COUNT。
用 HAVING 筛选组的聚合值
SELECT category,COUNT(*) AS product_count
FROM products_23_lab
GROUP BY category HAVING COUNT(*) >= 2 ORDER BY category;
结果保留文具三件、生活用品两件,电子产品因只有一件而被排除。COUNT(*) 是每组计算出的数量,所以“至少两件”的条件写在 HAVING;它不是分组前每条商品记录上已经存在的字段,不能直接拿来写成 WHERE COUNT(*)。
只把条件中的 2 改为 3,结果就只剩文具组。核对后恢复 2,再确认生活用品组重新出现。这个对照只改变组的门槛,原始表和分组键都不变,因此可以清楚观察 HAVING 筛选的是组。
MariaDB 在适用情形下也允许 HAVING 引用结果别名。本例直接使用 COUNT(*),让条件与聚合的对应关系更明显。写复杂查询时,应避免让别名与输入列同名而产生歧义。
比较先用 WHERE 过滤输入后的结果
SELECT category,COUNT(*) AS in_stock_products
FROM products_23_lab WHERE stock > 0
GROUP BY category HAVING COUNT(*) >= 2 ORDER BY category;
WHERE stock > 0 先排除编号 2 和 6。此时文具只剩编号 1、5,共两件;生活用品仍为两件;电子产品没有输入行,因此没有形成该组。HAVING 再检查“至少两件”,最终得到文具两件和生活用品两件。
这与上一条查询中的文具三件,代表不同的统计对象。HAVING 无法把 WHERE 已排除的商品补回来。如果问题是“全部商品数达到门槛的分类”,就不应先去掉零库存商品;如果问题是“有库存商品数达到门槛的分类”,这个预过滤才符合意图。
另一个常见问题是仅按 category 分组,却选择未分组的 product_name 与 SUM(stock)。启用 ONLY_FULL_GROUP_BY 时,这种没有明确组级含义的名称会被拒绝。不要为了让语句通过就关闭该模式,应决定是否只输出分类级值,还是把商品名也加入分组;后者会改变结果粒度。
相关检查模式可参阅官方 SQL_MODE 文档。查看当前连接状态即可判断适用条件,不需要为了练习去修改服务器全局设置。
检查 NULL 分组键与连接造成的重复
如果另一个数据集允许分组键为 NULL,这些 NULL 键会形成同一个组。不要因为普通 WHERE 中 NULL = NULL 不是 TRUE,就推断 GROUP BY 会为每个 NULL 单独建组。本节 category 不允许 NULL,这个边界应与当前表的约束区分开。
在连接多表后做聚合时,先检查一条商品记录是否匹配多条其他记录。若输入已被扩成多行,再对金额求和就可能重复计算。对于小样本,可以列出每组的原始主键,逐条核对记录数和金额,再确认聚合结果。
所有这些 SELECT 都不需要改写本节六行。若要重练,先确认数据库、结构与记录,并备份需要保留的数据。下面只删除已确认可丢弃的 products_23_lab,会同时删除定义和全部行,普通 ROLLBACK 无法恢复;把清理放在事务实验之外。
DROP TABLE shopcourse.products_23_lab;




