MySQL: MySQL聚合函数与GROUP BY分组详解
最后更新：2026-08-26
聚合函数是数据分析的核心——统计、分组、汇总全靠它们。
本课深入讲解聚合函数和分组查询。
graph TB
A[聚合查询流程] --> B[FROM 数据源]
B --> C[WHERE 过滤行]
C --> D[GROUP BY 分组]
D --> E[聚合函数<br/>COUNT/SUM/AVG/MAX/MIN]
E --> F[HAVING 过滤组]
F --> G[SELECT 输出]
G --> H[ORDER BY 排序]
H --> I[WITH ROLLUP 汇总]
1. 你将学到
- COUNT/SUM/AVG/MAX/MIN 用法
- GROUP BY 分组统计
- HAVING 过滤分组
- WITH ROLLUP 汇总行
- 聚合函数与 NULL 的关系
2. 报表统计的真实故事
(1) 痛点：Excel 统计太慢
一个运营要统计：每个部门的员工数、平均工资、最高工资。
用 Excel 需要写多个公式，数据更新还要重算。
(2) 聚合函数的解法
SQL
SELECT
department,
COUNT(*) AS emp_count,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department;
3. 聚合函数
(1) COUNT 计数
SQL
-- 统计总行数
SELECT COUNT(*) FROM employees;
-- 统计非 NULL 的行数
SELECT COUNT(email) FROM employees;
-- 统计去重数量
SELECT COUNT(DISTINCT department) FROM employees;
⚠️ 注意：
COUNT(*) 统计所有行，COUNT(col) 统计 col 非 NULL 的行。
(2) SUM/AVG 求和/平均
SQL
-- 总工资
SELECT SUM(salary) FROM employees;
-- 平均工资
SELECT AVG(salary) FROM employees;
-- 条件求和
SELECT SUM(amount) FROM orders WHERE status = 'paid';
(3) MAX/MIN 最大/最小
SQL
-- 最高工资
SELECT MAX(salary) FROM employees;
-- 最低工资
SELECT MIN(salary) FROM employees;
-- 最早/最晚日期
SELECT MIN(hire_date) AS earliest, MAX(hire_date) AS latest FROM employees;
4. GROUP BY 分组
▶ 示例：单字段分组
SQL
-- 按部门统计
SELECT
department,
COUNT(*) AS emp_count,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
输出：
TEXT
📖 仅展示
+-------------+-----------+-----------+
| department | emp_count | avg_salary|
+-------------+-----------+-----------+
| Engineering | 15 | 8500.00 |
| Sales | 10 | 6000.00 |
| Marketing | 8 | 5500.00 |
| HR | 5 | 5000.00 |
+-------------+-----------+-----------+
▶ 示例：多字段分组
SQL
-- 按部门+职位统计
SELECT
department,
job_title,
COUNT(*) AS count,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title
ORDER BY department, avg_salary DESC;
▶ 示例：按表达式分组
SQL
-- 按年份统计订单
SELECT
YEAR(order_date) AS year,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY YEAR(order_date);
-- 按年龄段统计
SELECT
CASE
WHEN age < 18 THEN 'Under 18'
WHEN age BETWEEN 18 AND 30 THEN '18-30'
WHEN age BETWEEN 31 AND 50 THEN '31-50'
ELSE 'Over 50'
END AS age_group,
COUNT(*) AS count
FROM users
GROUP BY age_group;
5. HAVING 过滤分组
WHERE 过滤行，HAVING 过滤组。
▶ 示例：HAVING 用法
SQL
-- 找出平均工资 > 6000 的部门
SELECT
department,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 6000;
-- 找出订单数 > 10 的客户
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING order_count > 10;
-- WHERE + GROUP BY + HAVING 组合
SELECT
department,
AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2024-01-01' -- 先过滤行
GROUP BY department -- 再分组
HAVING avg_salary > 6000; -- 最后过滤组
6. WITH ROLLUP 汇总
▶ 示例：ROLLUP 汇总行
SQL
-- 自动生成总计行
SELECT
department,
COUNT(*) AS emp_count,
SUM(salary) AS total_salary
FROM employees
GROUP BY department WITH ROLLUP;
输出：
TEXT
📖 仅展示
+-------------+-----------+--------------+
| department | emp_count | total_salary |
+-------------+-----------+--------------+
| Engineering | 15 | 127500.00 |
| Sales | 10 | 60000.00 |
| Marketing | 8 | 44000.00 |
| HR | 5 | 25000.00 |
| NULL | 38 | 256500.00 | -- 总计
+-------------+-----------+--------------+
7. 聚合函数与 NULL
| 函数 | NULL 处理 |
|---|---|
COUNT(*) |
包含 NULL 行 |
COUNT(col) |
排除 NULL |
SUM(col) |
忽略 NULL |
AVG(col) |
忽略 NULL（分母不含 NULL） |
MAX(col) |
忽略 NULL |
MIN(col) |
忽略 NULL |
▶ 示例：NULL 处理
SQL
-- COUNT(*) vs COUNT(email)
SELECT
COUNT(*) AS total_rows,
COUNT(email) AS has_email,
COUNT(*) - COUNT(email) AS no_email
FROM users;
-- AVG 忽略 NULL
SELECT AVG(salary) FROM employees; -- NULL 不参与计算
❓ 常见问题
Q WHERE 和 HAVING 能一起用吗？
A 可以。执行顺序：WHERE（过滤行）→ GROUP BY（分组）→ HAVING（过滤组）→ SELECT → ORDER BY。
Q GROUP BY 的字段必须在 SELECT 中吗？
A MySQL 严格模式要求 SELECT 的非聚合字段必须在 GROUP BY 中。
Q COUNT(*) 和 COUNT(1) 哪个快？
A 一样快。MySQL 优化器会自动选择最优方式。
Q 聚合函数能嵌套吗？
A 不能直接嵌套
AVG(SUM(...))。用子查询：SELECT AVG(total) FROM (SELECT SUM(amount) AS total FROM orders GROUP BY customer_id) t。📖 小节
- COUNT/SUM/AVG/MAX/MIN 是五大聚合函数
- GROUP BY 按字段分组，可多字段组合
- HAVING 过滤分组（可使用聚合函数），WHERE 过滤行（不能用聚合函数）
- WITH ROLLUP 自动生成汇总行
- NULL 在聚合函数中被忽略（除 COUNT(*)）
📝 作业
-
基础题（难度⭐）：统计每个部门的员工数量和平均工资。
-
进阶题（难度⭐⭐）：找出总订单金额 > 10000 的客户，按总金额降序排列。
-
挑战题（难度⭐⭐⭐）：统计每个月的订单数量、总金额和平均金额，使用 WITH ROLLUP 添加年度汇总行。
