在前面的文章中我们学会了如何精确地查询某一行或某些行如何用WHERE过滤数据、用ORDER BY排序。但在实际业务中我们往往不需要看到每一行细节而是需要“汇总”信息——比如总共有多少用户、每类图书的平均库存、每位读者的借阅次数等。这就是聚合函数和分组统计的用武之地。本文将带你掌握五种常用聚合函数COUNT、SUM、AVG、MAX、MINGROUP BY分组统计HAVING过滤分组结果SELECT各子句的逻辑执行顺序完整版实战统计每个读者的借阅数量及未归还数1. 聚合函数从细节到全局聚合函数对一组行进行计算返回单个值。它们常用来回答“有多少”、“总和是多少”、“平均值”、“最大/最小值”等问题。1.1 COUNT —— 计数COUNT(*)计算所有行的数量包括 NULL 行而COUNT(列名)只计算该列非 NULL的行数。-- 统计 books 表总共有多少本书SELECTCOUNT(*)FROMbooks;-- 统计有多少本书填写了 ISBNSELECTCOUNT(isbn)FROMbooks;COUNT(DISTINCT 列名)可以计算某列有多少个不同的值-- 统计有几个不同的作者SELECTCOUNT(DISTINCTauthor)FROMbooks;1.2 SUM —— 求和SUM对数值列进行求和自动忽略 NULL 值。-- 统计所有书的总库存SELECTSUM(stock)FROMbooks;1.3 AVG —— 平均值AVG计算数值列的平均值同样忽略 NULL。-- 书的平均库存SELECTAVG(stock)FROMbooks;1.4 MAX / MIN —— 最大值与最小值-- 最高库存和最低库存SELECTMAX(stock),MIN(stock)FROMbooks;-- 最新和最旧的出版日期SELECTMAX(publish_date),MIN(publish_date)FROMbooks;这些函数也可以用于字符串和日期类型MIN(name)返回字典序最小的书名MAX(publish_date)返回最近的日期。1.5 练习单表聚合在图书表中试试SELECTCOUNT(*)AStotal_books,SUM(stock)AStotal_stock,AVG(stock)ASavg_stock,MAX(stock)ASmax_stock,MIN(stock)ASmin_stock,COUNT(DISTINCTauthor)ASdistinct_authorsFROMbooks;2. GROUP BY分组统计GROUP BY子句根据一列或多列的值将数据行分组然后对每个组应用聚合函数。这是一个强大且常用的功能可以回答“每个分类有多少本书”、“每个读者的借阅次数”这类问题。2.1 基本语法SELECT列1,聚合函数(列2)FROM表名[WHERE条件]GROUPBY列1;示例统计每位作者的书籍数量SELECTauthor,COUNT(*)ASbook_countFROMbooksGROUPBYauthorORDERBYbook_countDESC;这里GROUP BY author将数据按作者分组COUNT(*)计算每个作者的书数最后用ORDER BY降序排列。2.2 多列分组GROUP BY可以包含多个列形成更细粒度的分组。-- 在 borrow_records 中按读者和图书分组统计每种借阅次数SELECTreader_id,book_id,COUNT(*)ASborrow_timesFROMborrow_recordsGROUPBYreader_id,book_id;2.3 GROUP BY 的规则使用GROUP BY时SELECT列表中出现的列必须是GROUP BY子句中的列或者聚合函数的参数否则查询可能报错ONLY_FULL_GROUP_BY模式下或结果不确定。例如下面的查询是不规范的-- 错误如果开启了 ONLY_FULL_GROUP_BYtitle 不在 GROUP BY 中也不是聚合SELECTauthor,title,COUNT(*)FROMbooksGROUPBYauthor;如果想要显示每个作者的一本代表书可以用MIN(title)或GROUP_CONCAT(title)。3. HAVING过滤分组WHERE是对行进行过滤而HAVING是对分组后的结果进行过滤。HAVING必须与GROUP BY配合使用但没有GROUP BY时也可以使用不过很少见。需求找出借阅次数超过 3 次的读者。SELECTreader_id,COUNT(*)ASborrow_countFROMborrow_recordsGROUPBYreader_idHAVINGborrow_count3;与 WHERE 的区别WHERE在分组前过滤行不能使用聚合函数。HAVING在分组后过滤组可以使用聚合函数。示例两阶段过滤查询在 2025 年 3 月之后至少借阅过 2 次的读者SELECTreader_id,COUNT(*)AScntFROMborrow_recordsWHEREborrow_date2025-03-01GROUPBYreader_idHAVINGcnt2;在这个查询中WHERE先过滤出 3 月及之后的借阅记录。GROUP BY按读者分组。HAVING保留借阅次数 ≥ 2 的组。4. SELECT 各子句的执行顺序完整版理解 SQL 子句的逻辑执行顺序有助于写出正确的查询尤其是处理别名和过滤条件时。一个包含目前所有子句的典型SELECT语句SELECT[DISTINCT]列FROM表[WHERE条件][GROUPBY列][HAVING分组条件][ORDERBY列][LIMIT偏移,行数];逻辑执行顺序FROM—— 确定数据源执行 JOINWHERE—— 过滤行GROUP BY—— 分组HAVING—— 过滤分组SELECT—— 选取列此时别名可用ORDER BY—— 排序可使用 SELECT 中定义的别名LIMIT—— 限制返回行数这就是为什么WHERE中不能使用别名因为别名在 SELECT 阶段才定义而ORDER BY中可以。5. 实战统计每个读者的借阅数量及未还数回到我们的图书管理系统进行几项有实际意义的聚合统计。5.1 数据准备假设你已经有了数据没有的话可以重新插入。为了演示效果我们先快速回顾现有数据必要时再插入几条-- 确保之前的数据还在如果数据丢失可从第一阶段的插入语句重新填充。-- 查看当前借阅记录数SELECTCOUNT(*)FROMborrow_records;5.2 统计每位读者的借阅次数SELECTr.nameASreader_name,COUNT(br.id)AStotal_borrowsFROMreaders rLEFTJOINborrow_records brONr.idbr.reader_idGROUPBYr.id,r.nameORDERBYtotal_borrowsDESC;使用LEFT JOIN确保没有借阅记录的读者也显示为 0因为COUNT(br.id)只计数非 NULL 的借阅记录 ID。如果使用INNER JOIN则不会显示没有借阅的读者。5.3 统计每位读者当前未归还的图书数量“未归还”意味着return_date IS NULL。我们可以在聚合中利用条件来分别统计。SELECTr.nameASreader_name,COUNT(br.id)AStotal_borrows,SUM(CASEWHENbr.return_dateISNULLTHEN1ELSE0END)ASunreturned_countFROMreaders rLEFTJOINborrow_records brONr.idbr.reader_idGROUPBYr.id,r.nameORDERBYunreturned_countDESC;解释COUNT(br.id)统计所有借阅记录数。SUM(CASE WHEN br.return_date IS NULL THEN 1 ELSE 0 END)计算未归还的数目。这里用CASE表达式在聚合内实现了条件计数。另一种方法是COUNT(*) FILTER (WHERE return_date IS NULL)MySQL 8.0 不支持但 PostgreSQL 等有在 MySQL 中我们通常用SUM(CASE...)或COUNT(IF(condition, 1, NULL))达到同样目的。另一种等价写法使用IF函数SUM(IF(br.return_dateISNULL,1,0))ASunreturned_count5.4 找出逾期未还的读者及逾期天数逾期定义未归还且当前日期已超过due_date。SELECTr.name,b.title,br.due_date,DATEDIFF(CURDATE(),br.due_date)ASoverdue_daysFROMborrow_records brJOINreaders rONbr.reader_idr.idJOINbooks bONbr.book_idb.idWHEREbr.return_dateISNULLANDbr.due_dateCURDATE()ORDERBYoverdue_daysDESC;如果想要统计每个读者逾期图书的数量SELECTr.name,COUNT(*)ASoverdue_count,SUM(DATEDIFF(CURDATE(),br.due_date))AStotal_overdue_daysFROMborrow_records brJOINreaders rONbr.reader_idr.idWHEREbr.return_dateISNULLANDbr.due_dateCURDATE()GROUPBYr.id,r.name;5.5 统计每本书的被借阅次数及当前库存SELECTb.title,b.stock,COUNT(br.id)ASborrow_times,SUM(CASEWHENbr.return_dateISNULLTHEN1ELSE0END)AScurrently_borrowedFROMbooks bLEFTJOINborrow_records brONb.idbr.book_idGROUPBYb.id,b.title,b.stockORDERBYborrow_timesDESC;这样我们可以同时看到一本书的库存总量、历史借阅次数以及当前正在外借的数量。5.6 综合按图书分类统计借阅量这个查询结合了多对多关系与聚合SELECTc.nameAScategory,COUNT(br.id)AStotal_borrowsFROMcategories cLEFTJOINbook_category bcONc.idbc.category_idLEFTJOINbooks bONbc.book_idb.idLEFTJOINborrow_records brONb.idbr.book_idGROUPBYc.id,c.nameORDERBYtotal_borrowsDESC;这个查询告诉我们哪个分类的图书最受欢迎。6. 小结本文我们掌握了数据分析的基础——聚合与分组聚合函数COUNT、SUM、AVG、MAX、MIN将多行数据汇集成一个值。GROUP BY把数据按某些列分组然后对每组应用聚合函数。HAVING在分组之后过滤组弥补了WHERE不能使用聚合函数的不足。执行顺序FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT记住这个顺序能避免许多别名错误。通过实战我们运用这些技术统计了读者借阅数、未归还数、逾期情况以及图书热度这些都是真实业务中的常见报表需求。下一篇文章我们将学习联合查询JOIN的深入用法包括内连接、外连接和自连接让你在多表查询中游刃有余。思考题如果用COUNT(*)和COUNT(列名)在 LEFT JOIN 中计数结果会有何不同HAVING和WHERE都能做过滤什么情况下必须用HAVING尝试写出统计每个读者平均每次借阅的图书数量考虑同时借多本可能需要提前了解子查询——可以暂时思考。参考资料MySQL 8.0 Reference Manual - Aggregate FunctionsMySQL 8.0 Reference Manual - GROUP BYMySQL 8.0 Reference Manual - SELECT Statement
聚合函数与分组统计：让数据说话
发布时间：2026/5/15 23:09:09
在前面的文章中我们学会了如何精确地查询某一行或某些行如何用WHERE过滤数据、用ORDER BY排序。但在实际业务中我们往往不需要看到每一行细节而是需要“汇总”信息——比如总共有多少用户、每类图书的平均库存、每位读者的借阅次数等。这就是聚合函数和分组统计的用武之地。本文将带你掌握五种常用聚合函数COUNT、SUM、AVG、MAX、MINGROUP BY分组统计HAVING过滤分组结果SELECT各子句的逻辑执行顺序完整版实战统计每个读者的借阅数量及未归还数1. 聚合函数从细节到全局聚合函数对一组行进行计算返回单个值。它们常用来回答“有多少”、“总和是多少”、“平均值”、“最大/最小值”等问题。1.1 COUNT —— 计数COUNT(*)计算所有行的数量包括 NULL 行而COUNT(列名)只计算该列非 NULL的行数。-- 统计 books 表总共有多少本书SELECTCOUNT(*)FROMbooks;-- 统计有多少本书填写了 ISBNSELECTCOUNT(isbn)FROMbooks;COUNT(DISTINCT 列名)可以计算某列有多少个不同的值-- 统计有几个不同的作者SELECTCOUNT(DISTINCTauthor)FROMbooks;1.2 SUM —— 求和SUM对数值列进行求和自动忽略 NULL 值。-- 统计所有书的总库存SELECTSUM(stock)FROMbooks;1.3 AVG —— 平均值AVG计算数值列的平均值同样忽略 NULL。-- 书的平均库存SELECTAVG(stock)FROMbooks;1.4 MAX / MIN —— 最大值与最小值-- 最高库存和最低库存SELECTMAX(stock),MIN(stock)FROMbooks;-- 最新和最旧的出版日期SELECTMAX(publish_date),MIN(publish_date)FROMbooks;这些函数也可以用于字符串和日期类型MIN(name)返回字典序最小的书名MAX(publish_date)返回最近的日期。1.5 练习单表聚合在图书表中试试SELECTCOUNT(*)AStotal_books,SUM(stock)AStotal_stock,AVG(stock)ASavg_stock,MAX(stock)ASmax_stock,MIN(stock)ASmin_stock,COUNT(DISTINCTauthor)ASdistinct_authorsFROMbooks;2. GROUP BY分组统计GROUP BY子句根据一列或多列的值将数据行分组然后对每个组应用聚合函数。这是一个强大且常用的功能可以回答“每个分类有多少本书”、“每个读者的借阅次数”这类问题。2.1 基本语法SELECT列1,聚合函数(列2)FROM表名[WHERE条件]GROUPBY列1;示例统计每位作者的书籍数量SELECTauthor,COUNT(*)ASbook_countFROMbooksGROUPBYauthorORDERBYbook_countDESC;这里GROUP BY author将数据按作者分组COUNT(*)计算每个作者的书数最后用ORDER BY降序排列。2.2 多列分组GROUP BY可以包含多个列形成更细粒度的分组。-- 在 borrow_records 中按读者和图书分组统计每种借阅次数SELECTreader_id,book_id,COUNT(*)ASborrow_timesFROMborrow_recordsGROUPBYreader_id,book_id;2.3 GROUP BY 的规则使用GROUP BY时SELECT列表中出现的列必须是GROUP BY子句中的列或者聚合函数的参数否则查询可能报错ONLY_FULL_GROUP_BY模式下或结果不确定。例如下面的查询是不规范的-- 错误如果开启了 ONLY_FULL_GROUP_BYtitle 不在 GROUP BY 中也不是聚合SELECTauthor,title,COUNT(*)FROMbooksGROUPBYauthor;如果想要显示每个作者的一本代表书可以用MIN(title)或GROUP_CONCAT(title)。3. HAVING过滤分组WHERE是对行进行过滤而HAVING是对分组后的结果进行过滤。HAVING必须与GROUP BY配合使用但没有GROUP BY时也可以使用不过很少见。需求找出借阅次数超过 3 次的读者。SELECTreader_id,COUNT(*)ASborrow_countFROMborrow_recordsGROUPBYreader_idHAVINGborrow_count3;与 WHERE 的区别WHERE在分组前过滤行不能使用聚合函数。HAVING在分组后过滤组可以使用聚合函数。示例两阶段过滤查询在 2025 年 3 月之后至少借阅过 2 次的读者SELECTreader_id,COUNT(*)AScntFROMborrow_recordsWHEREborrow_date2025-03-01GROUPBYreader_idHAVINGcnt2;在这个查询中WHERE先过滤出 3 月及之后的借阅记录。GROUP BY按读者分组。HAVING保留借阅次数 ≥ 2 的组。4. SELECT 各子句的执行顺序完整版理解 SQL 子句的逻辑执行顺序有助于写出正确的查询尤其是处理别名和过滤条件时。一个包含目前所有子句的典型SELECT语句SELECT[DISTINCT]列FROM表[WHERE条件][GROUPBY列][HAVING分组条件][ORDERBY列][LIMIT偏移,行数];逻辑执行顺序FROM—— 确定数据源执行 JOINWHERE—— 过滤行GROUP BY—— 分组HAVING—— 过滤分组SELECT—— 选取列此时别名可用ORDER BY—— 排序可使用 SELECT 中定义的别名LIMIT—— 限制返回行数这就是为什么WHERE中不能使用别名因为别名在 SELECT 阶段才定义而ORDER BY中可以。5. 实战统计每个读者的借阅数量及未还数回到我们的图书管理系统进行几项有实际意义的聚合统计。5.1 数据准备假设你已经有了数据没有的话可以重新插入。为了演示效果我们先快速回顾现有数据必要时再插入几条-- 确保之前的数据还在如果数据丢失可从第一阶段的插入语句重新填充。-- 查看当前借阅记录数SELECTCOUNT(*)FROMborrow_records;5.2 统计每位读者的借阅次数SELECTr.nameASreader_name,COUNT(br.id)AStotal_borrowsFROMreaders rLEFTJOINborrow_records brONr.idbr.reader_idGROUPBYr.id,r.nameORDERBYtotal_borrowsDESC;使用LEFT JOIN确保没有借阅记录的读者也显示为 0因为COUNT(br.id)只计数非 NULL 的借阅记录 ID。如果使用INNER JOIN则不会显示没有借阅的读者。5.3 统计每位读者当前未归还的图书数量“未归还”意味着return_date IS NULL。我们可以在聚合中利用条件来分别统计。SELECTr.nameASreader_name,COUNT(br.id)AStotal_borrows,SUM(CASEWHENbr.return_dateISNULLTHEN1ELSE0END)ASunreturned_countFROMreaders rLEFTJOINborrow_records brONr.idbr.reader_idGROUPBYr.id,r.nameORDERBYunreturned_countDESC;解释COUNT(br.id)统计所有借阅记录数。SUM(CASE WHEN br.return_date IS NULL THEN 1 ELSE 0 END)计算未归还的数目。这里用CASE表达式在聚合内实现了条件计数。另一种方法是COUNT(*) FILTER (WHERE return_date IS NULL)MySQL 8.0 不支持但 PostgreSQL 等有在 MySQL 中我们通常用SUM(CASE...)或COUNT(IF(condition, 1, NULL))达到同样目的。另一种等价写法使用IF函数SUM(IF(br.return_dateISNULL,1,0))ASunreturned_count5.4 找出逾期未还的读者及逾期天数逾期定义未归还且当前日期已超过due_date。SELECTr.name,b.title,br.due_date,DATEDIFF(CURDATE(),br.due_date)ASoverdue_daysFROMborrow_records brJOINreaders rONbr.reader_idr.idJOINbooks bONbr.book_idb.idWHEREbr.return_dateISNULLANDbr.due_dateCURDATE()ORDERBYoverdue_daysDESC;如果想要统计每个读者逾期图书的数量SELECTr.name,COUNT(*)ASoverdue_count,SUM(DATEDIFF(CURDATE(),br.due_date))AStotal_overdue_daysFROMborrow_records brJOINreaders rONbr.reader_idr.idWHEREbr.return_dateISNULLANDbr.due_dateCURDATE()GROUPBYr.id,r.name;5.5 统计每本书的被借阅次数及当前库存SELECTb.title,b.stock,COUNT(br.id)ASborrow_times,SUM(CASEWHENbr.return_dateISNULLTHEN1ELSE0END)AScurrently_borrowedFROMbooks bLEFTJOINborrow_records brONb.idbr.book_idGROUPBYb.id,b.title,b.stockORDERBYborrow_timesDESC;这样我们可以同时看到一本书的库存总量、历史借阅次数以及当前正在外借的数量。5.6 综合按图书分类统计借阅量这个查询结合了多对多关系与聚合SELECTc.nameAScategory,COUNT(br.id)AStotal_borrowsFROMcategories cLEFTJOINbook_category bcONc.idbc.category_idLEFTJOINbooks bONbc.book_idb.idLEFTJOINborrow_records brONb.idbr.book_idGROUPBYc.id,c.nameORDERBYtotal_borrowsDESC;这个查询告诉我们哪个分类的图书最受欢迎。6. 小结本文我们掌握了数据分析的基础——聚合与分组聚合函数COUNT、SUM、AVG、MAX、MIN将多行数据汇集成一个值。GROUP BY把数据按某些列分组然后对每组应用聚合函数。HAVING在分组之后过滤组弥补了WHERE不能使用聚合函数的不足。执行顺序FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT记住这个顺序能避免许多别名错误。通过实战我们运用这些技术统计了读者借阅数、未归还数、逾期情况以及图书热度这些都是真实业务中的常见报表需求。下一篇文章我们将学习联合查询JOIN的深入用法包括内连接、外连接和自连接让你在多表查询中游刃有余。思考题如果用COUNT(*)和COUNT(列名)在 LEFT JOIN 中计数结果会有何不同HAVING和WHERE都能做过滤什么情况下必须用HAVING尝试写出统计每个读者平均每次借阅的图书数量考虑同时借多本可能需要提前了解子查询——可以暂时思考。参考资料MySQL 8.0 Reference Manual - Aggregate FunctionsMySQL 8.0 Reference Manual - GROUP BYMySQL 8.0 Reference Manual - SELECT Statement
